<< Click to Display Table of Contents >> Navigation:  Application development > Database driver characteristics > SQL drivers > Passthrough SQL

The native SQL dialect of the SQL server can be passed directly to the server, as long as the appropriate BDE driver is installed. These passthrough SQL queries can be executed directly by using DbiQExecDirect or in stages. See Querying Databases.

The SQLPASSTHRU MODE parameter of the BDE configuration file allows you to specify whether passthrough and non-passthrough SQL operations can share the same connection. It also allows you to specify whether you want passthrough SQL to be autocommitted or not (if the connection is shared). When passthrough and non-passthrough SQL operations share the same connection, transaction control statements should not be executed in passthrough SQL. Instead, use DbiBeginTran and DbiEndTran.

Update of Simple Unidirectional SQL Passthrough Queries

Certain SQL servers support these dynamic SQL statements:

UPDATE ... WHERE CURRENT of CursorName

DELETE ... WHERE CURRENT of CursorName

BDE supports this syntax, provided that it is also supported by the server.

Use the statement property stmtCURSORNAME to set or get the cursor name from the passthrough SELECT statement and use it in the UPDATE statement. For example:

    ...

   DbiQPrepare(hDb, 

               qrylangSQL, 

               "SELECT * FROM FOO FOR UPDATE OF f1", 

               &hStmt);

 

   // set the cursor name for the SELECT statement

   DbiSetProp(hStmt,  

              stmtCURSORNAME,  

              pszCursorName);  

 

   // set unidirectional cursor

   DbiSetProp(hStmt,   

              stmtUNIDIRECTIONAL, 

              TRUE);               

 

   // execute the SELECT stmt

   DbiQExec(hStmt, 

            &hCur); 

 

   // fetch a record

   DbiGetNextRecord(hCur, 

                    dbiNOLOCK, 

                    pRecBuf, 

                    NULL);        

 

   // Note that we use DbiQExecDirect to execute the UPDATE

   // statement in this example.

   // DbiQPrepare/DbiQExec/DbiQFree can be used instead of

   // DbiQExecDirect to execute the UPDATE

 

   sprintf(pszQuery, 

           "UPDATE foo SET f1 = 'X' WHERE CURRENT of %s",

           pszCursorName);

 

   // update the current record

   DbiQExecDirect(hDb, 

                  qrylangSQL, 

                  pszQuery, 

                  NULL);         

 

   // free the SELECT stmt

   DbiQFree(&hStmt);

 

   // close the SELECT cursor

   DbiCloseCursor(&hCur);    

   ...

 

Certain drivers require that you set the cursor name BEFORE the SELECT statement is executed (as in the above example). Other drivers do not require you to explicitly set the cursor name and will generate one for you. If the server generates a cursor name, you can retrieve that name by calling DbiGetProp AFTER the SELECT statement has been executed. As always, when using passthrough SQL, you must know the native syntax supported by the back end server.

Where not supported, the function DbiSetProp with stmtCURSORNAME will return DBIERR_NOTSUPPORTED.

InterBase

By default the InterBase SQL Link driver must close cursors when transactions end (COMMIT/ABORT occurs). When this happens, the remaining rows are read from the server and cached locally. This means that a COMMIT/ABORT can cause you to lose your current cursor position, and a subsequent UPDATE ... WHERE CURRENT can update the WRONG row. For this reason, you must be certain that a COMMIT/ABORT does not cause SQL Link to prematurely close the server cursor.

There are two ways to guarantee this:

1.Set your SQLPASSTHRU MODE to NOT SHARED. In this  mode, all passthrough statements are performed on a separate connection and will NOT be autocommitted.

2.If your SQLPASSTHRU MODE is either SHARED AUTOCOMMIT or SHARED NOAUTOCOMMIT, passthrough and non-passthrough statements share the same connection. Operations performed within an explicit transaction (that is, within the DbiBeginTran/DbiEndTran block) are never autocommitted.

By adding 4096 to the setting of DRIVER FLAGS in the BDE configuration, you can specify that the InterBase SQL Links driver should use soft commits. Soft commits are a feature of InterBase that let the driver retain the cursor when commiting changes. Soft commits improve performance on updates to large sets of data. When not used, the BDE must re-fetch all the records, even for a single record change. With soft commit the cursor is retained, and a re-fetch isn't needed. Soft commits are never used in explicit transactions started by BDE client applications.