Application Techniques
Using Transaction Objects

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 Using Oracle in 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:

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.

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 section called “Declaring DBMS stored procedures as remote procedure calls” in 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"

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.

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.

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.

To code your application to use the user object:

1. Open the object or control for which you want to write a script.

2. Select the event for which you want to write the script.

   For instructions on using the Script view, see the section called “Opening Script views” in Users Guide.

3. Write code that uses the user object to do the necessary processing for your application.

   Here is a simple code example that connects to an Oracle database, calls the GIVE_RAISE stored procedure to calculate the raise, displays a message box with the new salary, and disconnects from the database:

   // Set Transaction object connection properties.
   SQLCA.DBMS="OR7"
   SQLCA.LogID="scott"
   SQLCA.LogPass="xxyyzz"
   SQLCA.ServerName="@t:oracle:testdb"
   SQLCA.DBParm="sqlcache=24,pbdbms=1"
    
   // Connect to the Oracle database.
   CONNECT USING SQLCA ;
    
   // Check for errors.
   IF SQLCA.sqlcode <> 0 THEN
         MessageBox ("Connect Error",SQLCA.SQLErrText)
         return
   END IF
    
   // Set 20,000 as the current salary.
   DOUBLE val = 20000
   DOUBLE rv
    
   // Call the GIVE_RAISE stored procedure to
   // calculate the raise.
   // Use dot notation to call the stored procedure
   rv = SQLCA.give_raise(val)
    
   // Display a message box with the new salary.
   MessageBox("The new salary is",string(rv))
    
   // Disconnect from the Oracle database.
   DISCONNECT USING SQLCA;

4. Compile the script to save your changes.