Connecting to Your Database
Using Embedded SQL

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

For example, the following qualifications are all acceptable:

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

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

SELECT UPPER(emp_name) INTO :emp_name_var FROM employee;

Calling DB-Library functions

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

The DB-Lib calls qualify for this type of access. Most DB-Lib calls require a pointer to a DBPROCESS structure as their first parameter. If you want to call DB-Lib without reconnecting to the database to get a DBPROCESS pointer, 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 DBPROCESS pointer that PowerBuilder uses internally to communicate with the database. You can use this returned long value in the SQL Server DLLs and pass it as one of the parameters in your 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 DBPROCESS pointer,
// call the DLL function.
MyDLLFunction( SQLServerHandle, parm1, parm2, ... )

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

MyDLLFunction( long 1SQLServerHandle,
      parm1_type parm1,
      parm2_type Parm2, ... )
{
DBPROCESS * pDatabase;
pDatabase = (DBPROCESS *)  1SQLServerHandle;
// DB-Lib functions can be called using pDatabase.
}

Transaction management statements

You use the following transaction management statements with transaction objects to manage connection and transactions for a SQL Server database:

Transaction management 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

Microsoft SQL Server Using CONNECT, COMMIT, DISCONNECT, and ROLLBACK

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

See also

Microsoft SQL Server Performance and locking

Microsoft SQL Server Temporary tables

Microsoft SQL Server Using AutoCommit

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.

Versions of SQL Server prior to SQL Server 2000 require you to execute Data Definition Language (DDL) statements outside the scope of a transaction. 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 SQL Server stored procedures containing DDL statements in SQL Server 7 and earlier, you must 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

Microsoft SQL Server Performance and locking

Microsoft SQL Server Temporary tables

Microsoft SQL Server 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

SQL Server 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 a SQL Server 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"

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 = "SQLServer"
SQLCA.servername = "SERVER24"
SQLCA.database = "ORDERS"
SQLCA.logid = "JPL"
SQLCA.logpass = "TREESTUMP"
// Connect to the database.
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.
// ROLLBACK 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 = "SQLServer"
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, rollback 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

Microsoft SQL Server Temporary tables

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 SQL Server 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

Microsoft SQL Server 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 SQL Server data type. When the data types do not match perfectly, PowerBuilder converts them.

SELECT emp_name, emp_salary FROM employee WHERE emp_id = 691;

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!")
   else 
//It didn't work.
      MessageBox( "Error", &
       "The delete failed. Employee ID is not "&
        +"valid.")
   end ifelse
// 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;

See also

Microsoft SQL Server DELETE, INSERT, and UPDATE

In embedded SQL, statements that retrieve data can involve cursors. These statements are:

Note UPDATE ... WHERE CURRENT OF cursor_name and DELETE ... WHERE CURRENT OF cursor_name are not supported in SQL Server.

Retrieval

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.

Scrolling and locking

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

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.

See also

Microsoft SQL Server Fetching rows

Microsoft SQL Server Closing the cursor

The SQL Server interfaces support the following FETCH statements (Microsoft_SQL_Server_FETCH):

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.

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

Microsoft SQL Server FETCH NEXT

Microsoft SQL Server FETCH FIRST, FETCH PRIOR, and FETCH LAST

SQL Server support the FETCH FIRST, FETCH PRIOR, and FETCH LAST statements in addition to the conventional FETCH NEXT statement.

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

// All done, so close the cursor.
CLOSE emp_curs;

See also

Microsoft SQL Server FETCH

Microsoft SQL Server FETCH NEXT

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.

In SQL Server, there is an additional reason to close cursors as soon as possible. When an OPEN statement completes successfully, there is a result pending for the current connection. FETCH statements can be executed as long as there are rows in the result set to be processed. However, as long as the result set is pending, no other commands can be executed using the connection. To execute other commands using the connection, you must release the result set by closing the cursor.

Internally, PowerBuilder issues a DB-Lib dbcancel statement when the cursor is closed. After the CLOSE has been executed, the connection can be used for other SQL statements.

Example

This example illustrates the pending result set problem in SQL Server. These statements use the cursor emp_curs to retrieve rows from the employee table, then attempt to execute another SQL statement while the cursor is open:

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

// Execute an INSERT statement.
INSERT INTO office ( office_id, office_city )
   VALUES ( 1234, 'Boston' );

// This INSERT statement would fail because of
// the pending result set from the emp_curs
// cursor. If we had never opened the cursor, or
// if we had completed processing of the cursor
// and then closed it, the INSERT statement
// would work.

Retrieval and update

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

PowerBuilder supports all these uses in PowerBuilder embedded SQL.

Using AutoCommit with database stored procedures

Database stored procedures often create temporary table that hold rows accumulated during processing. To create these tables, the stored procedure executes SQL Data Definition Language (DDL) statements. Versions of SQL Server prior to SQL Server 2000 do not allow you to execute DDL statements within the scope of a transaction.

To execute SQL Server stored procedures that contain DDL statements statements in SQL Server 7 and earlier, you must set the AutoCommit property of the transaction object 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.

System database stored procedures

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.

See also

Microsoft SQL Server Retrieval

Microsoft SQL Server Temporary tables

Microsoft SQL Server Update

Microsoft SQL Server Using database stored procedures in DataWindow objects

Microsoft SQL Server Database stored procedures summary

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

Microsoft SQL Server DECLARE and EXECUTE

Microsoft SQL Server FETCH

Microsoft SQL Server 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
   SQL_Server_procedure_name
   @Param1 = value1, @Param2 = value2, 
   @Param3 = value3 OUTPUT,
   {USING transaction_object} ;

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

The parameter references can take the form of any valid parameter string that SQL Server accepts. PowerBuilder does not inspect the parameter list format except for purposes of variable substitution. You must use the reserved word OUTPUT to indicate an output parameter. The USING clause is required only if you are using a transaction object other than the default transaction object (SQLCA).

Example 1

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;

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;

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.

Example 1

FETCH emp_proc INTO :emp_name_var;

You can use this FETCH statement only to access values produced with a SELECT statement in a database stored procedure. You cannot use the FETCH statement to access computed rows.

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

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;

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

// 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.
   FETCH emp_proc2 INTO :part_name_var;

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

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.

See also

Microsoft SQL Server FETCH NEXT

Microsoft SQL Server FETCH FIRST, FETCH PRIOR, and FETCH LAST

If a database stored procedure returns a result set, it must be closed when processing is complete.

Closing a procedure looks the same as closing a cursor:

CLOSE emp_proc;

As with cursors, 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.

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

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 SQL Server procedure good_employee is:

// SQL Server 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 SQL Server procedure get_employee is:

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

PowerBuilder also provides access to return values and output parameters. The return values and output parameters are always in the last result set returned by the stored procedure and they are in this order:

return value, output parm1, output parm 2 ...

Example 3

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

CREATE PROCEDURE emp_return @m1 int, @m2 int,
@resultp int output
AS SELECT @RESULTP = @m1*@m2
RETURN 0

where @m1, @m2, and @resultp are integers.

This example shows how PowerBuilder provides access to return values:

//Stored procedure syntax
CREATE PROCEDURE sp_outputs @ml int, @m2 int,
@result int output as SELECT
@result = @ml*@m2;

//Declare syntax in script.
DECLARE myproc PROCEDURE for sp_outputs @ml = 3,
@m2 = 3, @result = 0 output;

//Note: The parameters in the declare must match
//exactly the parameters in the sp.
EXECUTE myproc;

//Execute fetches needed until rc = 100
//then fetch output parameters.
int myresult

FETCH myproc into :myresult;
CLOSE myproc;

Database stored procedures frequently contain temporary tables that are used as repositories when accumulating rows during processing within the procedure. Since versions of SQL Server prior to SQL Server 2000 do not allow Data Definition Language (DDL) to be executed within the scope of a transaction, PowerBuilder provides the boolean AutoCommit property in the transaction object to allow you to handle these cases.

When AutoCommit is false (the default), normal transaction processing takes place: a BEGIN TRANSACTION is internally issued on a successful connect and this transaction is terminated by a COMMIT TRANSACTION or ROLLBACK TRANSACTION.

When AutoCommit is set to true, no transaction management is performed. Therefore, stored procedures that create temporary tables can be executed. This option should be used with great care because of the recovery implications. If AutoCommit is true, ROLLBACK cannot be issued.

See also

Microsoft SQL Server Using CONNECT, COMMIT, DISCONNECT, and ROLLBACK

Microsoft SQL Server Performance and locking

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

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