When transaction pooling is in effect, PowerBuilder logically terminates the database connections and commits any database changes, but does not physically remove them. Instead, the database connections are kept open in the transaction pool so that they can be reused for other database operations.
When to use it Transaction pooling can enhance the performance of an application that services a high volume of short transactions to the same data source.
How to use it To establish a transaction pool, you use the SetTransPool function. You can code SetTransPool anywhere in your application, as long as it is executed before the application connects to the database. A logical place to execute SetTransPool is in the application Open event.
Example This statement specifies that up to 16 database connections will be supported through this application, and that 12 connections will be kept open once successfully connected. When the maximum number of connections has been reached, each subsequent connection request will wait for up to 10 seconds for a connection in the pool to become available. After 10 seconds, the application will return an error:
myapp.SetTransPool (12,16,10)
For more information For more information about the SetTransPool function, see the PowerScript Reference.
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:
1 From the Objects tab in the New dialog box, define a standard class user object inherited from the built-in Transaction object.
2 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.
3 Save the user object.
4 In the Application painter, specify the user object you defined as the default global variable type for SQLCA.
5 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 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.
Using Transaction objects to call stored procedures
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`
Step 1: define the standard class user object
❖ To define the standard class user object:
1 Start PowerBuilder.
2 Connect to a database that supports stored procedures.
The rest of this procedure assumes you are connected to an Oracle database that contains remote stored procedures on the database server.
For instructions on connecting to an Oracle database in PowerBuilder and using Oracle stored procedures, see Connecting to Your Database.
3 Click the New button in the PowerBar, or select File>New from the menu bar.
The New dialog box displays.
4 On the Object tab, select the Standard Class icon and click OK to define a new standard class user object.
The Select Standard Class Type dialog box displays:
5 Select transaction as the built-in system type that you want your user object to inherit from, and click OK.
The User Object painter workspace displays so that you can assign properties (instance variables) and functions to your user object:
Step 2: declare the stored procedure as an external function
FUNCTION or SUBROUTINE declaration
You can declare a non-result-set database stored procedure as an external function or external subroutine in a PowerBuilder application. If the stored procedure has a return value, declare it as a function (using the FUNCTION keyword). If the stored procedure returns nothing or returns VOID, declare it as a subroutine (using the SUBROUTINE keyword).
RPCFUNC and ALIAS
FOR keywords You must use the RPCFUNC keyword in the function or subroutine declaration to indicate that this is a remote procedure call (RPC) for a database stored procedure rather than for an external function in a dynamic library. 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 your script.
For complete information about the syntax for declaring stored procedures as remote procedure calls, see the chapter on calling functions and events in the PowerScript Reference.
❖ To declare stored procedures as external functions for the user object:
1 In the Script view in the User Object painter, select [Declare] from the first list and Local External Functions from the second list.
Using Transaction objects to call stored procedures
2 Place your cursor in the Declare Local External Functions view. From the pop-up menu or the Edit menu, select Paste Special>SQL>Remote Stored Procedures.
PowerBuilder loads the stored procedures from your database and displays the Remote Stored Procedures dialog box. It lists the names of stored procedures in the current database.
3 Select the names of one or more stored procedures that you want to declare as functions for the user object, and click OK.
PowerBuilder retrieves the stored procedure declarations from the database and pastes each declaration into the view.
For example, here is the declaration that displays on one line when you select sp_addlanguage:
function long sp_addlanguage()
RPCFUNC ALIAS FOR "dbo.sp_addlanguage"
4 Edit the stored procedure declaration as needed for your application.
Use either of the following syntax formats to declare the database remote procedure call (RPC) as an external function or external subroutine (for details about the syntax, see the PowerScript Reference):
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" } Here is the edited RPC function declaration for sp_addlanguage:
FUNCTION long sp_addlanguage()
RPCFUNC ALIAS FOR "addlanguage_proc"
Step 3: save the user object
❖ To save the user object:
1 In the User Object painter, click the Save button, or select File>Save from the menu bar.
The Save User Object dialog box displays.
2 Specify the name of the user object, comments that describe its purpose, and the library in which to save the user object.
3 Click OK to save the user object.
PowerBuilder saves the user object with the name you specified in the selected library.
Step 4: specify the default global variable type for SQLCA
In the Application painter, you must specify the user object you defined as the default global variable type for SQLCA. When you execute your application, this tells PowerBuilder to use your standard class user object instead of the built-in system Transaction object.
Using your own Transaction object instead of SQLCA
This procedure assumes that your application uses the default Transaction object SQLCA, but you can also declare and create an instance of your own Transaction object and then write code that calls the user object as a property of your Transaction object. For instructions, see the chapter on working with user objects in the PowerBuilder User’s Guide.
❖ To specify the default global variable type for SQLCA:
1 Click the Open button in the PowerBar, or select File>Open from the menu bar.
The Open dialog box displays.
2 Select Applications from the Object Type drop-down list. Choose the application where you want to use your new user object and click OK.
The Application painter workspace displays.
Using Transaction objects to call stored procedures
3 Select the General tab in the Properties view. Click the Additional Properties button.
The Additional Properties dialog box displays.
4 Click the Variable Types tab to display the Variable Types property page.
5 In the SQLCA box, specify the name of the standard class user object you defined in Steps 1 through 3:
6 Click OK or Apply.
When you execute your application, PowerBuilder will use the specified standard class user object instead of the built-in system object type it inherits from.
Step 5: code your application to use the user object
What you have done so far In the previous steps, you defined the GIVE_RAISE remote stored procedure as an external function for the u_trans_database standard class user object. You then specified
u_trans_database as the default global variable type for SQLCA. These steps give your PowerBuilder application access to the properties and functions encapsulated in the user object.