Using Transaction Objects

About this chapter

This chapter describes Transaction objects and how to use them in PowerBuilder applications.

About Transaction objects

In a PowerBuilder database connection, a Transaction object is a special nonvisual object that functions as the communications area between a PowerBuilder application and the database. The Transaction object specifies the parameters that PowerBuilder uses to connect to a database. You must establish the Transaction object before you can access the database from your application, as shown in the following figure:

Figure: Transaction object to access database

Communicating with the database

In order for a PowerBuilder application to display and manipulate data, the application must communicate with the database in which the data resides.

To communicate with the database from your PowerBuilder application:

  1. Assign the appropriate values to the Transaction object.

  2. Connect to the database.

  3. Assign the Transaction object to the DataWindow control.

  4. Perform the database processing.

  5. Disconnect from the database.

For information about setting the Transaction object for a DataWindow control and using the DataWindow to retrieve and update data, see DataWindow Programmers Guide.

Default Transaction object

When you start executing an application, PowerBuilder creates a global default Transaction object named SQLCA (SQL Communications Area). You can use this default Transaction object in your application or define additional Transaction objects if your application has multiple database connections.

Transaction object properties

Each Transaction object has 15 properties, of which:

  • Ten are used to connect to the database.

  • Five are used to receive status information from the database about the success or failure of each database operation. (These error-checking properties all begin with SQL.)

Description of Transaction object properties

The following table describes each Transaction object property. For each of the ten connection properties, it also lists the equivalent field in the Database Profile Setup dialog box that you complete to create a database profile in the PowerBuilder development environment.

Transaction object properties for your PowerBuilder database interface

For the Transaction object properties that apply to your PowerBuilder database interface, see Transaction object properties and supported PowerBuilder database interfaces.

For information about the values you should supply for each connection property, see the section for your PowerBuilder database interface in Connecting to Your Database.

Property

Datatype

Description

In a database profile

DBMS

String

The DBMS identifier for your connection. For a complete list of the identifiers for the supported database interfaces, see Connection Reference.

DBMS

Database

String

The name of the database to which you are connecting.

Database Name

UserID

String

The name or ID of the user who connects to the database.

User ID

DBPass

String

The password used to connect to the database.

Password

Lock

String

For those DBMSs that support the use of lock values and isolation levels, the isolation level to use when you connect to the database. For information about the lock values you can set for your DBMS, see the description of the Lock DBParm parameter in the section called “Lock” in Connection Reference.

Isolation Level

LogID

String

The name or ID of the user who logs in to the database server.

Login ID

LogPass

String

The password used to log in to the database server.

Login Password

ServerName

String

The name of the server on which the database resides.

Server Name

AutoCommit

Boolean

For those DBMSs that support it, specifies whether PowerBuilder issues SQL statements outside or inside the scope of a transaction. Values you can set are:

  • True

    PowerBuilder issues SQL statements outside the scope of a transaction; that is, the statements are not part of a logical unit of work (LUW). If the SQL statement succeeds, the DBMS updates the database immediately as if a COMMIT statement had been issued.

  • False

    (Default) PowerBuilder issues SQL statements inside the scope of a transaction. PowerBuilder issues a BEGIN TRANSACTION statement at the start of the connection. In addition, PowerBuilder issues another BEGIN TRANSACTION statement after each COMMIT or ROLLBACK statement is issued.

    For more information, see the AutoCommit description in the section called “AutoCommit” in Connection Reference.

AutoCommit Mode

DBParm

String

Contains DBMS-specific connection parameters that support particular DBMS features. For a description of each DBParm parameter that PowerBuilder supports, see the section called “Setting Additional Connection Parameters” in Connecting to Your Database.

DBPARM

SQLReturnData

String

Contains DBMS-specific information. For example, after you connect to an Informix database and execute an embedded SQL INSERT statement, SQLReturnData contains the serial number of the inserted row.

SQLCode

Long

The success or failure code of the most recent SQL operation. For details, see Error handling after a SQL statement.

SQLNRows

Long

The number of rows affected by the most recent SQL operation. The database vendor supplies this number, so the meaning may be different for each DBMS.

SQLDBCode

Long

The database vendor's error code. For details, see Error handling after a SQL statement.

SQLErrText

String

The text of the database vendor's error message corresponding to the error code. For details, see Error handling after a SQL statement.


Transaction object properties and supported PowerBuilder database interfaces

The Transaction object properties required to connect to the database are different for each PowerBuilder database interface. Except for SQLReturnData, the properties that return status information about the success or failure of a SQL statement apply to all PowerBuilder database interfaces.

The following table lists each supported PowerBuilder database interface and the Transaction object properties you can use with that interface.

Database interface

Transaction object properties

Informix

DBMS

UserID

DBPass

Database

ServerName

DBParm

Lock

AutoCommit

SQLReturnData

SQLCode

SQLNRows

SQLDBCode

SQLErrText

JDBC

DBMS

LogID

LogPass

DBParm

Lock

AutoCommit

SQLCode

SQLNRows

SQLDBCode

SQLErrText

Microsoft SQL Server

DBMS

Database

ServerName

LogID

LogPass

DBParm

Lock

AutoCommit

SQLCode

SQLNRows

SQLDBCode

SQLErrText

ODBC

DBMS

UserID*

LogID#

LogPass#

DBParm

Lock

AutoCommit

SQLReturnData

SQLCode

SQLNRows

SQLDBCode

SQLErrText

OLE DB

DBMS

LogID

LogPass

DBParm

AutoCommit

SQLCode

SQLNRows

SQLDBCode

SQLErrText

Oracle

DBMS

ServerName

LogID

LogPass

DBParm

SQLReturnData

SQLCode

SQLNRows

SQLDBCode

SQLErrText

SAP Sybase DirectConnect

DBMS

Database

ServerName

LogID

LogPass

DBParm

Lock

AutoCommit

SQLCode

SQLNRows

SQLDBCode

SQLErrText

SAP Adaptive Server Enterprise

DBMS

Database

ServerName

LogID

LogPass

DBParm

Lock

AutoCommit

SQLCode

SQLNRows

SQLDBCode

SQLErrText


* UserID is optional for ODBC. (Be careful specifying the UserID property; it overrides the connection's UserName property returned by the ODBC SQLGetInfo call.)

# PowerBuilder uses the LogID and LogPass properties only if your ODBC driver does not support the SQL driver CONNECT call.

Working with Transaction objects

PowerBuilder uses a basic concept of database transaction processing called logical unit of work (LUW). LUW is synonymous with transaction. A transaction is a set of one or more SQL statements that forms an LUW. Within a transaction, all SQL statements must succeed or fail as one logical entity.

There are four PowerScript transaction management statements:

  • COMMIT

  • CONNECT

  • DISCONNECT

  • ROLLBACK

Transaction basics

CONNECT and DISCONNECT

A successful CONNECT starts a transaction, and a DISCONNECT terminates the transaction. All SQL statements that execute between the CONNECT and the DISCONNECT occur within the transaction.

Before you issue a CONNECT statement, the Transaction object must exist and you must assign values to all Transaction object properties required to connect to your DBMS.

COMMIT and ROLLBACK

When a COMMIT executes, all changes to the database since the start of the current transaction (or since the last COMMIT or ROLLBACK) are made permanent, and a new transaction is started. When a ROLLBACK executes, all changes since the start of the current transaction are undone and a new transaction is started.

When a transactional component is deployed to an application server, you can use the TransactionServer context object to control transactions. See Transaction server deployment.

AutoCommit setting

You can issue a COMMIT or ROLLBACK only if the AutoCommit property of the Transaction object is set to False (the default) and you have not already started a transaction using embedded SQL.

For more about AutoCommit, see Description of Transaction object properties.

Automatic COMMIT when disconnected

When a transaction is disconnected, PowerBuilder issues a COMMIT statement.

Transaction pooling

To optimize database processing, you can code your PowerBuilder application to take advantage of transaction pooling.

For information, see Pooling database transactions.

Transaction server deployment

Components that you develop in PowerBuilder can participate in transactions in application servers. You can mark components to indicate that they will provide transaction support. When a component provides transaction support, the transaction server ensures that the component's database operations execute as part of a transaction and that the database changes performed by the participating components are all committed or rolled back. By defining components to use transactions, you can ensure that all work performed by components that participate in a transaction occurs as intended.

PowerBuilder provides a transaction service context object called TransactionServer that gives you access to the transaction state primitives that influence whether the transaction server commits or aborts the current transaction. COM+ clients can also use the OleTxnObject object to control transactions. If you use the TransactionServer context object and set the UseContextObject DBParm parameter to Yes, COMMIT and ROLLBACK statements called in the Transaction object will result in a database error.

By default, the TransactionServer context object is not used. Instead you can use COMMIT and ROLLBACK statements to manage transactions. In this case, COMMIT is interpreted as a SetComplete function and ROLLBACK is interpreted as a SetAbort function.

The default Transaction object

SQLCA

Since most applications communicate with only one database, PowerBuilder provides a global default Transaction object called SQLCA (SQL Communications Area).

PowerBuilder creates the Transaction object before the application's Open event script executes. You can use PowerScript dot notation to reference the Transaction object in any script in your application.

You can create additional Transaction objects as you need them (such as when you are using multiple database connections at the same time). But in most cases, SQLCA is the only Transaction object you need.

Example

This simple example uses the default Transaction object SQLCA to connect to and disconnect from an ODBC data source named Sample:

// Set the default Transaction object properties.
SQLCA.DBMS="ODBC"
SQLCA.DBParm="ConnectString='DSN=Sample'"
// Connect to the database.
CONNECT USING SQLCA;
IF SQLCA.SQLCode < 0 THEN &
   MessageBox("Connect Error", SQLCA.SQLErrText,&
   Exclamation!)
...
// Disconnect from the database.
DISCONNECT USING SQLCA;
IF SQLCA.SQLCode < 0 THEN &
   MessageBox("Disconnect Error", SQLCA.SQLErrText,&
      Exclamation!)

Semicolons are SQL statement terminators

When you use embedded SQL in a PowerBuilder script, all SQL statements must be terminated with a semicolon (;). You do not use a continuation character for multiline SQL statements.

Assigning values to the Transaction object

Before you can use a default (SQLCA) or nondefault (user-defined) Transaction object, you must assign values to the Transaction object connection properties. To assign the values, use PowerScript dot notation.

Example

The following PowerScript statements assign values to the properties of SQLCA required to connect to an SAP Adaptive Server Enterprise database through the PowerBuilder Adaptive Server Enterprise database interface:

sqlca.DBMS="SYC"
sqlca.database="testdb"
sqlca.LogId="CKent"
sqlca.LogPass="superman"
sqlca.ServerName="Dill"

Reading values from an external file

Using external files

Often you want to set the Transaction object values from an external file. For example, you might want to retrieve values from your PowerBuilder initialization file when you are developing the application or from an application-specific initialization file when you distribute the application.

ProfileString function

You can use the PowerScript ProfileString function to retrieve values from a text file that is structured into sections containing variable assignments, like a Windows INI file. The PowerBuilder initialization file is such a file, consisting of several sections including PB, Application, and Database:

[PB]
variables and their values
...
[Application]
variables and their values
...
[Database]
variables and their values
...

The ProfileString function has this syntax:

ProfileString ( file, section, key, default )

Example

This script reads values from an initialization file to set the Transaction object to connect to a database. Conditional code sets the variable startupfile to the appropriate value for each platform:

sqlca.DBMS = ProfileString(startupfile, "database",&
   "dbms", "")
sqlca.database = ProfileString(startupfile,&
   "database", "database", "")
sqlca.userid = ProfileString(startupfile, "database",&
   "userid", "")
sqlca.dbpass = ProfileString(startupfile, "database",&
   "dbpass", "")
sqlca.logid = ProfileString(startupfile, "database",&
   "logid", "")
sqlca.logpass = ProfileString(startupfile, "database",&
   "LogPassWord","")
sqlca.servername = ProfileString(startupfile,&
   "database", "servername","")
sqlca.dbparm = ProfileString(startupfile, "database",&
   "dbparm", "")

Connecting to the database

Once you establish the connection parameters by assigning values to the Transaction object properties, you can connect to the database using the SQL CONNECT statement:

// Transaction object values have been set.
CONNECT;

Because CONNECT is a SQL statement -- not a PowerScript statement -- you need to terminate it with a semicolon.

If you are using a Transaction object other than SQLCA, you must include the USING TransactionObject clause in the SQL syntax:

CONNECT USING TransactionObject;

For example:

CONNECT USING ASETrans;

Using the Preview tab to connect in a PowerBuilder application

The Preview tab page in the Database Profile Setup dialog box makes it easy to generate accurate PowerScript connection syntax in the development environment for use in your PowerBuilder application script.

As you complete the Database Profile Setup dialog box, the correct PowerScript connection syntax for each selected option is generated on the Preview tab. PowerBuilder assigns the corresponding DBParm parameter or SQLCA property name to each option and inserts quotation marks, commas, semicolons, and other characters where needed. You can copy the syntax you want from the Preview tab directly into your script.

To use the Preview tab to connect in a PowerBuilder application:

  1. In the Database Profile Setup dialog box for your connection, supply values for basic options (on the Connection tab) and additional DBParm parameters and SQLCA properties (on the other tabbed pages) as required by your database interface.

    For information about connection parameters for your interface and the values you should supply, click Help.

  2. Click Apply to save your settings without closing the Database Profile Setup dialog box.

  3. Click the Preview tab.

    The correct PowerScript connection syntax for each selected option displays in the Database Connection Syntax box on the Preview tab.

  4. Select one or more lines of text in the Database Connection Syntax box and click Copy.

    PowerBuilder copies the selected text to the clipboard. You can then paste this syntax into your script, modifying the default Transaction object name (SQLCA) if necessary.

  5. Click OK.

Disconnecting from the database

When your database processing is completed, you disconnect from the database using the SQL DISCONNECT statement:

DISCONNECT;

If you are using a Transaction object other than SQLCA, you must include the USING TransactionObject clause in the SQL syntax:

DISCONNECT USING TransactionObject;

For example:

DISCONNECT USING ASETrans;

Automatic COMMIT when disconnected

When a transaction is disconnected, PowerBuilder issues a COMMIT statement by default.

Defining Transaction objects for multiple database connections

Use one Transaction object per connection

To perform operations in multiple databases at the same time, you need to use multiple Transaction objects, one for each database connection. You must declare and create the additional Transaction objects before referencing them, and you must destroy these Transaction objects when they are no longer needed.

Caution

PowerBuilder creates and destroys SQLCA automatically. Do not attempt to create or destroy it.

Creating the nondefault Transaction object

To create a Transaction object other than SQLCA, you first declare a variable of type transaction:

transaction TransactionObjectName

You then instantiate the object:

TransactionObjectName = CREATE transaction

For example, to create a Transaction object named DBTrans, code:

transaction DBTrans
DBTrans = CREATE transaction
// You can now assign property values to DBTrans.
DBTrans.DBMS = "ODBC"
...

Assigning property values

When you assign values to properties of a Transaction object that you declare and create in a PowerBuilder script, you must assign the values one property at a time, like this:

// This code produces correct results.
transaction ASETrans
ASETrans = CREATE TRANSACTION
ASETrans.DBMS = "SYC"
ASETrans.Database = "Personnel"

You cannot assign values by setting the nondefault Transaction object equal to SQLCA, like this:

// This code produces incorrect results.
transaction      ASETrans
ASETrans = CREATE TRANSACTION
ASETrans = SQLCA // ERROR!

Specifying the Transaction object in SQL statements

When a database statement requires a Transaction object, PowerBuilder assumes the Transaction object is SQLCA unless you specify otherwise. These CONNECT statements are equivalent:

CONNECT;
CONNECT USING SQLCA;

However, when you use a Transaction object other than SQLCA, you must specify the Transaction object in the SQL statements in the following table with the USING TransactionObject clause.

COMMIT

INSERT

CONNECT

PREPARE (dynamic SQL)

DELETE

ROLLBACK

DECLARE Cursor

SELECT

DECLARE Procedure

SELECTBLOB

DISCONNECT

UPDATEBLOB

EXECUTE (dynamic SQL)

UPDATE


To specify a user-defined Transaction object in SQL statements:

  • Add the following clause to the end of any of the SQL statements in the preceding list:

    USING TransactionObject

    For example, this statement uses a Transaction object named ASETrans to connect to the database:

    CONNECT USING ASETrans;

Always code the Transaction object

Although specifying the USING TransactionObject clause in SQL statements is optional when you use SQLCA and required when you define your own Transaction object, it is good practice to code it for any Transaction object, including SQLCA. This avoids confusion and ensures that you supply USING TransactionObject when it is required.

Example

The following statements use the default Transaction object (SQLCA) to communicate with a SQL Anywhere database and a nondefault Transaction object named ASETrans to communicate with an Adaptive Server Enterprise database:

// Set the default Transaction object properties.
SQLCA.DBMS = "ODBC"
SQLCA.DBParm = "ConnectString='DSN=Sample'"
// Connect to the SQL Anywhere database.
CONNECT USING SQLCA;
// Declare the ASE Transaction object.
transaction ASETrans
// Create the ASE Transaction object.
ASETrans = CREATE TRANSACTION
// Set the ASE Transaction object properties.
ASETrans.DBMS = "SYC"
ASETrans.Database = "Personnel"
ASETrans.LogID = "JPL"
ASETrans.LogPass = "JPLPASS"
ASETrans.ServerName = "SERVER2"
 
// Connect to the ASE database.
CONNECT USING ASETrans;
 
// Insert a row into the SQL Anywhere database
INSERT INTO CUSTOMER
VALUES ( 'CUST789', 'BOSTON' )
USING SQLCA;
// Insert a row into the ASE database.
INSERT INTO EMPLOYEE
VALUES ( "Peter Smith", "New York" )
USING ASETrans;
 
// Disconnect from the SQL Anywhere database
DISCONNECT USING SQLCA;
// Disconnect from the ASE database.
DISCONNECT USING ASETrans;
// Destroy the ASE Transaction object.
DESTROY ASETrans

Using error checking

An actual script would include error checking after the CONNECT, INSERT, and DISCONNECT statements.

For details, see Error handling after a SQL statement.

Error handling after a SQL statement

When to check for errors

You should always test the success or failure code (the SQLCode property of the Transaction object) after issuing one of the following statements in a script:

  • Transaction management statement (such as CONNECT, COMMIT, and DISCONNECT)

  • Embedded or dynamic SQL

Not in DataWindows

Do not do this type of error checking following a retrieval or update made in a DataWindow.

For information about handling errors in DataWindows, see the section called “Handling DataWindow errors” in DataWindow Programmers Guide.

SQLCode return values

The following table shows the SQLCode return values.

Value

Meaning

0

Success

100

Fetched row not found

-1

Error (the statement failed)

Use SQLErrText or SQLDBCode to obtain the details.


Using SQLErrText and SQLDBCode

The string SQLErrText in the Transaction object contains the database vendor-supplied error message. The long named SQLDBCode in the Transaction object contains the database vendor-supplied status code. You can reference these variables in your script.

Example

To display a message box containing the DBMS error number and message if the connection fails, code the following:

CONNECT USING SQLCA;
IF SQLCA.SQLCode = -1 THEN
   MessageBox("SQL error " + String(SQLCA.SQLDBCode),&
   SQLCA.SQLErrText )
END IF

Pooling database transactions

Transaction pooling

To optimize database processing, an application can pool database transactions. Transaction pooling maximizes database throughput while controlling the number of database connections that can be open at one time. When you establish a transaction pool, an application can reuse connections made to the same data source.

How it works

When an application connects to a database without using transaction pooling, PowerBuilder physically terminates each database transaction for which a DISCONNECT statement is issued.

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 section called “SetTransPool” in 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 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:

  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 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).

// 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 the section called “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:

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 section called “Calling Functions and Events” in 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.

  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"

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 section called “Working with User Objects” in Users 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.

  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.

What you do now

You now need to write code that uses the user object to perform the necessary processing.

In your application script, you can use PowerScript dot notation to call the stored procedure functions you defined for the user object, just as you do when using SQLCA for all other PowerBuilder objects. The dot notation syntax is:

object.function ( arguments )

For example, you can call the GIVE_RAISE stored procedure with code similar to the following:

SQLCA.give_raise(salary)

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.

Using error checking

An actual script would include error checking after the CONNECT statement, DISCONNECT statement, and call to the GIVE_RAISE procedure. For details, see Error handling after a SQL statement.

Supported DBMS features when calling stored procedures

When you define and use a custom Transaction object to call remote stored procedures in your application, the features supported depend on the DBMS to which your application connects.

The following sections describe the supported features for some of the DBMSs that you can access in PowerBuilder. Read the section for your DBMS to determine what you can and cannot do when using the RPC technique in a PowerBuilder application.

Result sets

You cannot use the remote procedure call 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.

Informix

If your application connects to an Informix database, you can use simple nonarray datatypes. You cannot use binary large objects (blobs).

ODBC

If your application connects to an ODBC data source, you can use the following ODBC features if the back-end driver supports them. (For information, see the documentation for your ODBC driver.)

  • IN, OUT, and IN OUT parameters, as shown in the following table.

    Parameter

    What happens

    IN

    An IN variable is passed by value and indicates a value being passed to the procedure.

    OUT

    An OUT variable is passed by reference and indicates that the procedure can modify the PowerScript variable that was passed. Use the PowerScript REF keyword for this parameter type.

    IN OUT

    An IN OUT variable is passed by reference and indicates that the procedure can reference the passed value and can modify the PowerScript variable. Use the PowerScript REF keyword for this parameter type.


  • Blobs as parameters. You can use blobs that are up to 32,512 bytes long.

  • Integer return codes.

Oracle

If your application connects to an Oracle database, you can use the following Oracle PL/SQL features:

  • IN, OUT, and IN OUT parameters, as shown in the following table.

    Parameter

    What happens

    IN

    An IN variable is passed by value and indicates a value being passed to the procedure.

    OUT

    An OUT variable is passed by reference and indicates that the procedure can modify the PowerScript variable that was passed. Use the PowerScript REF keyword for this parameter type.

    IN OUT

    An IN OUT variable is passed by reference and indicates that the procedure can reference the passed value and can modify the PowerScript variable. Use the PowerScript REF keyword for this parameter type.


  • Blobs as parameters. You can use blobs that are up to 32,512 bytes long.

  • PL/SQL tables as parameters. You can use PowerScript arrays.

  • Function return codes.

Microsoft SQL Server or SAP Adaptive Server Enterprise

If your application connects to a Microsoft SQL Server or SAP Adaptive Server Enterprise database, you can use the following Transact-SQL features:

  • IN, OUT, and IN OUT parameters, as shown in the following table.

    Parameter

    What happens

    IN

    An IN variable is passed by value and indicates a value being passed to the procedure.

    OUT

    An OUT variable is passed by reference and indicates that the procedure can modify the PowerScript variable that was passed. Use the PowerScript REF keyword for this parameter type.

    IN OUT

    An IN OUT variable is passed by reference and indicates that the procedure can reference the passed value and can modify the PowerScript variable. Use the PowerScript REF keyword for this parameter type.


  • Blobs as parameters. You can use blobs that are up to 32,512 bytes long.

  • Integer return codes.

SQL Anywhere

If your application connects to a SQL Anywhere database, you can use the following SQL Anywhere features:

  • IN, OUT, and IN OUT parameters, as shown in the following table.

    Parameter

    What happens

    IN

    An IN variable is passed by value and indicates a value being passed to the procedure.

    OUT

    An OUT variable is passed by reference and indicates that the procedure can modify the PowerScript variable that was passed. Use the PowerScript REF keyword for this parameter type.

    IN OUT

    An IN OUT variable is passed by reference and indicates that the procedure can reference the passed value and can modify the PowerScript variable. Use the PowerScript REF keyword for this parameter type.


  • Blobs as parameters. You can use blobs that are up to 32,512 bytes long.