Advanced PowerBuilder

Stored Procedures - External Functions

As you learned in the previous sessions, you can execute a database stored procedure in two different ways, from the DataWindow, and from the PowerScript using embedded/dynamic SQL.

You can also execute stored procedures by declaring them as external functions, as long as they don't return any result set.You can declare a stored procedure as an external function only in the transaction type user object. In the user object's session, we created "uo_transaction" user object, which is of "transaction" type.  If you open "uo_transaction" and select "Declare/Local External Functions" from the menu, you will find "Procedures" button in the dialog box.

Let's create a stored procedure to find out the number of products in the "product_master" table. Invoke the database administration painter from the database painter. Type the following and execute it by pressing CTRL + L keys.

create procedure GetProductCount
      (@TotalCount integer output)
as
begin
   select @TotalCount=count(*) from product_master
   return(0)
end;

Let's execute the above procedure through external access mechanism. Open the user object "uo_transaction" and select "Declare/Local External Functions" from the menu. Click on the "Procedures" Command Button and select GetProductCount procedure and click OK CommandButton. You will see the syntax declaration something like:

subroutine GetProductCount(ref long TotalCount) &
RPCFUNC ALIAS FOR "~"dba~".~"abc1~""

Just for demonstration sake, open the application painter and type the following code after the Open( w_login ) line:

long l_ProductCount
SQLCA.GetProductCount( l_ProductCount )
MessageBox( "Total No. of Products in the Database", &
            l_ProductCount)

Run the application and test it.

While creating the stored procedure, we declared the parameter with an OUTPUT keyword. It means, the changed value of that variable is available back to the client ( PowerBuilder ). Please note that we are executing the stored procedure as if we were calling a function at an object, using PowerBuilder's dot notation. The following is the syntax for declaring stored procedures as external functions.

FUNCTION rtndatatype functionname &
({ { REF } datatype1 arg1, ..., { REF } datatypen argn }) &
RPCFUNC { ALIAS FOR "spname" }

SUBROUTINE functionname &
( { { REF } datatype1 arg1,...,{ REF } datatypen argn } ) &
RPCFUNC { ALIAS FOR "spname" }

The '&' symbol is not part of the syntax, however you need to use the line continuation character if the declaration spans multiple lines. If the stored procedure returns a value, the function ( FUNCTION keyword ) is declared, otherwise the subroutine ( SUBROUTINE keyword ) is declared. A stored procedure should not return a result set. However, you can use OUT keyword in Watcom database and OUTPUT keyword in Sybase for parameter declaration.

You must use the RPCFUNC keyword in the function or subroutine declaration, to indicate a remote procedure call ( RPC ) for a database stored procedure rather than for an external function in a DLL. Optionally, you can use the ALIAS FOR "spname" expression, to supply the name of the stored procedure as it appears in the database, if this name differs from the one you want to use in the script.

Home	Previous Lesson: Call-Back External Functions Next Lesson: Cross Platform Issues