Using Transaction objects to call stored procedures

SQLCA is a built-in global variable of type transaction that is used in all PocketBuilder applications. In your application, you can define a specialized version of SQLCA that performs certain processing or calculations on your data.

Not supported in UltraLite Stored procedures are not supported in UltraLite databases.

You might already have defined remote stored procedures to perform these operations. You can use the remote procedure call (RPC) technique to define a customized version of the Transaction object that calls these database stored procedures in your application.

Result sets You cannot use the RPC technique to access result sets returned by stored procedures. If the stored procedure returns one or more result sets, PocketBuilder ignores the values and returns the output parameters and return value. If your stored procedure returns a result set, you can use the embedded SQL DECLARE Procedure statement to call it.

For information about the DECLARE Procedure statement, see the chapter on SQL statements in the PowerScript Reference or the online Help.

Overview of the RPC procedure

To call database stored procedures from within your PocketBuilder application, you can use the remote procedure call technique and PowerScript dot notation (object.function) to define a customized version of the Transaction object that calls the stored procedures.

To call database stored procedures in your application:

From the Objects tab in the New dialog box, define a standard class user object inherited from the built-in Transaction object.
In the Script view in the User Object painter, use the RPCFUNC keyword to declare the stored procedure as an external function or subroutine for the user object.
Save the user object.
In the Application painter, specify the user object you defined as the default global variable type for SQLCA.
Code your PocketBuilder application to use the user object.

For instructions on using the User Object and Application painters and the Script view in PocketBuilder, see the User’s Guide.

Understanding the example

u_trans_database user object The following sections give step-by-step instructions for using a Transaction object to call stored procedures in your application. The example shows how to define and use a standard class user object named u_trans_database.

The u_trans_database user object is a descendant of (inherited from) the built-in Transaction object SQLCA. A descendant is an object that inherits functionality (properties, variables, functions, and event scripts) from an ancestor object. A descendent object is also called a subclass.

The example uses a simple stored procedure that takes a salary as an input and returns the value of the salary after a 5% raise:

CREATE FUNCTION DBA."sp_raise" ( salary double )
RETURNS double
BEGIN
	DECLARE salary double;
	SET salary = salary * 1.05;
	RETURN salary;
END