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

This article was previously published under Q117143
This article has been archived. It is offered "as is" and will no longer be updated.
The function dbcancel() and sqlcancel(), the Visual Basic SQL (VBSQL)equivalent, are often used in applications when they should not be. A largepercentage of common DB-Library (DB-Lib) or VBSQL programming problems stemfrom applications that misuse this API call. This article gives somepractical guidelines on when and when not to use dbcancel() andSqlcancel().
More information
It is generally recommended and considered good DB-Library programmingpractice to processes all results until there are no more results and allrows until there are no more rows when retrieving results or after sendinga 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 ofiterations.

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 can, and often does, causeapplication problems that may not become evident until later in developmentor testing when corrective action is costly.

If for some reason an application is required to access only x rows from aresult set, it is recommend that the transact SQL command "set rowcount x"be used instead of calling dbnextrow x times and then dbcancel(). There areseveral reasons for not using dbcancel() as part of standard resultsprocessing 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 beretained even after issuing dbcancel(), since the user-defined transactionis still active. This can cause blocking and other difficult concurrencyproblems. When issuing dbcancel() within a user-defined transaction, theprogrammer should ensure that the transaction is either explicitlycommitted or rolled back.

Furthermore when operating in this context, it is easy to miss that anapplication is actually in the midst of a user-defined transaction that wasnever committed and/or rolled back. In addition, there are also somevariations of how dbcancel or the attention signal that it generates areimplemented from platform to platform.

NOTE: Microsoft SQL Server implementations of dbcancel() are constantacross all Microsoft SQL Server platforms such as Windows NT x86, Alpha, and RISC platforms. Compatibility issues arise primarily betweendifferent Sybase and Microsoft SQL Server implementations. Also, thesedifferences often stem from transport-related issues such as how onevendor's TCP/IP out-of-band data is implemented and how it interacts withanother vendor's TCP/IP out-of-band data implementation, the net results ofwhich can cause portability problems in DB-Library applications usingdbcancel().

When to Use dbcancel()

dbcancel() should be used in cases where the user needs to regain controlof an application. In this environment, a programmer can handle theexception of a user issuing a dbcancel in an environment where dbcancel()does not work by responding with a message like "dbcancel() not supportedon this platform!". A programmer may also want to use dbcancel() in case ofa DB-Library command failure as part of a clean up procedure for the DB-Libprocesses effected.
4.20a oob begin tran end dblib

Article ID: 117143 - Last Review: 10/26/2013 12:42:00 - Revision: 4.0

Microsoft SQL Server 4.21a Standard Edition, Microsoft SQL Server 6.5 Standard Edition

  • kbnosurvey kbarchive kbinfo kbprogramming KB117143