SetTransObject

Description

Causes a DataWindow control or DataStore to use a programmer-specified transaction object. The transaction object provides the information necessary for communicating with the database.

Applies to

DataWindow type

Method applies to

PowerBuilder

DataWindow control, DataWindowChild object, DataStore object


Syntax

PowerBuilder

integer dwcontrol.SetTransObject ( transaction transaction )

Argument

Description

dwcontrol

A reference to a DataWindow control, DataStore, or child DataWindow in which you want to use a programmer-specified transaction object rather than the DataWindow control's internal transaction object

transaction

The name of the transaction object you want to use in the  dwcontrol


Return value

Returns 1 if it succeeds and -1 if an error occurs. If any argument's value is null, in PowerBuilder and JavaScript the method returns null.

Usage

Transaction objects in PowerBuilder

A programmer-specified transaction object gives you more control over the database transactions and provides efficient application performance. You control the database connection by using SQL statements such as CONNECT, COMMIT, and ROLLBACK.

Since the DataWindow control does not have to connect to the database for every RETRIEVE and UPDATE statement, these statements run faster. You are responsible for committing and rolling back transactions after you call the Update method, using code like the following:

IF dw_Employee.Update()>0 THEN
      COMMIT USING emp_transobject;
ELSE
      ROLLBACK USING emp_transobject;
END IF

You must set the parameters required to connect to your DBMS in the transaction object before you can use the transaction object to connect to the database. PowerBuilder provides a global transaction object called SQLCA, which is all you need if you are connecting to one database. You can also create additional transaction objects, as shown in the examples.

To use SetTransObject, write code that does the following tasks:

  1. Set up the transaction object by assigning values to its fields (usually in the application's Open event).

  2. Connect to the database using the SQL CONNECT statement and the transaction object (in the Open event for the application or window).

  3. Call SetTransObject to associate the transaction object with the DataWindow control or DataStore (usually in the window's Open event).

  4. Check the return value from the Update method and follow it with a SQL COMMIT or ROLLBACK statement, as appropriate.

If you change the DataWindow object associated with the DataWindow control (or DataStore) or if you disconnect and reconnect to a database, the connection between the DataWindow control (or DataStore) and the transaction object is severed. You must call SetTransObject again to reestablish the connect.

SetTransObject versus SetTrans

In most cases, use the SetTransObject method to specify the transaction object because it is efficient and gives you control over when transactions are committed.

The SetTrans method provides another way of managing the database connection. SetTrans, which sets transaction information in the internal transaction object for the DataWindow control or DataStore, manages the connection automatically. You do not explicitly connect to the database; the DataWindow connects and disconnects for each database transaction, which is less efficient but necessary in some situations.

For more information, see SetTrans.

Examples

This statement causes dw_employee to use the default transaction object SQLCA:

dw_employee.SetTransObject(SQLCA)

This statement causes dw_employee to use the programmer-defined transaction object emp_TransObj. In this example, emp_TransObj is an instance variable, but your script must allocate memory for it with the CREATE statement before you use it:

emp_TransObj = CREATE transaction
... // Assign values to the transaction object
dw_employee.SetTransObject(emp_TransObj)

This example has two parts. The first script, for the application's Open event, reads database parameters from an initialization file called MYAPP.INI and stores the values in the default transaction object (SQLCA). The Database section of MYAPP.INI has the same keywords as PowerBuilder's own PB.INI file. The parameters shown are for a SQL Server or Oracle database. The second script, for the window's Open event, establishes a connection and retrieves data from the database.

The application's Open event script populates SQLCA:

SQLCA.DBMS = ProfileString("myapp.ini", &
      "database", "DBMS", " ")
SQLCA.Database = ProfileString("myapp.ini", &
      "database", "Database", " ")
SQLCA.LogId = ProfileString("myapp.ini", &
      "database", "LogId", " ")
SQLCA.LogPass = ProfileString("myapp.ini", &
      "database", "LogPassword", " ")
SQLCA.ServerName = ProfileString("myapp.ini", &
      "database", "ServerName", " ")
SQLCA.UserId = ProfileString("myapp.ini", &
      "database", "UserId", " ")
SQLCA.DBPass = ProfileString("myapp.ini", &
      "database", "DatabasePassword", " ")
SQLCA.lock = ProfileString("myapp.ini", &
      "database", "lock", " ")

The Open event script for the window that contains the DataWindow control connects to the database, assigns the transaction object to the DataWindow, and retrieves data:

long RowsRetrieved
string LastName
 
// Connect to the database.
CONNECT USING SQLCA;
 
// Test whether the connect succeeded.
IF SQLCA.SQLCode <> 0 THEN
      MessageBox("Connect Failed", &
         "Cannot connect to database " &
         + SQLCA.SQLErrText)
      RETURN
END IF
 
// Set the transaction object to SQLCA.
dw_employee.SetTransObject(SQLCA)
 
// Retrieve the rows.
LastName = ...
RowsRetrieved = dw_employee.Retrieve(LastName)
// Test whether the retrieve succeeded.
IF RowsRetrieved < 0 THEN
      MessageBox("Retrieve Failed", &
         "Cannot retrieve data from the database.")
END IF

See also

GetTrans

SetTrans