Connecting to Your Database
Using Embedded SQL

Since PowerBuilder does not inspect all SQL statement syntax, you can qualify Adaptive Server Enterprise catalog entities as necessary.

For example, the following qualifications are all acceptable:

You can use any function that Adaptive Server Enterprise supports (such as aggregate or mathematical functions) in SQL statements.

This example shows how to use the Adaptive Server Enterprise function UPPER in a SELECT statement:

SELECT UPPER(emp_name) 
   INTO :emp_name_var 
   FROM employee;

Calling Client Library functions

While PowerBuilder provides access to a large percentage of the features within Adaptive Server Enterprise, in some cases you may decide that you need to call one or more Client Library (CT-Lib) functions directly for a particular application. PowerBuilder provides access to any Windows DLL by using external function declarations.

CT-Lib calls require a pointer to one of the following structures as their first parameter:

You can obtain the current CS_CONNECTION pointer by using the PowerScript DBHandle function.

Using DBHandle to obtain the CS_CONNECTION pointer

DBHandle takes a transaction object as a parameter and returns a long variable, which is the CS_CONNECTION pointer that PowerBuilder uses internally to communicate with the database. You can pass this value as one of the parameters to your external function.

This example shows how to use DBHandle. Assume a successful connection has occurred using the default transaction object (SQLCA):

// Define a variable to hold our DB handle. 
long    SQLServerHandle

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

// Now that you have the CS_CONNECTION pointer, 
// call the DLL function. 
MyDLLFunction( SQLServerHandle, parm1, parm2, ... )

In your DLL, cast the incoming long value into a pointer to a CS_CONNECTION structure:

MyDLLFunction( long 1SQLServerHandle,
      parm1_type parm1,
      parm2_type Parm2, ... )
{
CS_CONNECTION * pConnect;
pConnect = (CS_CONNECTION *)  1SQLServerHandle;
// CT-LIB functions can be called using pConnect.
}

Obtaining the CS_CONTEXT pointer

Within your external function, you can obtain the CS_CONTEXT pointer with the following function call:

CS_RETCODE       RC;
CS_CONNECTION    * PConnect;
CS_INT           outlen;
CS_CONTEXT        * pContext;
rc = ct_con_props (pConnect,CS_GET,CS_PARENT_HANDLE,
               (CS_VOID *) &pContext, CS_UNUSED,
               &outlen);

Allocating a new command pointer

Likewise, you can allocate a new command pointer with the following code:

CS_COMMAND    * pCommand;
rc = ct_cmd_alloc(pConnect, &pCommand);

You use the following transaction management statements with transaction objects to manage connections and transactions for Adaptive Server Enterprise databases:

Transaction management statements in triggers

You should not use transaction statements in triggers. A trigger is a special kind of stored procedure that takes effect when you issue a statement such as INSERT, DELETE, or UPDATE on a specified table or column. Triggers can be used to enforce referential integrity.

For example, assume that a certain condition within a trigger is not met and you want to execute a ROLLBACK. Instead of coding the ROLLBACK directly in the trigger, you should use RAISERROR and test for that particular return code in the DBMS-specific return code (SQLDBCode) property within the referenced transaction object.

See also

SAP Adaptive Server Enterprise Using CONNECT, COMMIT, DISCONNECT, and ROLLBACK

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

See also

SAP Adaptive Server Enterprise Performance and locking

SAP Adaptive Server Enterprise Using AutoCommit

The setting of the AutoCommit property of the transaction object determines whether PowerBuilder issues SQL statements inside or outside the scope of a transaction. When AutoCommit is set to false or 0 (the default), SQL statements are issued inside the scope of a transaction. When you set AutoCommit to true or 1, SQL statements are issued outside the scope of a transaction.

Adaptive Server Enterprise requires you to execute Data Definition Language (DDL) statements outside the scope of a transaction unless you set the database option "ddl in tran" to true. If you execute a database stored procedure that contains DDL statements within the scope of a transaction, an error message is returned and the DDL statements are rejected. When you use the transaction object to execute a database stored procedure that creates a temporary table, you do not want to associate the connection with a transaction.

To execute Adaptive Server Enterprise stored procedures containing DDL statements, you must either set "ddl in tran" to true, or set AutoCommit to true so PowerBuilder issues the statements outside the scope of a transaction. However, if AutoCommit is set to true, you cannot issue a ROLLBACK. Therefore, you should set AutoCommit back to false (the default) immediately after completing the DDL operation.

When you change the value of AutoCommit from false to true, PowerBuilder issues a COMMIT statement by default.

See also

SAP Adaptive Server Enterprise Performance and locking

SAP Adaptive Server Enterprise Using CONNECT, COMMIT, DISCONNECT, and ROLLBACK

An important consideration when designing a database application is deciding when CONNECT and COMMIT statements should occur to maximize performance and limit locking and resource use. A CONNECT takes a certain amount of time and can tie up resources during the life of the connection. If this time is significant, then limiting the number of CONNECT statements is desirable.

In addition, after a connection is established, SQL statements can cause locks to be placed on database entities. The more locks 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:

Isolation feature

SAP Adaptive Server Enterprise databases use 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 Read uncommitted:

Example 1

This script uses embedded SQL to connect to a database and insert a row in the ORDER_HEADER table and a row in the ORDER_ITEM table. Depending on the success of the statements in the script, the script executes either a COMMIT or a ROLLBACK.

// Set the SQLCA connection properties.
SQLCA.DBMS = "SYC"
SQLCA.servername = "SERVER24"
SQLCA.database = "ORDERS"
SQLCA.logid = "JPL"
SQLCA.logpass = "TREESTUMP"
// Connect to the database. AutoCommit is set to
// False by default.
CONNECT USING SQLCA;
// Insert a row into the ORDER_HEADER table.
// A ROLLBACK is required only if the first row
// was inserted successfully.
INSERT INTO ORDER_HEADER (ORDER_ID,CUSTOMER_ID)
   VALUES ( 7891, 129 ); 
// Test return code for ORDER_HEADER insertion.
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
// If insert failed, roll back insertion of 
// ORDER_HEADER.
   ROLLBACK USING SQLCA;
   end if
end if
// Commit changes and disconnect from the database.
DISCONNECT USING SQLCA;

Example 2

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

Since this script uses SetTransObject to connect to the database, the programmer is responsible for managing the transaction.

The window OPEN event script:

// Set the transaction object properties 
// and connect to the database. 
// Set the SQLCA connection properties. 
SQLCA.DBMS = "SYC" 
SQLCA.servername = "SERVER24" 
SQLCA.database = "ORDERS"
SQLCA.logid = "JPL" 
SQLCA.logpass = "TREESTUMP"
// Connect to the database. 
CONNECT USING SQLCA;
// Tell the DataWindow which transaction object
// to use.
SetTransObject( dw_1, SQLCA )

The CommandButton CLICKED event script:

// Declare ReturnValue an integer. 
integer ReturnValue 
ReturnValue = Update( dw_1 )
// 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;

See also

SAP Adaptive Server Enterprise Using CONNECT, COMMIT, DISCONNECT, and ROLLBACK

The statements that do not involve cursors or procedures are:

Internally, PowerBuilder processes DELETE, INSERT, and UPDATE statements the same way. PowerBuilder inspects them for any PowerScript data variable references and replaces all such references with a constant that conforms to Adaptive Server Enterprise rules for the 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 data 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 that when the DELETE statement executes, the database receives the following statement:

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

SAP Adaptive Server Enterprise SELECT

The SELECT statement contains input variables and output variables.

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 variables defined within the scope of the script that contains the SELECT statement, and emp_id_var 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 Adaptive Server Enterprise data type. When the data types do not match perfectly, PowerBuilder converts them.

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

SELECT emp_name, emp_salary 
   FROM employee WHERE emp_id = 691;

If the statement executes with no errors, data locations for the result fields are bound internally. The data returned into these locations is then converted as necessary and the appropriate PowerScript data 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 to delete the employee.

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, 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!") 
      COMMIT; 
   else 
//It didn't work. 
      MessageBox( "Error", & 
      "The delete failed. Employee ID is not "& 
      +"valid.")
      ROLLBACK;
   end if
else
// No input value. Prompt user.
      MessageBox( "Error",& 
      "An employee ID is required for "& 
      +"delete!" ) 
   end if

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    EmployeeAgeUpperLimit

// 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_employee(emp_id,emp_name)
   SELECT emp_id, emp_name FROM employee
      WHERE emp_age >= :EmployeeAgeLowerLimit
      AND emp_age <= :EmployeeAgeUpperLimit;

// If there are no errors, commit the changes.
if SQLCA.sqlcode = 0 then
   COMMIT;
else

// If there are errors, roll back the changes and
// tell the user.
   ROLLBACK;
   MessageBox( "Insert Failed", SQLCA.sqlerrtext)
end if

See also

SAP Adaptive Server Enterprise DELETE, INSERT, and UPDATE

In embedded SQL, statements that retrieve data and statements that update data can both involve cursors.

Retrieval statements

The retrieval statements that involve cursors are:

Update statements

The update statements that involve cursors are:

Setting CursorUpdate to use updatable cursors

To use the UPDATE ... WHERE CURRENT OF or DELETE ... WHERE CURRENT OF statements, you must set the CursorUpdate DBParm parameter to 1 before declaring the cursor. (By default, CursorUpdate is set to 0.)

For example:

SQLCA.DBParm = "CursorUpdate = 1"

You can set the CursorUpdate parameter at any time before or after connecting to the database. You can also change its setting at any time.

See also

SAP Adaptive Server Enterprise Retrieval Using Cursors

SAP Adaptive Server Enterprise Closing the Cursor

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

If you expect only a single row to exist in the employee table with the specified emp_id, use the singleton SELECT. 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, when a SELECT may return multiple rows, you must:

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 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. 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.

Fetching rows

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

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 can be 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 data variables emp_id_var and emp_name_var. Executing another FETCH statement will place the variables from the next row into specified variables.

FETCH statements typically occur in a loop that processes several rows from a result set (one row at a time): fetch the row, process the variables, and then fetch the next row.

Example

This cursor example illustrates how you can loop through a result set. Assume 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 in 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

// Pop up 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

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.

Unlike the DB-Library interface to SQL Server, the CT-Library interface lets you issue other commands while a cursor is open.

Example

In this example, the additional request for the employee name (shown in bold) is issued while the cursor is open. Under the DB-Library interface, this request would have failed and returned a Results Pending message. Under the CT-Library interface, it succeeds.

string    dname
long      depthead
string    fname
string    lname

SQLCA.dbms = "SYC"
SQLCA.database = "mzctest"
SQLCA.logid = "mikec"
SQLCA.logpass = "mikecx"
SQLCA.servername = "SYB1001"
SQLCA.autocommit = "false"

CONNECT USING SQLCA;
if SQLCA.sqlcode <> 0 then
   MessageBox("Connect Error",SQLCA.sqlerrtext)
end if

DECLARE dept_curs CURSOR FOR SELECT dept_name,
   dept_head_id FROM department;
OPEN dept_curs;
if SQLCA.sqlcode < 0 then
   MessageBox("Open Cursor",SQLCA.sqlerrtext)
end if

DO WHILE SQLCA.sqlcode = 0
   FETCH dept_curs INTO :dname, :depthead;
   if SQLCA.sqlcode < 0 then
      MessageBox("Fetch Error",SQLCA.sqlerrtext)
   elseif SQLCA.sqlcode = 0 then
      SELECT emp_fname, emp_lname INTO
         :fname,:lname FROM employee
         WHERE emp_id = :depthead;
      if SQLCA.sqlcode <> 0 then
         MessageBox("Singleton Select",  & 
            SQLCA.sqlerrtext)
      end if
   end if
LOOP

CLOSE dept_curs;
if SQLCA.sqlcode <> 0 then
   MessageBox("Close Cursor", SQLCA.sqlerrtext)
end if

See also

SAP Adaptive Server Enterprise SELECT

One of the most significant features of SAP Adaptive Server Enterprise is database stored procedures. You can use database stored procedures for:

PowerBuilder supports all these uses in embedded SQL.

Using AutoCommit with database stored procedures

The setting of the AutoCommit property of the transaction object determines whether PowerBuilder issues SQL statements inside or outside the scope of a transaction. When AutoCommit is set to false or 0 (the default), SQL statements are issued inside the scope of a transaction. When you set AutoCommit to true or 1, SQL statements are issued outside the scope of a transaction.

Adaptive Server Enterprise requires you to execute Data Definition Language (DDL) statements outside the scope of a transaction unless you set the database option "ddl in tran" to true. If you execute a database stored procedure that contains DDL statements within the scope of a transaction, an error message is returned and the DDL statements are rejected. When you use the transaction object to execute a database stored procedure that creates a temporary table, you do not want to associate the connection with a transaction.

To execute Adaptive Server Enterprise stored procedures containing DDL statements, you must either set "ddl in tran" to true, or set AutoCommit to true so PowerBuilder issues the statements outside the scope of a transaction. However, if AutoCommit is set to true, you cannot issue a ROLLBACK. Therefore, you should set AutoCommit back to false (the default) immediately after completing the DDL operation.

When you change the value of AutoCommit from false to true, PowerBuilder issues a COMMIT statement by default.

Using transaction statements in database stored procedures

Transaction statements in database stored procedures are not honored when the stored procedure is executing within the scope of a transaction. For example, a ROLLBACK statement will not be honored if the following are all true:

You should use alternative means to execute the ROLLBACK. For example, you can use return values as described in the information about triggers in Transaction management statements (SAP Adaptive Server Enterprise Transaction management statements).

See also

SAP Adaptive Server Enterprise Retrieval

SAP Adaptive Server Enterprise Temporary tables

SAP Adaptive Server Enterprise Update

SAP Adaptive Server Enterprise Return values and output parameters

SAP Adaptive Server Enterprise System stored procedures

SAP Adaptive Server Enterprise Using database stored procedures in DataWindow objects

PowerBuilder uses a construct that is very similar to cursors to support retrieval using database stored procedures. In the PowerBuilder-supported embedded SQL, there are four commands that involve database stored procedures:

See also

SAP Adaptive Server Enterprise DECLARE and EXECUTE

SAP Adaptive Server Enterprise FETCH

SAP Adaptive Server Enterprise CLOSE

PowerBuilder requires a declarative statement to identify the database stored procedure that is being used and a logical name that can be referenced in subsequent SQL statements.

The general syntax for declaring a procedure is:

DECLARE logical_procedure_name PROCEDURE FOR
   {@rv = } SQL_Server_procedure_name
   @Param1 = value1, @Param2 = value2 , ...
   {USING transaction_object} ;

where logical_procedure_name can be any valid PowerScript data identifier, SQL_Server_procedure_name is the name of the stored procedure in the database, and @rv is an optional return value.

The parameter references can take the form of any valid parameter string that Adaptive Server Enterprise accepts. PowerBuilder does not inspect the parameter list format except for purposes of variable substitution. The USING clause is required only if you are using a transaction object other than the default transaction object (SQLCA).

Example

Assume a stored procedure proc1 is defined as:

CREATE PROCEDURE proc1 AS
   SELECT emp_name FROM employee

To declare that procedure for processing within PowerBuilder, enter:

DECLARE emp_proc PROCEDURE FOR proc1;

Note that this declaration is a nonexecutable statement, just like a cursor declaration. 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:

EXECUTE emp_proc;

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

For example:

FETCH emp_proc INTO :emp_name_var;

Example 1

Database stored procedures can return multiple result sets. Assume you define a database stored procedure proc2 as:

CREATE PROCEDURE proc2 AS
   SELECT emp_name FROM employee
   SELECT part_name FROM parts

PowerBuilder provides access to both result sets:

// Declare the procedure.
DECLARE emp_proc2 PROCEDURE FOR proc2;

// Declare some variables to hold results.
string    emp_name_var
string    part_name_var

// Execute the stored procedure.
EXECUTE emp_proc2;

// Loop through all rows in the first result
// set.
DO WHILE SQLCA.sqlcode = 0

// Fetch the next row from the first result set.
   FETCH emp_proc2 INTO :emp_name_var;
LOOP

// At this point we have exhausted the first
// result set. After this occurs, 
// PowerBuilder notes that there is another
// result set and internally shifts result sets. 
// The next FETCH executed will retrieve the 
// first row from the second result set.
// Fetch the first row from the second result
// set.
if SQLCA.sqlcode = 100 then
   FETCH emp_proc2 INTO :part_name_var;
end if

// Loop through all rows in the second result 
// set. 
DO WHILE SQLCA.sqlcode = 0

// Fetch the next row from the second result
// set.
   FETCH emp_proc2 INTO :part_name_var;
LOOP

// Close the procedure.
CLOSE emp_proc2;

The result sets that will be returned when a database stored procedure executes cannot be determined at compile time. Therefore, you must code FETCH statements that exactly match the format of a result set returned by the stored procedure when it executes.

Example 2

In the preceding example, if instead of coding the second fetch statement as:

FETCH emp_proc2 INTO :part_name_var;

you coded it as:

FETCH emp_proc2 INTO :part_var1,:part_var2,:part_var3;

the statement would compile without errors. But an execution error would occur: the number of columns in the FETCH statement does not match the number of columns in the current result set. The second result set returns values from only one column.

If a database stored procedure returns a result set, it must be closed when processing is complete. You do not have to retrieve all the rows in a result set to close a request or procedure.

Closing a procedure looks the same as closing a cursor:

CLOSE emp_proc;

If a procedure executes successfully and returns at least one result set and is not closed, a result set is pending and no SQL commands other than the FETCH can be executed. Procedures with result sets should be closed as soon as possible.

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

Using the SQL Code property

If you know for sure that a particular procedure can never return result sets, then the EXECUTE statement is all that is needed. If there is a procedure that 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 following table shows all the possible values for SQLCode after an EXECUTE:

Example 1

Assume the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has been executed. Also assume the description of the Adaptive Server Enterprise procedure good_employee is:

// Adaptive Server Enterprise good_employee
// stored procedure:
CREATE PROCEDURE good_employee AS
   UPDATE employee 
   SET emp_salary=emp_salary * 1.1
   WHERE emp_status = 'EXC'

This example illustrates how to execute a stored procedure that does not return any result sets:

// 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 result sets.
if SQLCA.sqlcode = -1 then

// Issue error message since it failed.
   MessageBox("Stored Procedure Error!", &
   SQLCA.sqlerrtext)
end if

Example 2

Assume the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has been executed. Also assume the description of the Adaptive Server Enterprise procedure get_employee is:

// Adaptive Server Enterprise get_employee
// stored procedure:
   CREATE PROCEDURE get_employee @emp_id_parm
   int AS SELECT emp_name FROM employee
   WHERE emp_id = @emp_id_parm

This example illustrates how to pass parameters to a database stored procedure. Emp_id_var has been set elsewhere to 691:

// 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

In addition to result sets, SAP Adaptive Server Enterprise stored procedures may return a long integer return value and output parameters of any data type. After all of the result sets have been returned, PowerScript requires you to issue one final FETCH procedure_name INTO . . . statement to obtain these values. The order in which these values are returned is:

return value, output parm1, output parm2, ...

Example 1

The following stored procedure contains one input parameter (@deptno) and returns a result set containing employee names and salaries for that department. It also returns two output parameters (@totsal and @avgsal), and a return value that is the count of employees in the department.

integer fetchcount = 0
long    lDeptno, rc
string  fname, lname
double  dSalary, dTotSal, dAvgSal

lDeptno = 100

DECLARE deptproc PROCEDURE FOR
   @rc = dbo.deptroster
   @deptno = :lDeptno,
   @totsal = 0 output,
   @avgsal = 0 output
USING SQLCA;

EXECUTE deptproc;
CHOOSE CASE SQLCA.sqlcode
CASE 0
   // Execute successful. There is at least one
   // result set. Loop to get the query result set
   // from the table SELECT.
   DO
      FETCH deptproc INTO :fname, :lname, :dSalary;
      CHOOSE CASE SQLCA.sqlcode
      CASE 0
         fetchcount++
      CASE 100
         MessageBox ("End of Result Set", &
            string (fetchcount) " rows fetched")
      CASE -1
         MessageBox ("Fetch Failed", &
            string (SQLCA.sqldbcode) " = " &
            SQLCA.sqlerrtext)
      END CHOOSE
   LOOP WHILE SQLCA.sqlcode = 0

   // Issue an extra FETCH to get the Return Value
   // and Output Parameters.
   FETCH deptproc INTO :rc, :dTotSal, :dAvgSal;
   CHOOSE CASE SQLCA.sqlcode
   CASE 0
      MessageBox ("Fetch Return Value and Output" &
         "Parms SUCCESSFUL", "Return Value is: " &
         string (rc) &
         "~r~nTotal Salary: " string (dTotSal) &
         "~r~nAverage Sal:  " string (dAvgSal))
   CASE 100
      MessageBox ("Return Value and Output Parms" &
         "NOT FOUND", "")
   CASE ELSE
      MessageBox ("Fetch Return Value and Output" &
         "Parms FAILED", "SQLDBCode is " &
         string (SQLCA.sqldbcode) " = " &
         SQLCA.sqlerrtext)
   END CHOOSE

   CLOSE deptproc;

CASE 100
   // Execute successful; no result set.
   // Do not try to close.
   MessageBox ("Execute Successful", "No result set")

CASE ELSE
   MessageBox ("Execute Failed", &
      string (SQLCA.sqldbcode) " = " &
      SQLCA.sqlerrtext)

END CHOOSE

Database stored procedures frequently contain temporary tables that are used as repositories when accumulating rows during processing within the procedure. Since Adaptive Server Enterprise requires you to execute Data Definition Language (DDL) statements outside the scope of a transaction unless you set the database option "ddl in tran" to true, PowerBuilder provides the boolean AutoCommit property in the transaction object to allow you to handle these cases.

The setting of AutoCommit determines whether PowerBuilder issues SQL statements inside or outside the scope of a transaction. When AutoCommit is set to false or 0 (the default), SQL statements are issued inside the scope of a transaction. When you set AutoCommit to true or 1, SQL statements are issued outside the scope of a transaction.

To execute Adaptive Server Enterprise stored procedures containing DDL statements, you must either set "ddl in tran" to true, or set AutoCommit to true so PowerBuilder issues the statements outside the scope of a transaction. However, if AutoCommit is set to true, you cannot issue a ROLLBACK. Therefore, you should set AutoCommit back to false (the default) immediately after completing the DDL operation.

When you change the value of AutoCommit from false to true, PowerBuilder issues a COMMIT statement.

You can access system database stored procedures the same way you access user-defined stored procedures. You can use the DECLARE statement against any procedure and can qualify procedure names if necessary.

Using stored procedures as DataWindow data sources

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

Database stored procedures summary

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