INF: When and How to Use dbcancel() or sqlcancel()

• Microsoft SQL Server Programmer's Toolkit, version 4.2

SUMMARY

The function dbcancel() and sqlcancel(), the Visual Basic SQL (VBSQL) equivalent, are often used in applications when they should not be. A large percentage of common DB-Library (DB-Lib) or VBSQL programming problems stem from applications that misuse this API call. This article gives some practical guidelines on when and when not to use dbcancel() and Sqlcancel().

MORE INFORMATION

It is generally recommended and considered good DB-Library programming practice to processes all results until there are no more results and all rows until there are no more rows when retrieving results or after sending a Transact-SQL batch to the server.

A DB-Library program should never have calls to dbresults()/sqlresults() and dbnextrow()/sqlnextrow() hard coded to a predetermined number of iterations.

For example, you should call dbresults() and dbnextrow() like this:

   while ((result_code = dbresults(dbproc)) != NO_MORE_RESULTS)
        {
             while (dbnextrow(dbproc) != NO_MORE_ROWS)
        }

If for some reason an application is required to access only x rows from a result set, it is recommend that the transact SQL command "set rowcount x" be used instead of calling dbnextrow x times and then dbcancel(). There are several reasons for not using dbcancel() as part of standard results processing routines.

When Not to Use dbcancel()

dbcancel() will not cancel, rollback, or commit a user-defined transaction. All exclusive locks acquired within the user-defined transaction will be retained even after issuing dbcancel(), since the user-defined transaction is still active. This can cause blocking and other difficult concurrency problems. When issuing dbcancel() within a user-defined transaction, the programmer should ensure that the transaction is either explicitly committed or rolled back.

Furthermore when operating in this context, it is easy to miss that an application is actually in the midst of a user-defined transaction that was never committed and/or rolled back. In addition, there are also some variations of how dbcancel or the attention signal that it generates are implemented from platform to platform.

NOTE: Microsoft SQL Server implementations of dbcancel() are constant across all Microsoft SQL Server platforms such as Windows NT x86, OS/2, and Windows NT RISC platforms. Compatibility issues arise primarily between different Sybase and Microsoft SQL Server implementations. Also these differences often stem from transport related issues such as how one vendor's TCP/IP out-of-band data is implemented and how it interacts with another vendor's TCP/IP out-of-band data implementation. The net results of which can cause portability problems in DB-Library applications using dbcancel().

When to Use dbcancel()

dbcancel() should be used in cases where the user needs to regain control of an application. In this environment, a programmer can handle the exception of a user issuing a dbcancel in an environment where dbcancel() does not work by responding with a message like "dbcancel() not supported on this platform!". A programmer may also want to use dbcancel() in case of a DB-Library command failure as part of a clean up procedure for the DB-Lib processes effected.