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
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
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, cause
application problems that may not become evident until later in development
or testing when corrective action is costly.
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
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, Alpha, and 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
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