Advanced PowerBuilder

Stored Procedures

Stored procedures are collections of SQL statements, which are stored in the database. When a stored procedure is first run, an execution plan is prepared, which makes any subsequent execution of the procedure fast. Also, since the stored procedure is stored in the database, the client application only needs to send the stored procedure name and parameters if any, for the procedure to run. This reduces network traffic and makes the execution faster.

If the database back-end supports stored procedures, you can execute them from a PowerBuilder application's script. Both SQL AnyWhere and SQL Server support stored procedures.

Let's look at the syntax involved in each.

SQL AnyWhere Stored Procedures

A simple example of a procedure is shown below:

CREATE PROCEDURE sp_list_item_product_balance
   (IN pProduct_balance integer)
RESULT(product_no integer, product_description char(32),
       product_balance integer)
BEGIN
SELECT product_no, product_description, product_balance
   FROM product_master
   WHERE product_balance > pProduct_balance
END ;

Note that we place any required parameters into the brackets following the procedure name. The keyword IN specifies the purpose of the parameter, and can be either IN, OUT or INOUT, and is followed by the name of the parameter and its data type.

In the result parentheses, you need to declare all the columns returned by the SELECT statement. We then declare the SELECT statement between the BEGIN and END keywords.

The names in the result declaration can be same as the column names.

We could execute this procedure from the Database Administration Painter, using the following syntax:

EXECUTE sp_list_item_product_balance( 1000 );

There are many limitations to SQL AnyWhere stored procedures, such as, without conditional statements, you can't have more than one SQL statement and you can't specify a default value to be used as the parameter.

SQL Server Stored Procedures

On the other hand, SQL Server stored procedures are both powerful and flexible. You specify the OUTPUT keyword only for those parameters for which you want a value to be returned. You can specify default values and have as many SQL statements as you want. The syntax is as follows:

CREATE PROCEDURE Proc2
@Product_balance int, @affected_count int =NULL OUTPUT
as

SELECT product_no, product_description, product_balance
FROM product_master
WHERE product_balance > @product_balance

SELECT @affected_count = @@rowcount

SELECT @affected_count

return (0)

Note that in SQL Server, there is no need to declare the result set.

To execute a SQL Server stored procedure in the Database Administration Painter, we would use the following:

EXECUTE Proc2 @product_balance = 100;

Note that you don't need to supply parentheses for the parameters, when executing a stored procedure residing in the SQL Server.

Using a Stored Procedure in PowerScript

As we've already seen, you can execute a stored procedure by specifying it as a data source to a DataWindow. To execute a stored procedure through PowerScript, you can either use Embedded SQL or Dynamic SQL.

There are four steps involved in using a stored procedure through Embedded SQL:

	Declaration.
	Execution.
	Retrieving rows.
	Closing.

Declaring a Stored Procedure

To declare a stored procedure, you need to click on icon or select "Edit/Past SQL" from the menu. Click on "Declare" icon under the "Procedure" group and click the OK button. This brings up a list of currently available procedures:

When you select the procedure you want, you'll be prompted to supply parameters that are required:

This allows you to select from a predefined list of available program variables. For example, if you have a SingleLineEdit control on your window, you can supply the text attribute as a parameter, so that a user could type in a value at run-time.

Once you supply the parameter values, you'll be prompted for the procedure name, which would be used to refer to the stored procedure in PowerBuilder:

The declaration statement will then be pasted into your script:

DECLARE TestProc1 PROCEDURE FOR sp_list_item_product_balance

pProduct_balance = :sle_balance.Text ;

Executing a Stored Procedure

To execute a stored procedure, you simply use the EXECUTE command in your PowerScript:

EXECUTE TestProc1();

This is similar to the cursor's OPEN command, so it won't surprise you to know that we can also use the FETCH command to retrieve the data.

Executing a Remote Stored Procedure

When connected to the SQL Server, you can execute a stored procedure that is located on a remote server. To do this, you qualify the stored procedure name with the appropriate server and database names. For example, if the SQL Server stored procedure we saw above is residing on Server2 and our application is connected to Server1, we could execute the stored procedure by declaring it as follows:

DECLARE lProc_0001 PROCEDURE FOR Server2.DB1.dbo.Proc2
@Product_Balance = :sle_balance.Text,
@affected_count = :laffected_count OUTPUT ;

A remote stored procedure has a different meaning when connected to a DB2 database via MDI Database Gateway for DB2. Here, a Remote Stored Procedure (RSP) is a customer-written CICS program which can be initiated by a client application, such as PowerBuilder. The CICS program can be written in COBOL II, Assembler, PL/1 or C. RSPs are unique to the MDI Database Gateway for DB2 or MVS solution.

Here, Server1 sends the request to execute the stored procedure to Server2. Server2 then executes the procedure and sends the results back to Server1 and Server1 returns the results to the client ( PowerBuilder application ).

Retrieving Multiple Result Sets

Before talking about Multiple result sets, let's be clear about a result set. It means, the rows returned by one SELECT statement. In SQL Server, you can have as many SELECT statements as you want, and each of those SELECT statements can return rows. The columns returned by each SELECT statement can be different. For example:

Declare ProcDummy
AS

/* Result set 1, returns everything from product_master */
select * from product_master

/* Set 2, returns everything from trans table */
select * from trans

/* Set 3, returns the no. of rows for the above SELECT */
select @@rowcount

/* Not a result set, because, we are using SELECT statement
to assign a value to a variable.*/
select @dummy = 100

/* Set 4, returns everything from units table */
select * from units

return 0

While executing multiple result set stored procedures in PowerBuilder, you can retrieve only one set at a time. When the script fetches the first result set, SQLCODE is populated with the 100 result code. From then onwards, the values of the second result set are available to the script.

For example, the following script would execute an sp_help stored procedure. If you supply a table name as parameter, it would give five result sets. The script listed below fetches the first two result sets:

// This script assumes there are 2 DataWindow controls in
// the window with appropriate columns and data types.

String lObjectName, lOwner, lObjectType, lDataSegment
Long lNewRow
DateTime lCreationTime

lObjectName = "sysobjects"

DECLARE sp_help_proc PROCEDURE FOR dbo.sp_help
        @objname = :lObjectName ;

EXECUTE sp_help_proc ;
If SQLCA.SQLcode <> 0 Then
   MessageBox( "Error", SQLCA.SQLErrorText )
   Close sp_help_proc ;
   Return
End If

dwc_1.Title = "Result Set: #1"

Do While True
   Fetch sp_help_proc
      INTO :lObjectName, :lOwner, :lObjectType ;
   If SQLCA.SQLcode = 100 Then Exit
   lNewRow = dwc_1.InsertRow(0)
   dwc_1.SetItem( lNewRow, 1, lObjectName )
   dwc_1.SetItem( lNewRow, 2, lOwner )
   dwc_1.SetItem( lNewRow, 3, lObjectType )
Loop

dwc_2.Title = "Result Set: #2"

Do While True
   FETCH sp_help_proc
      INTO :lDataSegment, :lCreationTime ;
   If SQLCA.SQLCOde = 100 or SQLCA.SQLCode = -1 Then Exit
   lNewRow = dwc_2.InsertRow(0)
   dwc_2.SetItem( lNewRow, 1, lDataSegment )
   dwc_2.SetItem( lNewRow, 2, lCreationTime )
Loop

Close sp_help_proc ;

Retrieving the value of OUTPUT variable

In SQL Server, you can get the changed value of a stored procedure parameter, by declaring the parameter as OUTPUT. For example:

Declare TestProc1 @Var1 Int=NULL OUTPUT
AS
select * from trans
select @Var1 = @@RowCount
return

In the above example, we are assigning the number ( count ) of rows returned by the first SELECT statement to the @Var1 variable. @Var1 is a parameter to the stored procedure. When the client executes this stored procedure, it gets the value of @Var1. Please note the way of assigning defaults to the stored procedure parameter when user does not pass any value. In the above example, we are assigning NULL value as default to the parameter @Var1. If want to display error when user doesn't pass parameter value, then do not assign a default, for ex: Declare TestProc1 @Var1 Int OUTPUT'. In this case, Sybase automatically generates an error when parameter value is not specified at execution time.

In PowerBuilder, to retrieve the OUTPUT variable value, you need to do one more FETCH after you see 100 in the SQLCA.SQLCode, similar to the one you did in the "Multiple Result Set" stored procedure section. Make sure that the number of variables you put in the INTO clause are equal to the number of variables that are declared with OUTPUT keyword.

Home	Previous Lesson: Cursors Next Lesson: Dynamic SQL