ODBC Performance and locking
After a connection is established, SQL statements can cause
locks to be placed on database entities. The more locks there are
in place at a given moment in time, the more likely it is that the
locks will hold up another transaction.
Rules
No set of rules for designing a database application is totally
comprehensive. However, when you design a PowerBuilder application,
you should do the following:
-
Long-running connections
Determine whether you can afford to have long-running connections.
If not, your application should connect to the database only when
absolutely necessary. After all the work for that connection is
complete, the transaction should be disconnected.If long-running connections are acceptable, then COMMITs should
be issued as often as possible to guarantee that all changes do
in fact occur. More importantly, COMMITs should be issued to release
any locks that may have been placed on database entities as a result
of the statements executed using the connection. -
SetTrans or SetTransObject function
Determine whether you want to use default DataWindow transaction
processing (the SetTrans function) or control the transaction in
a script (the SetTransObject function).If you cannot afford to have long-running connections and
therefore have many short-lived transactions, use the default DataWindow
transaction processing. If you want to keep connections open and
issue periodic COMMITs, use the SetTransObject function and control
the transaction yourself.
Switching during a connection
To switch between transaction processing and AutoCommit during
a connection, change the setting of AutoCommit in the transaction
object.
Isolation feature
ODBC uses the isolation feature to support assorted database
lock options. In PowerBuilder, you can use the Lock property of
the transaction object to set the isolation level when you connect
to the database.
The following example shows how to set the Lock property to
RU (Read uncommitted):
|
1 |
// Set the lock property to read uncommitted <br>// in the default transaction object SQLCA. <br>SQLCA.Lock = "RU" |
PowerBuilder uses the ODBC API call SQ2.SetConnectOption (SetIsolationLevel)
to set the isolation level. The lock value is passed to the function
as a 32-bit mask.
Example 1
This script uses embedded SQL to connect to a database and
attempts to insert a row in the ORDER_HEADER table and
a row in the ORDER_ITEM table. The script then executes
a COMMIT or ROLLBACK depending on the success of all statements
in the script.
|
1 |
// Set the SQLCA connection properties.<br>SQLCA.DBMS = "ODBC" <br>SQLCA.DBParm = "connectstring = 'DSN = orders'" |
|
1 |
// Connect to the database. <br>CONNECT USING SQLCA; |
|
1 |
// Insert a row into the ORDER_HEADER table.<br>INSERT INTO ORDER_HEADER (ORDER_ID,CUSTOMER_ID)<br> VALUES (7891, 129); |
|
1 |
// Test return code for ORDER_HEADER insertion.<br>// A ROLLBACK is required only if the first row <br>// was inserted successfully.<br>if SQLCA.sqlcode = 0 then |
|
1 |
// Since the ORDER_HEADER is inserted,<br>// try to insert ORDER_ITEM.<br> INSERT INTO ORDER_ITEM <br> (ORDER_ID, ITEM_NBR, PART_NBR, QTY)<br> VALUES (7891, 1, '991PLS', 456); <br>// Test return code for ORDER_ITEM insertion.<br> if SQLCA.sqlcode = -1 then |
|
1 |
// Disconnect from the database.<br>DISCONNECT USING SQLCA; |
Error checking
Although you should test the SQLCode after every SQL statement,
these examples show statements to test the SQLCode only to illustrate
a specific point.
Example 2
This example uses scripts for the Open and Close events for
a window and the Clicked event for a CommandButton to illustrate
how you can manage transactions for a DataWindow control. Assume
a window contains a DataWindow control dw_1 and a CommandButton
Cb_Update. Also assume the user enters data in dw_1
and then clicks the Cb_Update button to update the database
with the data.
The window OPEN event script:
|
1 |
// Set the transaction object properties<br>// and connect to the database. <br>// Set the SQLCA connection properties. <br>SQLCA.DBMS = "ODBC"<br>SQLCA.DBParm = "connectstring = 'DSN = orders'" |
|
1 |
// Connect to the database.<br>CONNECT USING SQLCA; |
|
1 |
// Tell the DataWindow which transaction object<br>// to use. <br>dw_1.SetTransObject(sqlca) |
The CommandButton CLICKED event script:
|
1 |
// Declare ReturnValue an integer.<br>integer ReturnValue <br>ReturnValue = dw_1.Update( ) |
|
1 |
// Test to see if updates were successful.<br>if ReturnValue = -1 then |
|
1 |
// Updates were not successful. Since we used<br>// SetTransObject, roll back any changes made<br>// to the database. <br> ROLLBACK USING SQLCA; <br>else |
|
1 |
// Updates were successful. Since we used<br>// SetTransObject, commit any changes made <br>// to the database. <br> COMMIT USING SQLCA;<br>end if |
The window CLOSE event script:
|
1 |
// Disconnect from the database.<br>DISCONNECT USING SQLCA; |