Copyright: Paradox for Windows Upsizing Help, v.5.xx
Citation: Copyright ® 1985, 1994 Borland International.


Upsizing Your ObjectPAL Application

This stand-alone Help system explores "upsizing" issues for Paradox for Windows application developers.  It is intended for ObjectPAL developers who have already installed one or more Borland SQL Link drivers and want to adapt existing applications, or create new ones, to access SQL data.

Choose a topic below for more information on upsizing your ObjectPAL application:

Working with SQL Link drivers	This section describes how the Borland SQL Link drivers provide access to SQL data through Paradox for Windows and ObjectPAL.
SQL-enabled ObjectPAL	This section contains references ObjectPAL language extensions for working with SQL data, new language elements, and ObjectPAL methods that do not support SQL.
Creating ObjectPAL applications for SQL data	This section discusses strategies for ObjectPAL developers who want to migrate to the SQL environment.


Working With SQL Link Drivers

See Also

Choose a topic below for information on working with SQL Link drivers:

Essentials

Options for querying SQL data

Table requirements

Unique row identification

Defined row ordering

SQL Link locking and transaction support

Default transaction behavior

Table-locking behavior

Record-locking behavior

Client-controlled transaction behavior

Client-controlled transaction behavior


See Also

SQL-enabled ObjectPAL

Creating ObjectPAL applications for SQL data


Working With SQL Link: Essentials
Essentials

See Also

Borland® SQL Links for Windows is a collection of drivers that let you work with SQL data through supported Borland applications. The SQL Link driver enables the connection to the SQL server, translates queries into the appropriate SQL dialect, and passes them to the SQL database. When processing is complete, the SQL database returns the answer to the client in a format that the desktop application can display.

SQL server	Client/Server network

1.	IDAPI client queries an SQL database.

2.	SQL Link driver enables client/server connection, translates query into appropriate SQL dialect, and sends the query to the SQL server.

3.	Server performs error and syntax checking, processes query, and displays the answer to the client.

4.	SQL Link driver translates answer into appropriate client syntax; client formats and displays answer to the user.

Note:	Borland database applications also support the use of SQL statements against local (Paradox or dBASE) data. For information on how to use Local SQL with Paradox for Windows, see the Paradox for Windows online Help.

Using Paradox with an SQL Link driver actually extends what you can do with your application, lending it some of the advantages inherent in using SQL databases:

the ability to query the SQL server directly

support for SQL-style transactions

improved record caching

data locking behavior

Accessing an SQL server through an SQL Link driver and Paradox also lends traditional SQL database users some of the advantages inherent in workstation databases, enabling you to

move in both directions through a result set or answer table

order data using an available index

work dynamically with the SQL data itself through live access to data sources


See Also

Supported applications

Supported databases


Options for querying SQL data

SQL query mode (SQLQRYMODE)

Under most circumstances, queries to SQL databases are processed on the SQL server. However, in cases where the SQL server cannot (or should not) process a query, Paradox processes the query locally.

Types of SQL database queries that are processed locally include

Heterogeneous queries

Queries that cannot be expressed as a single SQL statement

Queries that the SQL server does not support

If you want to make sure that all queries originating from Paradox are processed according to the rules of the SQL server, you can configure the SQL Link driver to block workstation processing of queries. For example, you may want to force remote processing if you are using a non-English language driver that does not properly mimic the SQL servers conventions for sorting and uppercasing.

To do so, use the IDAPI Configuration Utility to modify the SQL database alias, setting SQLQRYMODE to SERVER.

Pass-through SQL mode (SQLPASSTHRU MODE)

Through the IDAPI Configuration Utility, you can also specify whether or not SQL statements passed directly to the server via executeSQL() should be automatically committed.  The parameter that governs this is SQLPASSTHRU MODE.

The possible SQLPASSTHRU MODE options are:

NOT SHARED  -- Pass-through SQL and non-pass-through SQL do NOT share the same connection.

SHARED AUTOCOMMIT -- Pass-through SQL and non-pass-through SQL will share the same connection, and (as long as you are not in an explicit client transaction or batch mode) pass-through SQL will be automatically committed.

SHARED NOAUTOCOMMIT -- Pass-through SQL and non-pass-through SQL share the same connection, but pass-through statements will not be automatically committed.  You should use the database type methods beginTransaction(), commitTransaction(), and rollbackTransaction() to explicitly control transaction processing on the SQL server.  Do not use SQL statements to control transaction processing on the server.


Table Requirements

To best take advantage of Borland SQL Link driver capabilities, tables should possess both a unique row identification method and a defined row ordering.

Choose a topic below for more information:

Unique row identification

Defined row ordering


Unique row identification

Unique row identification is generally recommended for updating data.  If the target table contains some non-unique records, Paradox cannot absolutely determine which record is desired and as a result the update may fail.  Unique row identification improves proformance of QBE DELETE and CHANGETO queries that are performed locally.

Note:	Insertions, modifications, and deletions do not require uniquely identifiable rows.  These kinds of operations can be performed on "snapshots," or views of SQL data unless:

1. The target database is configured to be non-logging (Informix only)

2. The table has duplicate rows (see above)

Borland SQL Links requires some kind of unique row identification to support full blob access for SQL servers that do not support blob handles for random reads and writes.  Most SQL servers limit a single sequential blob read to less than the maximum size of a blob. In those cases an entire blob may not be available.  QBE, forms, and ObjectPAL do not support inserting and altering sequentially-accessed blobs.  To see if your SQL server tables support blob handles, or identify the maximum size of a single blob read, see your SQL Links Connecting to... manual.

You can guarantee unique row identification through either a unique index or an implicit row identification method.

Note:	 Servers that support implicit row identification do not always support it for all server objects.  For example, even if your server supports an implicit unique row identification method for tables, it may not support one for SQL server views.  Your Connecting to... manual notes whether your server supports an implicit unique row identification method.


Defined row ordering

SQL Links requires a defined row ordering to access a small window of data, centered around the current row location.  The window into the server data moves as the client's current row location moves, and can be refreshed from the server through the Paradox forceRefresh command.

If no defined row ordering is available, when data is inserted SQL Link cannot tell where the SQL server places the inserted row. Therefore, the row may or may not appear in the set of data as it is read. This behavior can vary from SQL server to SQL server, and even from table to table.

A defined row ordering requires either an index or some other method that specifies an ordering of those rows.


SQL Link Locking and Transaction Support

The SQL Link driver enables support for table locking, record locking, and explicit SQL transaction management.  For information on how your SQL server handles locking and transactions, see your SQL server documentation.

Choose a topic below for more information:

Default transaction behavior

Table-locking

Record-locking

Client-controlled transaction behavior


Default Transaction Behavior

SQL operations always take place within the context of a transaction. When no explicit transaction occurs, SQL Link manages the SQL server transactions transparently for the client. Any successful modification of SQL server data is immediately committed to ensure its permanence in the database.  For example, a single QBE query, a single ObjectPAL table append, a single form editing operation, and a single ObjectPAL record post or unlock are each individually committed by default.

Even if multiple operations take place (for example, in a heterogeneous QBE query), they appear to the client as a single operation.


Table-locking Behavior

The SQL Link driver provides the same table locking support as the target SQL server.  For information on your SQL server's locking support, see your server documentation.

In SQL servers that support table locks, locks can only be maintained within the context of a transaction.  A lock is not acquired until after the transaction starts, and can only be released when the transaction ends.  When SQL Link acquires a table lock, it automatically starts a transaction if necessary.  When SQL Link is ready to release a table lock it first commits the transaction, automatically releasing all other locks at the same time.  It then automatically re-acquires any remaining locks.  If a request to release a table lock will cause an implicit COMMIT, the request is held until the transaction is ended.

Note:	During the period between the time a lock is released and then re-acquired, it is possible for another user to change your data.  For this reason, it is recommended to either release all table locks together when the last lock is no longer needed, or use explicit SQL transactions instead of locking entire tables.


Record-locking Behavior

SQL servers lock data as required, depending on the type and granularity of lock supported.  SQL Link offers an additional locking strategy called optimistic locking to provide a generic way to ensuring data integrity.

Optimistic locking allows a user to modify a local copy of a record, instead of locking a record for the entire time it is being modified.  When the modifications are finished, SQL Link checks the current "live" data to make sure no other changes have been made in the interim, then modifies the "live" data based on the changes made to the copy.  If the "live" data was changed by someone else, an optimistic lock failure occurs.  The user is notified that someone else has changed the data first.  They can then inspect the new data and decide whether or not to make changes at that time.

The operation is said to be optimistic because it assumes that no other user will change the record.

Optimistic locking enables users to modify data without the performance and concurrency penalty that comes with locking the data.


Client-controlled Transaction Behavior

SQL Link provides Paradox functions to explicitly start, commit, and roll back a transaction.  This gives the programmer the same protection afforded to pass-through SQL users.  When a transaction is explicitly started, a COMMIT operation is never performed automatically, so the client has the full protection of the SQL server transaction mechanism. That is, modifications are performed, but not automatically committed and table lock releases do not cause a commit within an explicit client transaction.

How transactions are started

When starting a transaction, SQL Link uses the same default isolation level in use at the SQL server.  For information on your server's default isolation level, see your server documentation.

An SQL Link transaction is always started and ended through a database alias.  Once a transaction is started, all modification and table- and record-locking operations executed in that database are performed in the context of that transaction.

How SQL Link operations change under an explicit transaction

When operating within an explicit client transaction, both table append operations and locally-executed queries execute within the context of that transaction.  Thus, they can be rolled back.  Append operations executed outside an explicit transaction may only partially complete if an error occurs.

Although transactions can be started in multiple database aliases simultaneously, SQL Link manages the transactions independently, so the user must be careful to minimize the window of vulnerability after the first commit of a multiple database transaction.

Note:	Data Definition Language (DDL) operations can be done within an explicit transaction if the SQL server allows it. Most SQL servers do not allow this, or would implicitly commit the explicit transaction. For such servers, SQL Link disallows DDL operations when an explicit client transaction is in effect.


SQL-enabled ObjectPAL

See Also

SQL Link provides new ObjectPAL methods that support SQL and transaction processing. It also provides additional remote access capabilities for existing ObjectPAL methods. If you are familiar with SQL, you can also include SQL statements in ObjectPAL code using an SQL...ENDSQL block.

This section provides information on ObjectPAL methods compatible for use in an SQL environment.  For detailed information on ObjectPAL methods, see the ObjectPAL online Help.

Choose a topic below for more information:

ObjectPAL SQL type methods

SQL-enabled ObjectPAL methods for other object types

Standard ObjectPAL methods that support SQL Links

Standard ObjectPAL methods that do not support SQL Links


See Also

Working with SQL Link drivers

Creating ObjectPAL applications for SQL data


ObjectPAL methods for the SQL object type
ObjectPAL SQL Type Methods

SQL Link adds a new SQL object type to ObjectPAL, and four new SQL type methods:

Method	Description/Syntax

executeSQL()	Executes an SQL statement.  A database must be specified.

Syntax:	1. executeSQL(const db Database) Logical

2. executeSQL(const db Database, const ansTbl String) Logical

3. executeSQL(const db Database, const ansTbl Table) Logical

4. executeSQL(const db Database, var ansTbl TCursor) Logical

getQueryRestartOptions()	Returns an integer value representing the user's query restart option setting.

Syntax:	getQueryRestartOptions ( ) SmallInt

isAssigned	Reports whether an SQL variable has an assigned value.

Syntax:	isAssigned ( ) Logical

readFromFile()	Reads in a file to an SQL variable to later be executed by executeSQL() or written to a file by writeSQL().

Syntax:	1. executeSQLFile(const db Database, const fileName String) Logical

2. executeSQLFile(const db Database, const fileName String, const ansTbl String) Logical

3. executeSQLFile(const db Database, const fileName String, const ansTbl Table) Logical

4. executeSQLFile(const db Database, const fileName String, var ansTbl TCursor) Logical

readFromString()	Reads in a string to an SQL variable to later be executed by executeSQL() or written to a file by writeSQL().

Syntax:	readFromString ( const sqlString SQL ) Logical

setQueryRestartOptions ()	Specifies what to do with the underlying tables while running a query.

Syntax:	setQueryRestartOptions ( const qryRestartType SmallInt ) Logical

SQL ... ENDSQL	Used to associate an SQL variable with a pass-through SQL statement within an ObjectPAL program.

Syntax:	SQLvar=SQL
SQL statements
END SQL

wantInMemoryTCursor ()	Specifies whether the results of an executed SQL statement that includes a TCursor should be an in-memory copy of the results or a dynaset.  If TRUE, the results should be an in-memory copy of the results; if FALSE, it should be a dynaset.

Syntax:	wantInMemoryTCursor ( [ const yesNo Logical ]

writeSQL()	Writes the pass-through SQL statement (represented by the SQL variable) to the specified file.

Syntax	writeSQL(const fileName String) Logical


SQL-enabled methods for other object types
SQL-enabled ObjectPAL Methods for Other Object Types

Methods have also been added to existing ObjectPAL data types to support SQL:

Method	Description/Object Type/Syntax

beginTransaction()	Starts a transaction on a server when transactions are supported. Otherwise, the function returns an error. Only one transaction for each database (or alias) is allowed.

beginTransaction() uses the value for isoLevel or defaults to the highest (most isolated) isolation level on a server.  While the transaction is active, statements that operate on any table associated with the specified database are included as part of the transaction (except for pass-through SQL statements where SQLPASSTHRU MODE is set to NOT SHARED).

Object Type:	Database

Syntax:	beginTransaction([const isoLevel String]) Logical

commitTransaction()	Commits all changes within a transaction on a server when transactions are supported. Otherwise, the function returns an error.

Object Type:	Database

Syntax:	commitTransaction() Logical

enumAliasLoginInfo()	Writes the alias name, properties, and property values to a specified table.

Object Type:	Session

Syntax:	enumAliasLoginInfo(const tableName String, const aliasName String) Logical

getAliasProperty()	Returns the requested alias property.

Object Type:	Session

Syntax:	getAliasProperty(const aliasName String, const property String) String

rollbackTransaction()	Rolls back all changes within a transaction on a server when transactions are supported. Otherwise, the function returns an error.

Object Type:	Database

Syntax:	rollbackTransaction() Logical

setAliasPassword()	Sets the alias password.

Object Type:	Session

Syntax:	setAliasPassword(const aliasName String, const password String) Logical

setAliasProperty()	Sets the specified alias property.

Object Type:	Session

Syntax:	setAliasProperty(const aliasName String, const property String) String

isOnSQLServer()	Returns TRUE if the table is on an SQL server.

Object Type:	TCursor

Syntax:	isOnSQLServer() Logical

isOpenOnUniqueIndex()	Returns True if the TCursor is opened on a unique index. Frequently used for SQL server verification because many functions require TCursor opened on a unique index.

Object Type:	TCursor

Syntax:	isOpenOnUniqueIndex() Logical


Standard methods that support SQL Links
Standard ObjectPAL Methods That Support SQL Links

Most ObjectPAL methods and procedures accept aliases to SQL databases, but otherwise perform as they do in Paradox.  The following table lists the standard ObjectPAL methods that support SQL Link.

Method	ObjectType	Method	ObjectType

open	Database	cSamStd	TCursor

isTable	Database	cSamVar	TCursor

executeQBE	Query	cStd	TCursor

cSum	TCursor	cVar	TCursor

currRecord	TCursor	deleteRecord	TCursor

isAssigned	Query	didFlyAway	TCursor

query (Keyword)	Query	dropIndex	TCursor

writeQBE	Query	edit	TCursor

addAlias	Session	empty	TCursor

enumAliasNames	Session	end	TCursor

enumDatabaseTables	Session	endEdit	TCursor

enumDriverCapabilities	Session	enumFieldNames	TCursor

enumDriverInfo	Session	enumFieldNamesinIndex	TCursor

enumDriverNames	Session	enumFieldStruct	TCursor

enumDriverTopics	Session	enumIndexStruct	TCursor

enumEngineInfo	Session	enumLocks	TCursor

enumOpenDatabases	Session	enumTableProperties	TCursor

lock	Session	eot	TCursor

removeAlias	Session	fieldName	TCursor

setRetryPeriod	Session	fieldNo	TCursor

unlock	Session	fieldSize	TCursor

add	Table	fieldType	TCursor

attach	Table	fieldValue	TCursor

cAverage	Table	getLanguageDriver	TCursor

cCount	Table	getLanguageDriverDesc	TCursor

cMax	Table	home	TCursor

cMin	Table	initRecord	TCursor

cNpv	Table	insertAfterRecord	TCursor

cSamStd	Table	insertBeforeRecord	TCursor

cSamVar	Table	insertRecord	TCursor

cStd	Table	isAssigned	TCursor

cSum	Table	isEdit	TCursor

cVar	Table	isEmpty	TCursor

empty	Table	isValid	TCursor

enumFieldNames	Table	locate	TCursor

enumFieldNamesinIndex	Table	locateNext	TCursor

enumFieldStruct	Table	locateNextPattern	TCursor

enumIndexStruct	Table	copyToArray	TCursor

fieldName	Table	locatePattern	TCursor

fieldNo	Table	locatePrior	TCursor

fieldType	Table	locatePriorPattern	TCursor

isAssigned	Table	lock	TCursor

isEmpty	Table	lockRecord	TCursor

isTable	Table	lockStatus	TCursor

lock	Table	moveToRecNo	TCursor

nFields	Table	moveToRecord	TCursor

nKeyFields	Table	nextRecord	TCursor

nRecords	Table	nFields	TCursor

rename	Table	nKeyFields	TCursor

setFilter	Table	nRecords	TCursor

setIndex	Table	open	TCursor

subtract	Table	postRecord	TCursor

type	Table	priorRecord	TCursor

unattach	Table	qLocate	TCurso

unlock	TCursor	recNo	TCursor

add	TCursor	recordStatus	TCursor

atFirst	TCursor	seqNo	TCursor

atLast	TCursor	setFieldValue	TCursor

attachToKeyViol	TCursor	setFlyAwayControl	TCursor

bot	TCursor	skip	TCursor

cancelEdit	TCursor	subtract	TCursor

cAverage	TCursor	switchIndex	TCursor

cCount	TCursor	tableName	TCursor

close	TCursor	type	TCursor

cMax	TCursor	unlock	TCursor

cMin	TCursor	unlockRecord	TCursor

cNpv	TCursor	updateRecord	TCursor

copy	TCursor

copyFromArray	TCursor

copyRecord	TCursor


Standard Methods That Do Not Support SQL Links

The following table lists ObjectPAL methods and procedures that do not support SQL Links:

Method	ObjectType	Method	ObjectType

compact	Table	enumRefIntStruct	TCursor

enumSecStruct	Table	enumSecStruct	TCursor

enumRefIntStruct	Table	familyRights	TCursor

familyRights	Table	fieldRights	TCursor

isEncrypted	Table	isEncrypted	TCursor

isShared	Table	isRecordDeleted	TCursor

protect	Table	isShowDeletedOn	TCursor

reIndex	Table	isShared	TCursor

reIndexAll	Table	protect	TCursor

setExclusive	Table	reIndex	TCursor

setReadOnly	Table	reIndexAll	TCursor

showDeleted	Table	showDeleted	TCursor

tableRights	Table	tableRights	TCursor

unprotect	Table	undeleteRecord	TCursor

usesIndexes	Table	unprotect	TCursor

compact	TCursor


Creating ObjectPAL Applications for SQL Data

See Also

Choose a topic below for more information:

Moving a local application to an SQL environment

Moving local tables

Re-binding forms

Changing table references

Changing index references

Using transactions in ObjectPAL applications

The SQL locking strategy

Applying transaction management principles

"Snapshot" transactions

Generating the key at the end of the transaction

Optimizing your SQL application

Customizing Paradox behavior

TCursors versus queries

Lookup tables

Stored procedures and triggers


See Also

Working with SQL Link drivers

SQL-enabled ObjectPAL


Moving a Local Application to an SQL Environment

This section discusses tasks that must be completed to port a local ObjectPAL application to an SQL environment. The procedures in this section are designed to get an existing ObjectPAL application up and running quickly against an SQL server.

For a discussion of ways to optimize code that has been ported to an SQL environment, see Optimizing your SQL application.

Choose a topic below for more information:

Moving local tables

Re-binding forms

Changing table references

Changing index references


Moving Local Tables

Before you begin to move your application to the server, upload all the tables you want to reside on the server in either of the following ways:

Use SQL Tools to create the table, then add or copy the desired records from the local table to the server table.

Use the SQL Editor to create the table on the server. When the table is ready, use a QBE INSERT query to copy the desired records from the local table to the server table.

Write an ObjectPAL script; for example:

const
   numTbls = 3
endconst
var
   tblsToCopy  Array[numTbls] String
   tb          Table
   i           SmallInt
endvar

tblsToCopy[1] = "CUTSOMER"
tblsToCopy[2] = "ORDERS"
tblsToCopy[3] = "LINEITEM"

for i from 1 to numTbls
   tb.attach(tblsToCopy[i])
   tb.copy(":IB_data:"+tblsToCopy[i])
endfor

After all local tables are uploaded to the SQL server, index the tables to duplicate the links between tables that exist in your local application. You can use either SQL statements or the ObjectPAL INDEX statement to create the index. (For the syntax of the INDEX keyword,  see the ObjectPAL Reference.)

To ease migration, preserve the table names (without the file extension) and the index names whenever possible. If you have two or more local tables that use the same index name and your server only allows you to use an index name once in a database, you may need to alter the index name. If so, you also need to change the existing references to the changed index.


Re-binding Forms

Re-binding an existing form to use SQL server tables requires that all local tables be uploaded to the SQL server, that the SQL database have a valid alias, and all necessary indexes have been created.

If you originally created forms by specifying an alias explicitly, you may only need to change the alias so that it maps to the SQL server. The forms can then be used in the new application. However, if you want to use forms that were created using tables in your working directory or some hard-coded DOS path name, you must re-map the form to the SQL server alias.

You can do this in either of the following ways:

Move the tables in the form to a different location on the workstation hard disk, then load the form in Design mode. This enables you to manually re-associate the forms supporting tables. Select the appropriate server tables; Paradox re-binds the data model to the selected tables.

For detail tables in a data model, if Paradox cannot find the index named in the link, it attempts to find and use an index which corresponds to the link information stored in the data model. If the table is a master and the index is not found, Paradox will also attempt to open the correct index; however, due to the changes in the index, you will lose any Order/Range information stored with your form.

Enter the data model of the form. Re-construct the data model directly so that it uses the desired alias:

1.	Use the Alias Manager to create an alias which points to the server where the tables reside.

2.	Open the form in design mode.

3.	Enter the data model diagram.

4.	Unlink and remove the tables in the data model.

5.	Recreate the data model. Be sure to select an alias from the Path drop-down list. After you select an alias, select the table. Paradox references the table by the alias and stores it in the data model with that alias.

6.	Re-link the tables in the data model.

7.	Save and close the form.


Changing Table References

As with forms that were originally created for local use, any tables used in an SQL environment must point specifically to tables that reside on the aliased server.

Because Paradox uses aliases to make connections with SQL databases, applications which were originally written against absolute DOS directories must be modified to run against an SQL server. For example, you may need to change all occurrences of:

Var
   tcCustomer TCursor
endvar
   tcCustomer.open( "C:\\data\\Customer.db" )

to either:

Var
   tcCustomer TCursor
endvar
   tcCustomer.open( ":someAlias:Customer" )

or:

Var
   tcCustomer TCursor
   SomeDB Database
endvar
SomeDB.Open(":SomeAlias:")
   tcCustomer.open( "Cutsomer", SomeDB)

where :SomeAlias: is an alias which points to C:\DATA. If your local application already uses aliases in this manner, moving the application to the server is transparent. You may redefine :SomeAlias: to point to your server database, and put CUSTOMER on the server. The TCursor opens the SQL table instead of the local table, and almost all TCursor methods called work properly against the server.

The use of aliases makes it easier to port an application to a different server, since only the alias definition (and not the code itself) must change.


Changing Index References

SQL servers have different naming conventions for index names. If you had to change index names in order to prevent duplicate index names from existing on the server, look for occurrences of the SwitchIndex() method. Change those references so that they point to the new index name at the SQL server.

Note:	This is especially true of Paradox primary keys that are uploaded to an SQL server. In Paradox the primary key (the default index with which a Paradox table is opened) is not given a name. Since SQL requires names for all indexes, it may be necessary for you to change occurrences of SwitchIndex() to SwitchIndex(<SQLKeyIndexName>).

After all forms have been remapped and all table and index references changed to point to the SQL server, check the application for the presence of ObjectPAL methods that do not support SQL Links.  Remove these methods from the application, or change them to SQL equivalents.


Using Transactions in ObjectPAL Applications

The decision to use transactions in an ObjectPAL application is not clear-cut. It depends upon the update requirements of your application, the number of users expected to access data concurrently, and even the desired level of performance in your application, since interactive editing against a server will be somewhat slower than against local tables.

Choose a topic below for more information:

The SQL locking strategy

Applying transaction management principles

"Snapshot" transactions

Generating the key at the end of the transaction


The SQL Locking Strategy

SQL servers do not use the same locking protocols as non-SQL desktop applications.  SQL Links optimistic locking scheme (see record locking) presents unique challenges to the application developer. Although your application may now be up and running on an SQL server, you may need to resolve issues with the way your application code manages locks in order to have the best possible SQL application.

For example, your application may be an order entry database written to run under Paradox. The application:

Enables the user to define or locate a customer in the database (and locks the master record).

Generates a unique order number.

Enables the user to enter detail line items for this order and posts each line item as it is entered. Since the master record is locked, the line items are also protected.

Unlocks the order record.

This approach works because the master record is locked whenever anyone attempts to modify it. In SQL, however, no such guarantee exists. Another user can edit the first users order record while the first user is still working with it. If the other user deleted the record before the first user finished, the first users entry would end in error.

SQL offers a solution to this problem by means of transactions. SQL-enabled ObjectPAL uses the transaction capabilities of SQL Link to begin, commit, and roll back transactions. This guarantees a consistent set of changes. The SQL server also automatically places locks whenever a user accesses and updates data on the server. These automatic locks prevent other users from modifying the changes you have posted before the transaction is committed or rolled back.


Applying Transaction Management Principles

The following example shows how transactions can be used in ObjectPAL applications.

In the example situation, you want to modify the previously-described order entry application so that a unique order number is created whenever the user requests that a new order be created. You do not have a stored procedure or trigger on the server to automatically generate the unique order number, and need to generate it from within your ObjectPAL application.

One technique, used often against Paradox tables, is to have a table with one field that contains the next available number for use as an order number. To automatically generate a unique order under, your program needs to read the number, increment the number, and prevent any other user from taking the same order number.

In non-SQL-enabled ObjectPAL, explicit record locking accomplishes this goal. In an SQL environment, however, the solution is to place the unique number generation inside a transaction.

The following code would go in the Action method or a custom Actionof the order record object on the form:

var
tcNumber TCursor
i Smallint
endvar
; db is a global DataBase variable which
; is open for the SQL server.  For example,
; the form's Open code might execute
; db.open (":IB_Data:" )
if eventInfo.id() = dataInsertRecord Then

; Allow only one transaction for each order. db is a Database var pointing to
; your server database.

if db.transactionActive() then
msgStop( "Order in progress", "Please finish the current order	first")
disableDefault	; refuse the insert
return
endif

Orders.edit()	; Transaction  - generate a unique number.

db.beginTransaction()
tcNumber.open( ":IB_Data:OrderNo" )	; Numbers table on Interbase
tcNumber.edit()
i = 0
svc=False
i=0
while i < 10
NewOrder = tc.(1)
tc.(1) = NewOrder + 1
if tc.UnlockRecord() then
db.commitTransaction
svc=True
else
i=i+1
endif
end while
if svc 	then
db.RollBackTransaction()
endif
tc.Close()
Endif

This code ensures that if two people read the value and one updates it, the second user receives an error when attempting to update the record. The programmer can then trap for a concurrency problem to see if someone else has modified the data while the first user was at work.

If you are not using a transaction to protect the record that has been locked and someone else deletes the record, you might lose the lock. In this case, the next time Paradox attempts to paint the data on your display you receive an Action method ID: DataLostLock. Paradox then attempts to drop your lock and sends you an Action method with the ID DataCancelRecord.

Note:	One restriction to locking and unlocking records in transactions is that a lock/unlock sequence for any given record must either occur entirely inside or outside a transaction block. This means a record may not be locked outside of a transaction and then unlocked within the transaction. The record also may not be locked inside a transaction and unlocked outside the transaction. Either of the previous situations return a Transaction Mismatch error. If a record is locked and then changed in the course of a transaction, unlocking the record does not post the changes to the server table. If the TCursor or database variable goes out of scope, the changes are lost.


"Snapshot" Transactions

SQL Link supports only one open ObjectPAL or SQL transaction per database. This means that once a user begins a transaction against a database, everything the user does interactively will occur inside that transaction until the transaction is either committed or rolled back. While this provides a good amount of concurrency protection on a multi-table form, it has the disadvantage of tying up the records being accessed until the transaction is completed. In an order entry application where a user might take an indefinite period of time to complete an order, it may be inadvisable to place a transaction around an entire order. Instead, you might consider using the 'snapshot' method of application development.

In a program using snapshot transactions, when the user requests SQL data the server downloads the data into local Paradox or dBASE tables. After the user updates the data locally, the SQL database is updated in a transaction that occurs after the user has finished the update. The server tables are updated all at the same time using either an SQL query or a TCursor-based transaction.

Tip:	For some queries, you can design a form which uses a QBE file in its data model which runs against the server. The form should provide local editing capabilities against the resulting Answer table. A slight modification to this same QBE allows users to post updates back up against the server, with little coding involved.

An advantage of the snapshot method is that server data is not locked for long periods of time, and still gains the benefits of transaction processing during the update. A disadvantage is that this method requires more programming work to correctly detect errors, pinpoint their cause, and resolve the situation.

Note:	Querying all data to local tables and performing edits may cause data loss due to the conversion of SQL data types to their Paradox or dBASE equivalents. (For information on data types supported by your SQL server, see your server-specific Connecting to... manual.)


Generating the Key at the End of the Transaction

Another solution is to generate unique values when posting the data, instead of generating them at the time the record is created. This creates fewer data locks while transactions are in progress, and thus less contention for data. The code for posting the data can be very similar to the code in our previous example, except that the posting process can be broken into two transactions:

1.	Obtain the unique ID, increment it, and commit the change.

2.	Put the number into the record, post it, and commit the number.

In this method, concurrency problems can only be resolved at the time the user is finished with the record. Editing sessions may run for a long period of time, or even be cancelled, without causing lock conflicts.


Optimizing Your SQL Application

This section introduces strategies to be considered when optimizing your ObjectPAL application for use in an SQL environment.

Choose a topic below for more information:

Customizing Paradox behavior

TCursors versus queries

Lookup tables

Stored procedures and triggers


Customizing behavior for SQL tables

Code sample

While Paradox's interactive editing features are effective for most situations, there are times when you must enhance or override these features.  This section shows how to customize Paradox behavior using the action() method, by trapping the action in which you are interested.

The companion code sample shows most of the actions you'll be concerned with.

Choose a topic below for more information:

How actions are generated

Determining editing states with properties

Default action behavior


Code Sample for Customizing Paradox Behavior

In this example, custom methods are called when one of the specified actions takes place.  To use this code in an application, create custom methods with the names shown above (and then place your code in the custom methods) or replace the custom method names with the code you want to use.  These actions are useful for any table type (whether SQL, Paradox, or dBASE) and let you control almost every record-oriented action a user can generate.

method action(var eventInfo ActionEvent)
var
   actionId smallInt
endVar
   actionId = eventInfo.id()
   Switch
      Case actionId = DataInsertRecord : CustomInsertCode()
      Case actionId = DataDeleteRecord : CustomDeleteCode()
      Case actionId = DataLockRecord   : CustomLockCode()
      Case actionId = DataUnlockRecord : CustomUnlockCode()
      Case actionId = DataPostRecord   : CustomPostCode()
      Case actionId = DataCancelRecord : CustomCancelCode()
   endSwitch
endmethod


How Actions Are Generated

Code sample

When customizing Paradox's built-in behavior, remember that actions can be generated in a variety of ways.  For example, the DataDeleteRecord action can be caused by pressing Ctrl+Delete, or choosing Record|Delete.  The following paragraphs show how some actions are generated:

Action	How generated

DataInsertRecord	press Insert, choose Record|Insert or start typing into the empty record at the end of the table when the Auto-Append property is TRUE

DataDeleteRecord	press Ctrl+Delete or choose Record|Delete

DataLockRecord	when you press F5, choose Record|Lock, or enter values into an unlocked record.  When working with SQL tables, remember that SQL uses optimistic locking, which means that tables are not locked until a transaction is attempted.

DataUnlockRecord	press Shift+F5, choose Record|Unlock, or move off a locked record or a new record being inserted.  If the record can't be posted (because it generates a key violation, for example), this action fails.

Can also be generated by moving to an object bound to a different table or by exiting  Edit mode (using F9 or Form|End Edit).

DataPostRecord	generated when you press Ctrl+F5, choose Record|Post/Keep Locked, or move from a locked master  record to an unlocked detail record in a table frame.  This action means "save the record, but keep it locked" and is an effective way to work with record flyaway.  (For more information about flyaway, see the Guide to ObjectPAL.)

DataCancelRecord	press Alt+Backspace, choose Edit|Undo, choose Record|Cancel Changes, or leave a locked record that has not been changed.  This action cancels unsaved changes and unlocks a record.


Determining Editing States With Properties

Code sample

For best results, your code should be aware of various editing states and react accordingly.  Use an object's properties to determine the editing state and how to react.  The following properties are useful:

Property	Description

READONLY	indicates whether or not the object can be edited

EDITING 	(on a form, tableFrame, MRO or record) indicates whether or not Paradox is in Edit mode.

LOCKED	indicates whether a Paradox has internally locked an object.  Because SQL Link files use optimistic locking, keep in mind that the record is not locked on the SQL server until a transaction is attempted.

TOUCHED	(on a form, tableFrame, MRO or record) indicates whether a user has edited any value in a record or field

(on a field object) indicates that the field itself is in the process of being changed.  The record doesn't get touched until you move off that field.

INSERTING	indicates whether or not the current record is being inserted into a table.  This record does not yet exist in the table.  It will be inserted into the table when the DataUnlockRecord or DataPostRecord action is  generated.

For examples of how to use these properties effectively, see Enhancing Default Behavior.


Default Action Behavior

See also	Code sample

When Paradox receives one of the actions described in the above code sample, it performs some default behavior.  The behavior is executed when you issue the DoDefault command or when the action method completes execution.

Following is a description of the default behavior of some actions:

Action	Default behavior

DataInsert Record	checks for errors for the current record (if locked) and makes sure it's appropriate to leave that record.  If the record can be departed, then this action inserts a temporary, unsaved record into the form you're viewing.  If you depart this record before making changes, it is deleted.  If you enter values into the new record's fields, the record is saved when you depart it.

DataDeleteRecord	removes the current record from the underlying table.  If you're working with Paradox or SQL tables, the data in the table is permanently removed from the table.  (dBASE tables support "soft-deletes," which let you undelete a record at a later time.  For more information, see the Paradox User's Guide.)

DataLockRecord	locks the record in the underlying table.  If you're working with Paradox or dBASE tables, this prevents other users from changing or deleting the record.  However, SQL tables use "optimistic" locking, which means  a record isn't locked until a transaction is attempted.  To work with this, Paradox indicates that an SQL record has been locked internally, but the actual record is not locked in the table until a dataUnlockRecord or dataPostRecord action.

DataUnlockRecord	commits (saves) changes made to a record and removes the lock.  If you change a key value that causes the record to appear in a different location in the table, the record "flies away" to the new location.  If this happens and you don't like the resulting visual behavior, use DataPostRecord to save the record and keep it locked.  If the record cannot be saved, this action fails and the lock is retained.

DataPostRecord	commits changes to a record and retains the lock.  If you change a record's key values, the record still "flies" to the new location in the table, but the form is synchronized to that new location.  This lets you save a record while making sure the saved record is still selected and displayed.

DataCancelRecord	abandons any changes to a record and removes the lock.  Visually, it restores the record to its last saved values, removes the  "Locked" message in the status bar, and displays a message.  If you execute the  default behavior, this action cannot fail.

Actions also generate internal actions to refresh (repaint) the screen and perform related internal functions.


See Also

Disabling default behavior

Enhancing default behavior


Disabling Default Behavior

Because actions also perform internal processing (such as repainting the screen), do not use the disableDefault command to prevent (block) the default behavior from executing.  Instead, use the setErrorCode() eventInfo method.  This prevents the data-oriented behavior from taking place and lets Paradox perform any required internal processing.

The setErrorCode() method requires an error constant.  If you use an error constant related to blocking the action, a warning message appears in the status line.  To prevent an action without displaying an error message, use the setErrorCode(UserError) eventInfo method.  The following table shows error constants that display messages appropriate for specific actions and the messages that appear:

Error constant	Meaning

peCannotDelete	unable to delete

peCannotEdit	you cannot modify this field

peCannotEditField	this field cannot be edited

peCannotInsertRecord	cannot insert record here

peCannotLock	cannot lock record

peCannotPerformAction	unable to perform action

peDataLoss	character(s) not supported by table language

peDeadlock	a deadlock was detected

peFieldIsBlank	field is blank

peNoActiveTransaction	no active transaction to commit or roll back

peNotLocked	object not locked

peOptRecLockFailed	record lock failed

peOptRecLockRecDel	record lock failed; record deleted

peUnlockFailed	unlock failed.

Paradox supports a variety of other error constants that may be useful.  Use enumRTLErrorConstants() to generate a list of these constants.


Enhancing Default Behavior

See Also

For the most part, your applications will enhance the default behavior of an action or set of  actions.  For example, you might cascade record edits (or deletions) from one table to a set of related tables.  This section contains tips, techniques, and ideas for enhancing specific actions.

In general, you'll want to place enhancement code after an action's default behavior executes (e.g. after a doDefault statement.)  For example, the following code sample shows one way to set the default value of a field when a record is inserted:

method action(var eventInfo ActionEvent)

   if eventInfo.id() = DataInsertRecord then
      doDefault
      If eventInfo.errorCode() = 0 then
         Orders.Sale_Date.Value = Today()
      endIf
   endIf

endMethod

In this example, Orders is an SQL table containing a field called Sale Date, which holds the date a sale is made.  The  above code waits for the user to insert a new record and then defaults the Sale Date field to the current date, provided a record was inserted properly.  (For Paradox tables, it's better to use the Default validity check.  For more information, see the Paradox User's Guide.)

There are times when you'll want to execute enhancement code before the default behavior of an action executes.  The following example shows one way to save a record that will be deleted, so it can be undeleted later.

method action(var eventInfo ActionEvent)
   if eventInfo.id() = DataDeleteRecord then
      self.copyToArray(savedRec)
      doDefault
   endIf
endMethod

In this example, the code has been placed on the record object of a table frame or a multi-record object.  SavedRec is a resizable dynamic array (dynArray) of AnyType that's been defined in a Var window of an appropriate object.  To incorporate this into an application, you'll need to include a mechanism to "undelete" the record.  (One such mechanism uses the pushButton method of a button object to insert a blank record and the copyFromArray method to restore the original record.)


See Also

DataInsertRecord enhancements

DataDeleteRecord enhancements

DataLockRecord enhancements

DataUnlockRecord/DataPostRecord enhancements

DataCancelRecord enhancements


DataInsertRecord Enhancements

Should be placed after a doDefault statement, unless you want to prevent this action in some cases.  If you want to initialize fields, be sure to do this after the default behavior executes,  as shown in an earlier code sample.  Also, use the errorCode() eventInfo method to make sure a record was inserted.  If the table is not in Edit mode, the table is read-only, or the user has insufficient access rights to the table or a field in the table, errorCode() contains an appropriate error code and a message appears in the status line.  While Paradox checks these conditions automatically, you can use errorCode() to display messages to the user or to react to the error condition.


DataDeleteRecord Enhancements

These can take many forms.  They are generally placed before the default behavior executes.  You can display a confirmation dialog, save the record so it can be undeleted later, or cascade deletions to other  tables in a single transaction.  If your code deletes the record in question, be sure to block the built-in  behavior; otherwise, two records will be deleted (the one you wanted to delete and the next one in the table).


DataLockRecord Enhancements

Should be placed after a doDefault statement.  Use the following code to make sure the lock was placed correctly:

if eventInfo.id() = dataLockRecord then
   doDefault
  if eventInfo.errorCode() = 0 then
     CustomLockCode()
  else
     CustomLockFailedCode()
  endIf
endIf

To perform related error checking before the default behavior executes, use something like this:

if eventInfo.id() = dataLockRecord then
  if not self.Editing or self.readOnly then
     eventInfo.setErrorCode(UserError)
  Else
     CustomLockCode()
  endIf
Endif

This approach may require you to block the default  behavior of this action.  Use care.


DataUnlock/PostRecord enhancements
DataUnlockRecord/DataPostRecord Enhancements

DataUnlockRecord and DataPostRecord enhancements are similar, and can be made either before or after the default behavior.  For example, you might perform some field-level validation checks before unlocking a record, or you might recalculate a summary field after saving a record.

These actions can be generated for records that are not locked, for records that were locked and not modified, and for records that were appended to the end of a table.  To work with these conditions, use an approach like this:

if actionID = dataUnlockRecord or actionID = dataPostRecord then
   if self.Touched then
      if self.Inserting then
         CustomNewRecordCode()
      else
        CustomSaveRecordCode()
      endIf
   endIf
endIf

This example lets Paradox handle the cases where these actions occur on unlocked or unmodified records and lets  you handle new records differently from changed records.  If the record wasn't locked, a silent error is generated.  If the record wasn't modified, Paradox releases the lock.

If you use TCursors, pass-through SQL, or another  non-UIObject oriented approach to post changes to a table, set the Touched property of the record to False before executing the default behavior.  This releases the lock quietly and prevents Paradox from trying to post changes  later.  An example of this approach is:

If eventInfo.id() = dataUnlockRecord then
   tc.attach(self)
  ; other code
  tc.postRecord()
  self.Touched = False
 doDefault
endIf

If you do not reset the Touched property and use doDefault, you may generate endless loops (as Paradox tries to post your changes) or other errors (as Paradox tries to remove the lock from an internal action).


DataCancelRecord Enhancements

Generally limited to  preventing users from canceling changes.  This is best accomplished by using eventInfo.setErrorCode(UserError), but there are other approaches.  If you use an alternate approach, use DoDefault to remove the lock; otherwise, you may receive unexpected results.  (Unlike DataUnlockRecord or DataPostRecord, the Touched property is not helpful with this action.)

To find out more about these actions, turn on Trace Execution and Trace Builtins for the action() method (as described in the Guide to ObjectPAL).  This tracer enables you to see the  general flow of events through the system, which can help you  better understand what specific actions your code needs to address.


TCursors vs. queries
TCursors versus Queries

If you port into an SQL environment a Paradox application which makes extensive use of TCursors, you will see that TCursors behave exactly as they do within Paradox. In one sense this is a great benefit, since it enables you to run code unmodified against either server or local data. However, many programs use filters and scan loops to modify sets of data, and would benefit more from the use of SQL queries.

The following example illustrates how to use a TCursor in a program that calculates the total of several items in a line item set for the cursor order. The next example illustrates that using a query can obtain similar results.

var
  tc Tcursor
endvar
  tc.attach( LineItemDetail )	; where LineItemDetail is a
  	; tableframe for the current order.
tc.edit()
Scan tc :
tc."Total" = tc."Quantity" * tc."Price" * 1.085
endScan
tc.endEdit()

This code performs even faster than a query against local Paradox data, because there is already a cursor to a subset of the Line Items table using a known index. The code scans through and updates values quickly in this filtered set. However, this may not be a fast operation against a remote SQL server.

When the above code executes, SQL Link cannot distinguish that the code is performing an operation against a set. Therefore, it is unable to optimize the operation at the SQL server. SQL Link must download the SQL data to Paradox (which performs the calculation) then issue an UPDATE SQL query to update that record. For one or two records, the difference in speed does not matter, but as the number of processed records rises the operation becomes less efficient.

Since one of the primary advantages of SQL is its ability to issue queries against large amounts of data and decrease network traffic, TCursor implementations can put SQL at a disadvantage. In this case, an SQL query might create a more optimized transaction.

Query

LineItems   | OrderNo          | Quantity | Price  | Total                     |
| ~(OrderNo.value) | _quant   | _price | changeto_quant*_price*1.08|

EndQuery

If you save this query as CALCPRICE.QBE, you may execute it from within your code:

var
  db Database
  any Query
endvar
  db.open( ":IB_Data:" )
qry.readFromFile ("Calcprice=")
qry.executeQBE( db )

The above method takes full advantage of SQL server query optimization and decreases network traffic. Much of the heavy network traffic which occurs in networked Paradox applications can be eliminated when this approach is used effectively.

Caution:	If you send the SQL statement to the server using the SQL Editor window, be aware that the issued query is considered part of a different connection than QBE, ObjectPAL, or a form, and thus cannot participate in an open ObjectPAL transaction. If you wish the query to participate in an open transaction, frame the query in the QBE environment instead of passing an SQL string directly to the server.


Lookup Tables

Unlike Paradox tables, SQL tables do not provide a built-in lookup help and fill feature, which is used heavily in Paradox applications. You need to create a TCursor against the SQL table and provide explicit code to look up the entered value manually. If your local ObjectPAL application already provides some type of lookup capability, your application can run against an SQL server without change.

When there are many small lookup tables that can be easily relocated to local tables, you might consider using them locally. This will increase the performance of your applications, particularly if you are performing interactive transactions against the server and you expect the lookup to be performed many times. Since doing a TCursor lookup against a SQL table involves a query, moving the table to a local directory speeds up the process significantly.

It is important to remember that SQL Link uses TCursors to provide transparent access to both SQL data and Paradox data. This enables you to choose which tables to upload to the server and which tables you wish to be on the network or local hard drive, in order to obtain the best possible performance in a busy network environment.


Stored Procedures and Triggers

Many kinds of data validation and unique key generation can be performed at the SQL server by triggers or stored procedures. These can decrease the amount of application code you write at the client and improve the overall performance of the application. Whenever you plan to port code that should occur when the user inserts, deletes, or updates a record, consider whether the operation could easily be performed by a trigger or stored procedure.


Pass-through SQL

The pass-through SQL feature enables users to "pass" SQL statements directly to the SQL server using the SQL Editor window.  Unlike queries made through your Paradox for Windows desktop, pass-through SQL queries are not filtered through the IDAPI database engine and translated into the appropriate SQL dialect.  Pass-through statements must use the same semantics as statements entered directly to the SQL server.

Pass-through SQL is considered to be a separate kind of connection from the conventional desktop application connection.  However, your alias' SQLPASSTHRU MODE can be set to allow desktop commands and pass-through SQL statements in the same alias connection.  For further information, see the IDAPI Configuration Utility online Help or your SQL Links Getting Started manual.


Transaction

A group of related operations that must all be performed successfully before the RDBMS will finalize any changes to the database.

In SQL, all transactions can be explicitly ended with a command to either accept or discard the changes. Once you are satisfied that no errors occurred during the transaction, you can end that transaction with a COMMIT command. The database then changes to reflect the operations you have just performed. If an error occurs, you can abandon the changes with the ROLLBACK command.


Supported Borland applications

Borland SQL Links works with any application that supports the Borland Database Engine (also known as IDAPI, the Integrated Database Application Programming Interface).  This includes Paradox for Windows, dBASE for Windows, Quattro Pro for Windows Database Desktop, and custom applications built using the Borland Database Engine.


Supported databases

Drivers in the SQL Links product package support InterBase, Informix, ORACLE, and SYBASE and Microsoft SQL Server databases.


Heterogeneous queries

Queries that involve more than one table type; for example, a Paradox table, a dBASE table, and an InterBase table.


beginTransaction() Example

The following example processes a withdrawal of cash from an ATM. The call to beginTransaction starts a transaction consisting of  three operations:

debiting the customer's account

debiting the cash on hand

dispensing cash to the customer

The result of each operation is stored in a DynArray. When all  the operations have been completed, this code checks each item in  the DynArray and either calls commitTransaction() (if all items are true) or rollbackTransaction() (if any item is false).

This example uses the beginTransaction(), commitTransaction(), rollbackTransaction(), transactionActive(), and enumAliasName() methods.

method pushButton(var eventInfo Event)

var
myQBE	Query
db	Database
opResult	DynArray[ ] Logical
Element	AnyType
All_OK	Logical
serverType,
myAlias,
custID	String
aliasNamTC	TCursor
xAmount	Currency
xDate	Date
xTime	Time
endvar

; Initialize variables
myAlias = "ITCHY"
custID = "RHALL001"
xAmount = Currency(120.00)
xDate = today()	; Returns current date
xTime = time()	; Returns current time

enumAliasNames("aliasNam.db")	; List aliases known to the system

aliasNamTC.open("aliasNam.db")

if aliasNamTC.locate("DBName", myAlias) then
db.open(myAlias)	; Use alias to get handle to server
else
msgStop("Problem", "The alias " + myAlias + " has not been defined.")
return	; Exit the method
endIf

if db.transactionActive() then

db.commitTransaction()	; Commit any previous transaction
endIf

db.beginTransaction()	; Begin a Tranaction

; Execute the operations for this transaction.
; debitAccount, debitCashOnHand, and dispenseCash
; are custom procs assumed to be defined elsewhere.

; After calling debitAccount and debitCashOnHand, the code
; calls transactionActive to check the transaction status
; before calling dispenseCash.

opResult["Debit customer account"] = debitAccount(custID, xAmount)
opResult["Debit cash on hand"] =
     debitCashOnHand(xAmount, xDate, xTime)

; The following if...then...else block is not required.
; It's included to show one way to use the transactionActive() method.

if db.transactionActive() then	; Make sure everything is OK
msgInfo("Transaction Status", "In a Transaction")
else
errorShow("NOT in a Transaction")
return
endif

opResult["Dispense cash"] = dispenseCash(xAmount)
All_OK = True	; Initialize to True

forEach element in opResult	; Check operation results
if opResult[element] = False then
All_OK = False
quitLoop
endIf
endForEach

; Inform user of transaction status

if All_OK then
if db.commitTransaction() then
msgInfo("Transaction Status","Transaction committed.")
else
errorShow("Transaction NOT committed")
endIf
else
if msgQuestion("Transaction failed", "View results?") = "Yes" then
opResult.view("Operation results")
endIf

if db.rollbackTransaction() then
msgInfo("Transaction Status", "Transaction rolled back.")
else
errorShow("Transaction NOT rolled back.")
endIf
endif

endmethod


isoLevel (isolation level)

The isolation level of a transaction to be performed through the beginTransaction() database method.  Possible values:

DirtyRead  -- Provides no isolation.  Appropriate for static tables that are used for queries.  The only isolation level available to databases that do not have transactions.

ReadCommitted -- Guarantees that every retrieved row is committed in the table at the time that the row is retrieved, but does not prevent other processes from acquiring an exclusive lock on that same row.  This makes it possible for another user to acquire a lock on that row, and modify or delete it, before you commit your transaction.

RepeatableRead -- Acquires a lock on every row selected during the transaction.  This prevents another user from modifying any selected row during your transaction.

Note:	There is no ObjectPAL equivalent to the Informix CursorStability isolation level.