Using
Transaction objects to call stored procedures
Contents
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 section called “SQL Statements” in PowerScript Reference.
Overview of the RPC
procedure
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 Users 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 descendant 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).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
// 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` |