HP 3000 Manuals HP 3000 Manuals
Example Using Single-transaction Mode with Timeouts
Suppose you want to access three DBEnvironments (PartsDBE, SalesDBE, and
AccountingDBE) to simultaneously display related information from each.
Depending on your coding environment, you could display data from each
DBEnvironment in a separate window or in a specific location in the same
window. You choose single-transaction mode because just one transaction
at a time must be active.
Since this is a display only application, you decide that all
transactions are to be established with the read committed isolation
level. You also decide on appropriate timeout values for each
transaction and how you want to respond to each possible timeout
condition. Your goal is to prevent long waits due to locks held or due
to the maximum transaction limit being reached.
The following pseudocode illustrates this scenario:
Define and initialize host variables for DBEnvironment and connection names.
:
Put single-transaction mode in effect. Note that although single-transaction mode
is the default, it is good coding practice to specify the transaction mode.
SET MULTITRANSACTION OFF
DECLARE SalesCursor
CURSOR FOR
SELECT PartNumber, InvoiceNumber, SalesDate, SalesAmount, CustomerNumber
FROM Owner.Sales
WHERE PartNumber = :PartNumber
AND SalesDate BETWEEN '1991-01-01' AND '1991-06-30'
Connect to three DBEnvironments specifying a connection name for each
connection. Set a timeout value following each current connection.
CONNECT TO :PartsDBE AS :Parts
Note that the following statement sets the Parts connection timeout to the
maximum specified in the DBECon file. If the maximum is set to NONE (infinity),
no timeout can occur. Here we'll assume that it is set to 300 seconds.
SET USER TIMEOUT MAXIMUM
CONNECT TO :SalesDBE AS :Sales
SET USER TIMEOUT 30 SECONDS
CONNECT TO :AccountingDBE AS :Accounting
SET USER TIMEOUT 30 SECONDS
Set the current connection to Parts.
SET CONNECTION :Parts
Begin a transaction that accesses PartsDBE. This transaction displays parts data
for a range of part numbers. Here, for clarity, the range is hard coded.
You could, however, use host variables to prompt the user for the lower and upper
limits. Another alternative would be to use dynamic processing, possibly with
dynamic parameters.
BEGIN WORK RC
BULK SELECT PartNumber, PartName, SalesPrice
INTO :PartsArray, :StartIndex, :NumberOfRows
FROM PurchDB.Parts
WHERE PartNumber BETWEEN 20000 AND 21000
Test the sqlcode field of the sqlca. If it equals -2825, a timeout has occurred,
and the transaction was rolled back. Display a message and gracefully exit the
application.
Otherwise, end the transaction.
COMMIT WORK
Set the current connection to Sales.
SET CONNECTION :Sales
Prompt the user for a part number in the displayed range and accept the response
into a host variable named PartNumber.
OPEN SalesCursor
Begin a second transaction that accesses SalesDBE. This transaction displays sales
data for the first six months of 1991 based on the PartNumber entered by the user.
Here, for clarity, the range is hard coded. You could, however, use host variables
to prompt the user for the lower and upper limits of a date range. Another alternative
would be to use dynamic processing, possibly with dynamic parameters.
BEGIN WORK RC
BULK FETCH SalesCursor
INTO :SalesArray, :StartIndex2, :NumberOfRows2
Test the sqlcode field of the sqlca. If it equals -2825, a timeout has occurred,
and the transaction was rolled back. Display a message and prompt the user to
try again or exit the application.
If they choose to try again, re-execute the transaction.
If they choose to exit the application, do so gracefully.
If no timeout error (or other error) occured, continue.
COMMIT WORK
Set the current connection to Accounting.
SET CONNECTION :Accounting
Prompt the user for an invoice number, and accept the response into a host variable
named InvoiceNumber.
Begin a third transaction accessing AccountingDBE. This transaction displays
accounting data for a part number and an invoice number based on the user entered
part number and invoice number. Again you could use dynamic processing, possibly
with dynamic parameters.
BEGIN WORK RC
BULK SELECT InvoiceNumber, PartNumber, InvoiceDate, DueDate, DatePaid
INTO :AccountingArray, :StartIndex3, :NumberOfRows3
FROM Owner.Accounting
WHERE InvoiceNumber = :InvoiceNumber
AND PartNumber = :PartNumber
Test the sqlcode field of the sqlca. If it equals -2825, a timeout has occurred,
and the transaction was rolled back. Display a message and prompt the user to
try again or exit the application.
If they choose to try again, re-execute the transaction.
If they choose to exit the application, do so gracefully.
If no timeout error (or other error) occurred, continue.
COMMIT WORK
At this point you could loop back to ask the user for either another invoice number
for the same part number, another part number from the range already selected in
the first transaction, or a new range of part numbers. Or you could issue the
DISCONNECT ALL statement and exit the application.