Using Transaction objects to call stored procedures
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.
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
the PowerBuilder 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 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 terminatorcharacter
The syntax shown here for creating an Oracle stored procedure
assumes that the SQL statement
terminator character is ` (backquote).
|
1 |
// Create GIVE_RAISE function for Oracle |
|
1 |
// SQL terminator character is ` (backquote). |
|
1 |
CREATE OR REPLACE FUNCTION give_raise |
|
1 |
(salary IN OUT NUMBER) |
|
1 |
return NUMBER |
|
1 |
IS rv NUMBER; |
|
1 |
BEGIN |
|
1 |
   salary := salary * 1.05; |
|
1 |
   rv := salary; |
|
1 |
   return rv; |
|
1 |
END; ` |
|
1 |
// Save changes. |
|
1 |
COMMIT WORK` |
|
1 |
// Check for errors. |
|
1 |
SELECT * FROM all_errors` |