SQLCA is a built-in global variable of type transaction that is used in all PowerBuilder applications. In your application, you can define a specialized version of SQLCA that performs certain processing or calculations on your data.
If your database supports stored procedures, 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, PowerBuilder 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.
To call database stored procedures from within your PowerBuilder 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 PowerBuilder application to use the user object.
For instructions on using the User Object and Application painters and the Script view in PowerBuilder, see the PowerBuilder Users Guide.
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.
GIVE_RAISE stored procedure The u_trans_database user object calls an Oracle database stored procedure named GIVE_RAISE that calculates a five percent raise on the current salary. Here is the Oracle syntax to create the GIVE_RAISE stored procedure:
SQL terminator
character
The syntax shown here for creating an Oracle stored procedure
assumes that the SQL statement
terminator character is ` (backquote).
// Create GIVE_RAISE function for Oracle
// SQL terminator character is ` (backquote).
CREATE OR REPLACE FUNCTION give_raise
(salary IN OUT NUMBER)
return NUMBER
IS rv NUMBER;
BEGIN
salary := salary * 1.05;
rv := salary;
return rv;
END; `
// Save changes.
COMMIT WORK`
// Check for errors.
SELECT * FROM all_errors`
