When and How to Use dbcancel()
The dbcancel() function is often used in applications when it should not be. A large percentage of common DB-Library or VBSQL programming problems stem from applications that misuse this API call.
When retrieving results or after sending a Transact-SQL batch to the server, it is considered good DB-Library programming practice to process all results until there are no more results, and to process all rows until there are no more rows. For example, you should call dbresults() and dbnextrow() like this:
while ((result_code = dbresults(dbproc)) != NO_MORE_RESULTS)
{
while (dbnextrow(dbproc) != NO_MORE_ROWS)
}
Not calling dbresults() and dbnextrow() as above often causes application problems that may not become evident until later in development or testing when corrective action is costly.
If an application is required to access only x number of rows from a result set, it is recommended that the SET ROWCOUNT x Transact-SQL statement be used instead of calling dbnextrow x times followed by a call to 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, roll back, or commit a user-defined transaction. All exclusive locks acquired within the user-defined transaction will be retained even after issuing dbcancel() because 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.
When operating in this context, it is easy to miss that an application is actually in the middle of a user-defined transaction that was never committed 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 SQL Server platforms. Compatibility issues arise primarily between different Sybase and SQL Server implementations. 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 such as "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 affected DB-Lib processes.