Using Embedded SQL with ODBC

About this chapter

When you create scripts for a PowerBuilder application, you can use embedded SQL statements in the script to perform operations on the database. The features supported when you use embedded SQL depend on the DBMS to which your application connects.

Overview

When you use the ODBC interface to connect to a backend database, you can use embedded SQL in your scripts.

You can embed the following types of SQL statements in scripts and user-defined functions if the ODBC driver you are using and the backend DBMS you are accessing supports this functionality. (Not all backend databases support cursor statements and database stored procedures.)

  • Transaction management statements

  • Non-cursor statements

  • Cursor statements

  • Database stored procedures

ODBC API

The ODBC interface uses the ODBC application programming interface (API) to interact with the backend database.

When you use embedded SQL, PowerBuilder makes the required calls to the backend database. Therefore, you do not need to know anything about the ODBC API to use embedded SQL with PowerBuilder.

See also

Using the ODBC Interface

ODBC SQL Support

ODBC Transaction management statements

ODBC Non-cursor statements

ODBC Cursor statements

ODBC Database stored procedures

ODBC SQL Support

PowerBuilder embedded SQL supports the name qualification conventions and functions used in the databases accessible through the ODBC interface.

See also

ODBC Name qualification

ODBC SQL functions

ODBC Name qualification

PowerBuilder does not inspect all SQL statement syntax, so you can qualify database catalog entities as necessary.

For example, the following qualifications are acceptable for an ODBC interface to a SQL Anywhere database:

  • emp_name

  • employee.emp_name

ODBC SQL functions

In SQL statements, you can use any function that your backend DBMS supports (such as aggregate or mathematical functions). For example, if your DBMS supports the function Sum, you can use the function Sum in a SELECT statement:

SELECT Sum(salary)
   INTO :salary_sum_var
   FROM employee;

Calling ODBC functions

While PowerBuilder provides access to a large percentage of the features within ODBC, in some cases you may decide that you need to call one or more ODBC functions directly for a particular application. PowerBuilder provides access to most Windows DLLs by using external function declarations.

The ODBC calls qualify for this type of access. Most ODBC calls require a pointer to a connection handle (of the variable type HDBC) to a structure as their first parameter. If you want to call ODBC without reconnecting to the database to get a connection handle, use the PowerScript DBHandle function.

DBHandle

DBHandle takes a transaction object as a parameter and returns a long variable, which is the handle to the database for the transaction. This handle is actually the connection handle PowerBuilder uses internally to communicate with the database. You can use this returned long value in the ODBC DLLs and pass it as one of the parameters in your function.

After you obtain the connection handle, you can use the ODBC SQLGetInfo call to obtain the environment handle of the variable type HENV.

Example

This example illustrates how to use DBHandle. As with other examples, assume a successful connection has occurred using the default transaction object (SQLCA).

// Define a variable to hold the DB connection handle
long         ODBCConnectionHandle

// Go get the handle.
ODBCConnectionHandle = SQLCA.DBHandle( )

// Now that you have the ODBC connection pointer,
// call the DLL function.
MyDLLFunction(ODBCConnectionHandle, parm1, parm2)

In your DLL, cast the incoming connection handle of the type HDBC:

MyDLLFunction(long 1ODBCConnectionHandle,
   parm1_type parm1,
   parm2_type Parm2, ...)
{
HDBC * pDatabase;
pDatabase = (HDBC *)  1ODBCConnectionHandle;
// ODBC functions can be called using pDatabase.
}

See also

ODBC Using escape clauses

ODBC Using escape clauses

ODBC defines extensions that are common to most backend DBMSs. To cover vendor-specific extensions, the syntax defined by ODBC uses the escape clause provided by the X/Open and SQL Access Group (SAG) SQL draft specifications.

For example, some of the extensions defined in ODBC are:

  • Date, time, and timestamp data

  • Scalar functions (such as data type, numeric, and string conversion functions)

  • Outer joins

  • Procedures

Maximum portability

For maximum portability, you should use escape sequences in your applications.

Syntax

For example, PowerBuilder uses the date, time, and timestamp escape clauses as the default formats for data manipulation. The syntax for each of these escape clauses is:

{ d yyyy-mm-dd }
{ t hh:mm:ss }
{ ts yyyy-mm-dd hh:mm:ss:[fff[fff]] }

Example

Each of the following statements updates employee Henry Jones's start time in the Employee table. The first statement uses the escape clause, and the second statement uses native syntax for a time column:

UPDATE Employee
   SET start_time = {t 08:30:00}
   WHERE emp_name = "Henry Jones"
UPDATE Employee
   SET start_time = (08:30:00)
   WHERE emp_name = "Henry Jones"

ODBC Transaction management statements

If the database you are connecting to supports transaction management, you can use the following transaction management statements with one or more transaction objects to manage connections and transactions for a database:

  • CONNECT

  • DISCONNECT

  • COMMIT

  • ROLLBACK

See also

ODBC Using CONNECT, DISCONNECT, COMMIT, and ROLLBACK

ODBC Using CONNECT, DISCONNECT, COMMIT, and ROLLBACK

The following table lists each transaction management statement and describes how it works when you use the ODBC interface to connect to a database:

Statement

Description

CONNECT

Establishes the database connection. After you assign values to the required properties of the transaction object, you can execute a CONNECT. When you connect to the database, the DBMS name returned by the ODBC SQLGetInfo call is returned in the transaction object property SQLReturnData.

DISCONNECT

Terminates a successful connection. When a DISCONNECT is executed, PowerBuilder internally executes a COMMIT WORK statement to commit all changes and then issues a CLOSE DATABASE statement to terminate the logical unit of work.

COMMIT

Applies all changes made to the database since the beginning of the current unit of work.

ROLLBACK

Undoes all changes made to the database since the beginning of the current logical unit of work.


See also

ODBC Performance and locking

ODBC Performance and locking

After a connection is established, SQL statements can cause locks to be placed on database entities. The more locks there are in place at a given moment in time, the more likely it is that the locks will hold up another transaction.

Rules

No set of rules for designing a database application is totally comprehensive. However, when you design a PowerBuilder application, you should do the following:

  • Long-running connections

    Determine whether you can afford to have long-running connections. If not, your application should connect to the database only when absolutely necessary. After all the work for that connection is complete, the transaction should be disconnected.

    If long-running connections are acceptable, then COMMITs should be issued as often as possible to guarantee that all changes do in fact occur. More importantly, COMMITs should be issued to release any locks that may have been placed on database entities as a result of the statements executed using the connection.

  • SetTrans or SetTransObject function

    Determine whether you want to use default DataWindow transaction processing (the SetTrans function) or control the transaction in a script (the SetTransObject function).

    If you cannot afford to have long-running connections and therefore have many short-lived transactions, use the default DataWindow transaction processing. If you want to keep connections open and issue periodic COMMITs, use the SetTransObject function and control the transaction yourself.

Switching during a connection

To switch between transaction processing and AutoCommit during a connection, change the setting of AutoCommit in the transaction object.

Isolation feature

ODBC uses the isolation feature to support assorted database lock options. In PowerBuilder, you can use the Lock property of the transaction object to set the isolation level when you connect to the database.

The following example shows how to set the Lock property to RU (Read uncommitted):

// Set the lock property to read uncommitted 
// in the default transaction object SQLCA. 
SQLCA.Lock = "RU"

PowerBuilder uses the ODBC API call SQ2.SetConnectOption (SetIsolationLevel) to set the isolation level. The lock value is passed to the function as a 32-bit mask.

Example 1

This script uses embedded SQL to connect to a database and attempts to insert a row in the ORDER_HEADER table and a row in the ORDER_ITEM table. The script then executes a COMMIT or ROLLBACK depending on the success of all statements in the script.

// Set the SQLCA connection properties.
SQLCA.DBMS = "ODBC" 
SQLCA.DBParm = "connectstring = 'DSN = orders'"
// Connect to the database. 
CONNECT USING SQLCA;
// Insert a row into the ORDER_HEADER table.
INSERT INTO ORDER_HEADER (ORDER_ID,CUSTOMER_ID)
   VALUES (7891, 129); 
// Test return code for ORDER_HEADER insertion.
// A ROLLBACK is required only if the first row 
// was inserted successfully.
if SQLCA.sqlcode = 0 then
// Since the ORDER_HEADER is inserted,
// try to insert ORDER_ITEM.
   INSERT INTO ORDER_ITEM 
      (ORDER_ID, ITEM_NBR, PART_NBR, QTY)
      VALUES (7891, 1, '991PLS', 456); 
// Test return code for ORDER_ITEM insertion.
   if SQLCA.sqlcode = -1 then
// Disconnect from the database.
DISCONNECT USING SQLCA;

Error checking

Although you should test the SQLCode after every SQL statement, these examples show statements to test the SQLCode only to illustrate a specific point.

Example 2

This example uses scripts for the Open and Close events for a window and the Clicked event for a CommandButton to illustrate how you can manage transactions for a DataWindow control. Assume a window contains a DataWindow control dw_1 and a CommandButton Cb_Update. Also assume the user enters data in dw_1 and then clicks the Cb_Update button to update the database with the data.

The window OPEN event script:

// Set the transaction object properties
// and connect to the database. 
// Set the SQLCA connection properties. 
SQLCA.DBMS = "ODBC"
SQLCA.DBParm = "connectstring = 'DSN = orders'"
// Connect to the database.
CONNECT USING SQLCA;
// Tell the DataWindow which transaction object to use. 
dw_1.SetTransObject(sqlca)

The CommandButton CLICKED event script:

// Declare ReturnValue an integer.
integer    ReturnValue 
ReturnValue = dw_1.Update( )
// Test to see if updates were successful.
if ReturnValue = -1 then
// Updates were not successful. Since we used
// SetTransObject, roll back any changes made
// to the database. 
   ROLLBACK USING SQLCA; 
else
// Updates were successful. Since we used
// SetTransObject, commit any changes made 
// to the database. 
   COMMIT USING SQLCA;
end if

The window CLOSE event script:

// Disconnect from the database.
DISCONNECT USING SQLCA;

ODBC Non-cursor statements

The statements that do not involve cursors are:

ODBC DELETE, INSERT, and UPDATE

Internally, PowerBuilder processes DELETE, INSERT, and UPDATE the same way. PowerBuilder inspects these statements for variable references and replaces all variable references with a constant that conforms to the backend database's rules for that data type.

Example

Assume you enter the following statement:

DELETE FROM employee WHERE emp_id = :emp_id_var;

In this example, emp_id_var is a PowerScript variable with the data type of integer that has been defined within the scope of the script that contains the DELETE statement.

Before the DELETE statement is executed, emp_id_var is assigned a value (say 691) so when the DELETE statement executes, the database receives the following command:

DELETE FROM employee WHERE emp_id = 691;

When is this substitution technique used?

This variable substitution technique is used for all PowerScript variable types. When you use embedded SQL, precede all PowerScript variables with a colon ( : ).

See also

ODBC SELECT

ODBC SELECT

The SELECT statement contains input and output variables.

  • Input variables

    are passed to the database as part of the execution, and the substitution is as described for DELETE, INSERT, and UPDATE.

  • Output variables

    return values based on the result of the SELECT statement.

Example 1

Assume you enter the following statement:

SELECT emp_name, emp_salary 
   INTO :emp_name_var, :emp_salary_var
   FROM employee WHERE emp_id = :emp_id_var;

In this example, emp_id_var, emp_salary_var, and emp_name_var are PowerScript variables defined within the scope of the script containing the SELECT statement, and emp_id_var is an input variable and is processed as described in the DELETE example above.

Both emp_name_var and emp_salary_var are output variables that will be used to return values from the database. The data types of emp_name_var and emp_salary_var should be the PowerScript data types that best match the data type in the database. When the data types do not match perfectly, PowerBuilder converts them.

How big should numeric output variables be?

For numeric data, the output variable must be large enough to hold any value that may come from the database.

Assume the value for emp_id_var is 691 as in the previous example. When the SELECT statement executes, the database receives this command:

SELECT emp_name, emp_salary FROM employee WHERE emp_id = 691;

If no errors are returned when the statement executes, data locations are bound internally for the result fields. The data returned into these locations is converted if necessary, and the appropriate PowerScript variables are set to those values.

Example 2

This example assumes the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has executed. It also assumes the data type of the emp_id column in the employee table is CHARACTER[10]. The user enters an employee ID into the single line edit field sle_Emp and clicks the button Cb_Delete.

The script for the Clicked event in the CommandButton Cb_Delete is:

// Make sure we have a value.
if sle_Emp.text <> "" then
// Since we have a value, let's try to delete it.
   DELETE FROM employee 
   WHERE emp_id = :sle_Emp.text;
// Test to see if the DELETE worked.
   if SQLCA.sqlcode = 0 then
// It seems to have worked; let user know.
   MessageBox("Delete",& 
      "The delete has been successfully "& 
      +"processed!") 
   else 
// It didn't work. 
   MessageBox("Error", &
      "The delete failed. Employee ID "&
      +"is not valid.")
      end if
else
// No input value. Prompt user.
   MessageBox("Error", & 
       "An employee ID is required for "&
      +"delete!") 
end if

Error checking

Although you should test the SQLCode after every SQL statement, these examples show statements to test the SQLCode only to illustrate a specific point.

Example 3

This example assumes the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has executed. The user wants to extract rows from the employee table and insert them into the table named extract_employees. The extraction occurs when the user clicks the button Cb_Extract. The boolean variable YoungWorkers is set to TRUE or FALSE elsewhere in the application.

The script for the Clicked event for the CommandButton Cb_Extract is:

integer   EmployeeAgeLowerLimit
integer   mployeeAgeUpperLimit

// Do they have young workers?
if (YoungWorkers = TRUE) then

// Yes - set the age limit in the YOUNG range. 
// Assume no employee is under legal working age. 
   EmployeeAgeLowerLimit = 16

// Pick an upper limit. 
   EmployeeAgeUpperLimit = 42
else

// No - set the age limit in the OLDER range.
   EmployeeAgeLowerLimit = 43

// Pick an upper limit that includes all employees.
   EmployeeAgeUpperLimit = 200 
end if 

INSERT INTO extract_employees(emp_id,emp_name) 
   SELECT emp_id, emp_name FROM employee 
      WHERE emp_age >= :EmployeeAgeLowerLimit 
      AND emp_age <= :EmployeeAgeUpperLimit;

ODBC Cursor statements

In embedded SQL, statements that retrieve data and statements that update data can both involve cursors. Not all backend DBMSs support cursor statements.

Retrieval statements

The retrieval statements that involve cursors are:

  • DECLARE cursor_name CURSOR FOR ...

  • OPEN cursor_name

  • FETCH cursor_name INTO ...

  • CLOSE cursor_name

Update statements

The update statements that involve cursors are:

  • UPDATE ... WHERE CURRENT OF cursor_name

  • DELETE ... WHERE CURRENT OF cursor_name

See also

ODBC Retrieval using cursors

ODBC FETCH NEXT

ODBC FETCH FIRST, FETCH PRIOR, and FETCH LAST

ODBC Update

ODBC Retrieval using cursors

Retrieval using cursors is conceptually similar to the singleton SELECT discussed earlier. The main difference is that since there can be multiple rows in a result set, you control when the next row is fetched into PowerScript variables.

For example, if you expect only a single row to exist in the employee table for each emp_id, use a singleton SELECT statement. In a singleton SELECT, you specify the SELECT statement and destination variables in one concise SQL statement:

SELECT emp_name, emp_salary
   INTO :emp_name_var, :emp_salary_var
   FROM employee WHERE emp_id = :emp_id_var;

However, if the SELECT may return multiple rows, you must:

  1. Declare a cursor.

  2. Open it (which conceptually executes the SELECT).

  3. Fetch rows as needed.

  4. Close the cursor.

Declaring and opening a cursor

Declaring a cursor is tightly coupled with the OPEN statement. The DECLARE specifies the SELECT statement to be executed, and the OPEN actually executes it.

Declaring a cursor is similar to declaring a variable. A cursor declaration is a nonexecutable statement just like a variable declaration. The first step in declaring a cursor is to define how the result set looks. To do this, you need a SELECT statement, and since you must refer to the result set in subsequent SQL statements, you must associate the result set with a logical name.

Example

Assume the SingleLineEdit sle_1 contains the state code for the retrieval:

// Declare cursor emp_curs for employee table
// retrieval.
DECLARE emp_curs CURSOR FOR
   SELECT emp_id, emp_name FROM EMPLOYEE 
   WHERE emp_state = :sle_1.text;

// Declare local variables for retrieval.
string emp_id_var
string emp_name_var

// Execute the SELECT statement with
// the current value of sle_1.text.
OPEN emp_curs;

// At this point, if there are no errors,
// the cursor is available for further
// processing.

Scrolling and locking

Use the DBParm parameters CursorScroll and CursorLock to specify the scrolling and locking options.

Note

Not all DBMSs support these scrolling and locking options.

Fetching rows

The ODBC interface supports the following FETCH statements. You can use them if they are supported by your backend DBMS.

  • FETCH NEXT

  • FETCH FIRST

  • FETCH PRIOR

  • FETCH LAST

Note

Not all DBMSs support all of these FETCH statements.

ODBC FETCH NEXT

In the singleton SELECT, you specify variables to hold values for the columns within the selected row. The syntax of the FETCH statement is similar to the singleton SELECT statement syntax. Values are returned INTO a specified list of variables.

Example

This example continues the previous example by retrieving some data:

// Go get the first row from the result set.
FETCH emp_curs INTO :emp_id_var, :emp_name_var;

If at least one row is retrieved, this FETCH places the values of the emp_id and emp_name columns from the first row in the result set into the PowerScript variables emp_id_var and emp_name_var. FETCH statements typically occur in a loop that processes several rows from a result set (one row at a time), but this is not the only way they are used.

What happens when the result set is exhausted?

FETCH returns +100 (not found) in the SQLCode property within the referenced transaction object. This is an informational return code; -1 in SQLCode indicates an error.

See also

ODBC FETCH FIRST, FETCH PRIOR, and FETCH LAST

ODBC FETCH FIRST, FETCH PRIOR, and FETCH LAST

In addition to the conventional FETCH NEXT statement, the ODBC interface supports FETCH FIRST, FETCH PRIOR, and FETCH LAST statements.

What happens if you only enter FETCH?

If you only enter FETCH, PowerBuilder assumes FETCH NEXT.

Closing the cursor

The CLOSE statement terminates processing for the specified cursor. CLOSE releases resources associated with the cursor, and subsequent references to that cursor are allowed only if another OPEN is executed. Although you can have multiple cursors open at the same time, you should close the cursors as soon as possible for efficiency reasons.

See also

ODBC FETCH NEXT

ODBC Update

After a FETCH statement completes successfully, you are positioned on a current row within the cursor. At this point, you can execute an UPDATE or DELETE statement using the WHERE CURRENT OF cursor_name syntax to update or delete the row. PowerBuilder enforces the cursor update restrictions of the backend database, and violations will result in an execution error.

Example

This cursor example illustrates how to loop through a result set. It assumes that the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has been executed. The statements retrieve rows from the employee table, and then display a message box with the employee name for each row that is found.

// Declare the emp_curs.
DECLARE emp_curs CURSOR FOR
   SELECT emp_name FROM EMPLOYEE
   WHERE emp_state = :sle_1.text;
// Declare a destination variable for employee 
// names. 
string emp_name_var
// Execute the SELECT statement with the
// current value of sle_1.text.
OPEN emp_curs;
// Fetch the first row from the result set.
FETCH emp_curs INTO :emp_name_var;
// Loop through result set until exhausted.
DO WHILE sqlca.sqlcode = 0
// Display a message box with the employee name.
   MessageBox("Found an employee!",emp_name_var)
// Fetch the next row from the result set.
   FETCH emp_curs INTO :emp_name_var;
LOOP
// All done; close the cursor.
CLOSE emp_curs;

Error checking

Although you should test the SQLCode after every SQL statement, these examples show statements to test the SQLCode only to illustrate a specific point.

ODBC Database stored procedures

Retrieval and update

You can use database stored procedures for:

  • Retrieval only

  • Update only

  • Retrieval and update

Your DBMS

Not all DBMSs support these retrieval and update options.

Using stored procedures

When you use database stored procedures in a PowerBuilder application, keep the following points in mind:

  • Manipulating stored procedures

    PowerBuilder provides SQL statements that are similar to cursor statements for manipulating database stored procedures.

  • Retrieval and update

    PowerBuilder supports retrieval, update, or a combination of retrieval and update in database stored procedures, including procedures that do not return a result set and those that return a result set.

  • Transactions and stored procedures without result sets

    When a procedure executes using a particular connection (transaction) and the procedure does not return a result set, the procedure is no longer active. No result set is pending, and therefore you do not execute a CLOSE statement.

See also

ODBC Retrieval

ODBC Using database stored procedures in DataWindow objects

ODBC Retrieval

PowerBuilder uses a construct similar to cursors to support retrieval using database stored procedures. PowerBuilder supports four embedded SQL statements that involve database stored procedures:

  • DECLARE procedure_name PROCEDURE FOR ...

  • EXECUTE procedure_name

  • FETCH procedure_name INTO ...

  • CLOSE procedure_name

See also

ODBC DECLARE and EXECUTE

ODBC EXECUTE

ODBC FETCH

ODBC CLOSE

ODBC DECLARE and EXECUTE

PowerBuilder requires a declarative statement to identify the database stored procedure that is being used and to specify a logical name for the procedure. The logical name is used to reference the procedure in subsequent SQL statements.

The general syntax for declaring a procedure is:

DECLARE logical_procedure_name PROCEDURE FOR 
   procedure_name 
   {@param1 = value, @param2 = value2, ...} 
   {USING transaction_object};

where logical_procedure_name can be any valid PowerScript identifier and procedure_name is the name of a stored procedure in the database.

The parameter references can take the form of any valid parameter string the database accepts. PowerBuilder inspects the parameter list format only for variable substitution. The USING clause is required only if you are using a transaction object other than the default transaction object (SQLCA).

Output parameters might not be returned when you use an embedded SQL command to call a stored procedure. You can set the PBNewSPInvocation database parameter to "Yes" to use an alternative method to invoke a stored procedure. The behavior of the PowerBuilder ODBC driver when this DBParm is set is consistent with the default behavior of the OLE DB and JDBC drivers.

If PBNewSPInvocation is set to "Yes," the alternative method is used when you retrieve data into a DataWindow object that uses a stored procedure. See ODBC DECLARE and EXECUTE with PBNewSPInvocation.

Example

Assume a stored procedure named proc1 is defined on the server. To declare proc1 for processing within PowerBuilder, enter:

DECLARE emp_proc PROCEDURE FOR proc1;

The procedure declaration is a nonexecutable statement, just like a cursor declaration. However, where cursors have an OPEN statement, procedures have an EXECUTE statement.

When an EXECUTE statement executes, the procedure is invoked. The EXECUTE refers to the logical procedure name, in this example emp_proc:

EXECUTE emp_proc;

See also

ODBC EXECUTE

ODBC DECLARE and EXECUTE with PBNewSPInvocation

ODBC DECLARE and EXECUTE with PBNewSPInvocation

PowerBuilder requires a declarative statement to identify the database stored procedure that is being used and to specify a logical name for the procedure. The logical name is used to reference the procedure in subsequent SQL statements.

The general syntax for declaring a procedure is:

DECLARE logical_procedure_name PROCEDURE FOR 
   procedure_name 
   @param1 = value, @param2 = value2, 
   @PARAM3 = VALUE3 OUTPUT 
{USING transaction_object};

where logical_procedure_name can be any valid PowerScript identifier and procedure_name is the name of a stored procedure in the database. Use the OUT or OUTPUT keyword to obtain the value of the output parameter.

The parameter references can take the form of any valid parameter string the database accepts. PowerBuilder inspects the parameter list format only for variable substitution. The USING clause is required only if you are using a transaction object other than the default transaction object (SQLCA).

You must set the PBNewSPInvocation database parameter to "Yes" to use this method to invoke a stored procedure. The behavior of the PowerBuilder ODBC driver when this DBParm is set is consistent with the default behavior of the OLE DB and JDBC drivers.

If PBNewSPInvocation is set to "Yes", this method is used when you retrieve data into a DataWindow object that uses a stored procedure. This DBParm has no effect when you use RPC to invoke a stored procedure.

If PBNewSPInvocation is set to "No", use the syntax described in ODBC DECLARE and EXECUTE.

Example 1

Assume a stored procedure named proc1 is defined on the server as:

CREATE PROCEDURE proc1 AS
   SELECT emp_name FROM employee

To declare proc1 for processing within PowerBuilder, enter:

DECLARE emp_proc PROCEDURE FOR proc1;

The procedure declaration is a nonexecutable statement, just like a cursor declaration. However, where cursors have an OPEN statement, procedures have an EXECUTE statement.

When an EXECUTE statement executes, the procedure is invoked. The EXECUTE refers to the logical procedure name, in this example emp_proc:

EXECUTE emp_proc;

Example 2

To declare a procedure with input and output parameters, enter:

DECLARE sp_duration PROCEDURE FOR pr_date_diff_prd_ken
   @var_date_1 = :ad_start,
   @var_date_2 = :ad_end,
   @rtn_diff_prd = :ls_duration OUTPUT;

If the stored procedure contains result sets, you must fetch the result sets first. If the stored procedure has a return value and you want to obtain it, use the format RC=procedure_name:

DECLARE sp_duration PROCEDURE FORRC=pr_date_diff_prd_ken 
   @var_date_1 = :ad_start,
   @var_date_2 = :ad_end,
   @rtn_diff_prd = :ls_duration OUTPUT;

ODBC FETCH

To access rows returned in a result set, use the FETCH statement the same way you use it for cursors. The FETCH statement can be executed after any successful EXECUTE statement for a procedure that returns a result set.

Example

FETCH emp_proc INTO :emp_name_var;

Using FETCH after EXECUTE

Following an EXECUTE statement for a procedure, you can use the FETCH statement only to access values produced by the SELECT statement in the database stored procedure.

Since PowerBuilder cannot determine at compile time what result set will be returned when a database stored procedure executes, you must code FETCH statements so that the stored procedure exactly matches the format of the result set during execution. Assume you coded the second FETCH statement in the example above as:

FETCH emp_proc INTO :var1, :var2, :var3;

The statement compiles without errors. However, you will get an execution error indicating that the number of columns in the FETCH statement does not match the number of columns in the result set.

See also

ODBC EXECUTE

ODBC FETCH NEXT

ODBC FETCH FIRST, FETCH PRIOR, and FETCH LAST

ODBC CLOSE

If a database stored procedure returns a result set, you must close the stored procedure when processing is complete. The procedure remains open until you close it, execute a COMMIT or ROLLBACK, or end the database connection.

Do you have to retrieve all the rows?

You do not have to retrieve all rows in a result set to close a request or procedure.

Example

Closing a procedure looks the same as closing a cursor:

CLOSE emp_proc;

ODBC EXECUTE

Database stored procedures that perform only updates and do not return a result set are handled in much the same way as procedures that return a result set. The only difference is that after the EXECUTE procedure_name statement executes, no result set is pending, so a CLOSE statement is not required.

Using the SQLCode property

If a specific procedure can never return a result set, only the EXECUTE statement is required. If a procedure may or may not return a result set, you can test the SQLCode property of the referenced transaction object for +100 (the code for NOT FOUND) after the EXECUTE.

The possible values for SQLCode after an EXECUTE are:

Return code

Means

0

The EXECUTE was successful and a result set is pending. Regardless of the number of FETCH statements executed, the procedure must be explicitly closed with a CLOSE statement.

Fetched row not found.

+100

Fetched row not found.

-1

The EXECUTE was not successful and no result set was returned.


Example 1

This example illustrates how to execute a stored procedure that does not return a result set. It assumes the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has been executed.

// good_employee is a database stored procedure. 
// Declare the procedure. 
DECLARE good_emp_proc PROCEDURE 
   FOR good_employee;
// Execute it. 
EXECUTE good_emp_proc;
// Test return code. Allow for +100 since you 
// do not expect a result set. 
if SQLCA.sqlcode = -1 then
// Issue an error message since it failed.
   MessageBox("Stored Procedure Error!", & 
   SQLCA.sqlerrtext) 
end if

Error checking

Although you should test the SQLCode after every SQL statement, these examples show statements to test the SQLCode only to illustrate a specific point.

Example 2

This example illustrates how to pass parameters to a database stored procedure. It assumes the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has been executed. Emp_id_var was set to 691 elsewhere.

// get_employee is a database stored procedure. 
// Declare the procedure. 
DECLARE get_emp_proc PROCEDURE FOR 
   get_employee @emp_id_parm = :emp_id_var;
// Declare a destination variable for emp_name. 
string emp_name_var
// Execute the stored procedure using the 
// current value for emp_id_var. 
EXECUTE get_emp_proc;
// Test return code to see if it worked. 
if SQLCA.sqlcode = 0 then
// Since we got a row, fetch it and display it. 
   FETCH get_emp_proc INTO :emp_name_var;
// Display the employee name. 
   MessageBox("Got my employee!",emp_name_var)
// You are all done, so close the procedure. 
   CLOSE Get_emp_proc; 
end if

ODBC Using database stored procedures in DataWindow objects

You can use database stored procedures as a data source for DataWindow objects. The following rules apply:

  • Result set definition

    You must define what the result set looks like in the DataWindow painter. PowerBuilder cannot determine this information from the stored procedure definition in the database.

  • Stored procedure arguments

    The DataWindow painter provides the arguments for stored procedures only if the ODBC driver you are using to connect gives PowerBuilder the required information. If the arguments for the database stored procedure are not provided, you must define them.

  • DataWindow updates

    Updates are not allowed for stored procedures in a DataWindow object. Only retrieval is allowed.

  • ODBC syntax

    PowerBuilder supports the syntax appropriate for all backend databases supported by the ODBC interface. In the DataWindow painter, PowerBuilder displays the most general stored procedure syntax. It then converts it to the syntax appropriate for the backend database before passing it to the database.