1081-1083

Previous Table of Contents Next

Page 1081

Note that the parameter passed to SqlConnect is of type SqlHandle. This is an abstract datatype used in SAL exclusively for database communications.

After connecting to the data source, SQL can be processed in a number of different ways. SAL provides methods for prepared and immediate execution of SELECT statements, as well as INSERT, UPDATE, and DELETE. The implementation of immediate execution in SAL is actually just a shortcut to using prepared execution. This point will be illustrated with the following examples. SQLWindows provides several alternative methods for retrieving a result set.

The most verbose of these options is to first call SqlPrepare(), followed by SqlExecute() and SqlFetch() to retrieve the first row. The code fragment in Listing 47.2 illustrates the use of these methods.

Listing 47.2. Retrieving a result set.

 Contents     Pushbutton:pbRefresh         Message Actions             On SAM_Click                 Set szSQL = "SELECT Last_name, First_name                     FROM TEMP INTO :szLast, :szFirst".                 Set bSuccess = SqlPrepare(hSQLMain, szSQL)                 If (bSuccess = TRUE)                     Set bSuccess = SqlExecute(hSQLMain)                 If (bSuccess = TRUE)                     Set bSuccess =  SqlFetchNext(hSQLMain, iRet) Window Variables     Sql Handle: hSQLMain     Boolean: bSuccess     String: szSQL     Number: iRet 

Note that the SELECT statement must provide parameters to receive column values.

If columns are not bound when the statement is prepared, the application will not be able to retrieve the values at runtime. The second parameter to SqlFetchNext() is of some interest as well. It is used to receive a code indicating the current state of the row that was just fetched . This code has four possible values:

• FETCH_Delete: Indicates that the row has been deleted since the cursor was built
• FETCH_EOF: Indicates that the end of the cursor has been reached
• FETCH_Ok: Indicates that the record has not been modified
• FETCH_Update: Indicates that the record has been modified since the cursor was created

This value may not be entirely accurate depending on the transaction isolation level. For example, if the transaction isolation level is set to read-only, this value would be either FETCH_Ok or FETCH_EOF because the snapshot would not be refreshed after the cursor was created. In most

Page 1082

database environments, transaction isolation should be set to read-only, if possible, as this reduces contention and improves performance. Unless an application has a specific need that requires in-place updates or similar cursor operations, this level of isolation should be acceptable.

A possible alternative to the methods demonstrated in Listing 47.2 is to combine the SqlPrepare() and SqlExecute() function calls with a single call to SqlPrepareAndExecute(), as illustrated in the following:

 Set bSuccess = SqlPrepareAndExecute(hSQLMain, szSQL) If (bSuccess = TRUE)     Set bSuccess =  SqlFetchNext(hSQLMain, iRet) 

The operation can be simplified further with a call to SqlImmediate():

 Set bSuccess = SqlImmediate(szSQL) 

After making the call to SqlImmediate(), the first row is already bound to application variables. Subsequent calls to SqlFetchNext() will begin with the second row in the result set. Unfortunately, the first time SqlImmediate() is called, it calls SqlConnect() with a handle that it manages internally. For this reason, it can be somewhat slow the first time it is called. However, each subsequent time it calls only the remaining three functions: SqlPrepare(), SqlExecute(), and SqlFetchNext(). It can also be used with transaction SQL, in which case, it does not make the call to SqlFetchNext().

Transaction-based SQL can be processed using the same functions, in a very similar manner. Listing 47.3 is an example of a transaction that inserts a record based on values in the data fields of a form.

Listing 47.3. This code fragment inserts rows based on the values in the data fields of a form and demonstrates transaction control in SQLWindows.

 Contents     Pushbutton:pbRefresh         Message Actions             On SAM_Click                 Set szSQL1 = "SELECT individual_ids.nextval                              INTO :iNewID FROM dual"                 Set bSuccess = SqlImmediate(szSQL1)                 Set szSQL2 = "INSERT INTO individual VALUES (                         :iNewID, :dfLast, :dfFirst, :dfNotes)"                 Set szSQL3 = "INSERT INTO phone VALUES (                         :iNewID, :dfType, :dfNumber)"                 If (bSuccess = TRUE)                     Set bSuccess = SqlPrepareAndExecute(hSQLMain,                                    szSQL2)                 If (bSuccess = FALSE)                     Call SqlPrepareAndExecute(hSQLMain, `ROLLBACK')                 Else                     Set bSuccess = SqlPrepareAndExecute(szSQL3) 

Page 1083

 If (bSuccess = FALSE)                     Call SqlPrepareAndExecute(hSQLMain, `ROLLBACK')                 Else                     Call SqlCommit(hSQLMain) Window Variables     Sql Handle: hSQLMain     Boolean: bSuccess     String: szSQL1     String: szSQL2     String: szSQL3     Number: iRet 

In the example, if either insert fails, the entire transaction is rolled back. Note the somewhat odd way in which commits and rollbacks are handled by SAL. SqlCommit() is a function, while no corresponding function exists for a rollback. The rollback must be accomplished through another call to SqlPrepareAndExecute().

Although SQLWindows has no functions specifically designed to support stored procedures, the ODBC syntax for Oracle can be used to execute a stored procedure using the methods previously described. For example, instead of the separate SqlPrepareAndExecute() statements in Listing 47.3, a single statement could be used to execute a stored procedure, as illustrated in the following:

 Set szSQL2 = "{call insert_indiv_phone(dfLast, :dfFirst, :dfNotes,             :dfType, :dfNumber)}" Set bSuccess = SqlPrepareAndExecute(hSQLMain, szSQL2) 

This example assumes that the stored procedure is using the sequence to get the new id, and it is handling commits and rollbacks internally.

The methods that have been described for communicating with the database from SQLWindows are a subset of a larger API. Additional functions include methods for scrolling cursors backwards , or to an absolute row position, naming transactions and cursors, and determining the number of rows in a result set before they are fetched.

Unfortunately, some of the more useful functions apply only to SQLBase connections. For example, the SqlError() and SqlGetErrorText() functions can be used to retrieve the error number, description, and remedy for the database error that occurred most recently. However, these values are not received from the driver, but read from a text file that ships with the product, containing only SQLBase error codes, messages, and remedies. Despite a few SQLBase-only features, the methods provided by SAL are fairly comprehensive, and they will prove adequate for most client/server development projects.

Summary

In addition to extensive support for database applications, the object-oriented features of SQLWindows are numerous , although somewhat unorthodox. It supports inheritance, multiple inheritance, and the capability to define classes based on visual and non-visual objects.

Previous Table of Contents Next