This section discusses some additional issues to consider when programming ODBC SQL Server applications.
Some ODBC functions can operate either synchronously or asynchronously (see the ODBC 2.0 Programmer's Reference for the list of functions). The application can enable asynchronous operations for either a statement handle or a connection handle. If the option is set for a connection handle, it affects all statement handles on the connection handle. The application uses the following statements to enable or disable asynchronous operations:
SQLSetConnectOption(hdbc, SQL_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON);
SQLSetConnectOption(hdbc, SQL_ASYNC_ENABLE, SQL_ASYNC_ENABLE_OFF);
SQLSetStmtOption(hdbc, SQL_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON);
SQLSetStmtOption(hdbc, SQL_ASYNC_ENABLE, SQL_ASYNC_ENABLE_OFF);
When an application calls an ODBC function in synchronous mode, the driver does not return control to the application until it is notified that the server has completed the command.
When operating asynchronously, the driver immediately returns control to the application, even before sending the command to the server. The driver sets the return code to SQL_STILL_EXECUTING. The application is then free to perform other work.
To test for completion of the command, make the same function call with the same parameters to the driver. If the driver is still waiting for an answer from the server, it will again return SQL_STILL_EXECUTING. The application must keep testing the command periodically until it returns something other than SQL_STILL_EXECUTING. When the application gets some other return code, even SQL_ERROR, the command has completed.
Sometimes a command is outstanding for a long time. If the application needs to cancel the command without waiting for a reply, it can do so by calling SQLCancel with the same statement handle as the outstanding command. This is the only time SQLCancel should be used. Some programmers use SQLCancel when the application has processed part way through a result set and they want to cancel the rest of the result set. SQLMoreResults or SQLFreeStmt with fOption set to SQL_CLOSE should be used to cancel the remainder of an outstanding result set, not SQLCancel.
The SQL Server ODBC driver is a fully multithreaded driver. Writing a multithread application is an alternative to using asynchronous calls to have multiple ODBC calls outstanding. A thread can make a synchronous ODBC call, and other threads can process while the first thread is blocked waiting for the response to its call. This model is more efficient than making asynchronous calls because it eliminates the overhead of the repeated ODBC function calls testing for SQL_STILL_EXECUTING to see if the function has completed.
Asynchronous mode is still an effective method of processing. The performance improvements of a multithread model are not enough to justify rewriting asynchronous applications. If users are converting DB-Library applications that use the DB-Library asynchronous model, it is easier to convert them to the ODBC asynchronous model.
Multithread applications need to have coordinated calls to SQLError. After a message has been read from SQLError, it is no longer available to the next caller of SQLError. If a connection or statement handle is being shared between threads, one thread may read a message needed by the other thread.
The ODBC standard is closely matched to the ANSI SQL standard, and ODBC applications expect standard behavior from an ODBC driver. To make its behavior conform more closely with that defined in the ODBC standard, the SQL Server ODBC driver always uses any ANSI options available in the version of SQL Server to which it connects. The server exposes these ANSI options through the Transact-SQL SET statement. The driver also sets some other options to help it support the ODBC environment.
The SQL Server ODBC driver that ships with SQL Server 6.5 issues the following Transact-SQL SET statements:
SET QUOTED_IDENTIFIER ON
SET TEXTSIZE 2147483647
SET ANSI_DEFAULTS ON
SET CURSOR_CLOSE_ON_COMMIT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET ANSI_NULL_DFLT_ON ON
SET TEXTSIZE 2147483647
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET TEXTSIZE 2147483647
SET ARITHABORT ON
The driver issues these statements itself; the ODBC application does nothing to request them. Setting these options allows ODBC applications using the driver to be more portable because the driver's behavior then matches the ANSI standard.
DB-Library based applications, including the SQL Server utilities, generally do not turn these options on. Sites observing different behavior between ODBC or DB-Library clients when running the same SQL statement should not assume this points to a problem with the ODBC driver. They should first rerun the statement in the DB-Library environment (such as ISQL/w) with the same SET options as would be used by the SQL Server ODBC driver.
Since the SET options can be turned on and off at any time by users and applications, developers of stored procedures and triggers should also take care to test their procedures and triggers with the SET options listed above turned both on and off. This ensures that the procedures and triggers work correctly regardless of what options a particular connection may have SET on when they invoke the procedure or trigger.
The SET options used by the version 2.65 driver when connected to SQL Server 6.5 has the net effect of setting on three more ANSI options than in the 6.0 environment: ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS. These options can cause problems in existing stored procedures and triggers. The version 2.65.0240 driver that shipped with SQL Server 6.5 SP2 allows data sources and connection statements to turn these options off. For more information, see Microsoft Knowledge Base article Q149921.
The version 2.50 driver that shipped with SQL Server 6.0 also sets on the QUOTED_IDENTIFIER option. With this option set on, SQL statements should comply with the ANSI rule that character data strings be enclosed in single quotes and that only identifiers, such as table or column names, be enclosed in double quotes:
SELECT "au_fname"
FROM "authors"
WHERE "au_lname" = 'O''Brien'
For more information about working with QUOTED_IDENTIFIER, see Microsoft Knowledge Base article Q156501.
Like the ANSI options noted above, the version 2.65.0240 driver that shipped with SQL Server 6.5 SP2 allows SQLDriverConnect, SQLBrowseConnect, and data sources to specify that QUOTED_IDENTIFIERS not be turned on.
ODBC applications should not use the Transact-SQL SET statement to turn these options on or off. They should only set these options in either the data source or the connection options. The logic in the driver depends on it correctly knowing the current state of the SET options. If the application issues the SET statements itself, the driver may generate incorrect SQL statements due to not knowing that the option has been changed.
This section discusses how to interpret the error messages that are returned by the SQL Server ODBC driver. All ODBC functions have return codes. The ODBC header files have #define statements that equate the return codes to literals, such as SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, and SQL_ERROR. If a function returns SQL_SUCCESS_WITH_INFO, it means the function was successful but there is information available. If a function returns SQL_ERROR, it means the function failed and there is information available indicating the nature of the problem. To get these messages, the application can call SQLError. SQLError returns three parameters that have important information:
The heading of szErrorMsg can be used to determine the source of the error:
[Microsoft][ODBC Driver Manager]
These are errors encountered by the ODBC Driver Manager.
[Microsoft][ODBC Cursor Library]
These are errors encountered by the ODBC Cursor Library.
[Microsoft][ODBC SQL Server Driver]
If there are no other nodes identifying other components, these are errors encountered by the driver.
[Microsoft][ODBC SQL Server Driver][Net-Libraryname]
These are errors encountered by the Net-Library, where Net-Libraryname is the name of a SQL Server Net-Library (see "Setup and Connecting" for a list of the names). This also includes errors raised from the underlying network protocol because these errors are reported to the driver from the Net-Library. In these errors, the pfNative code contains the actual error returned by the network. (For more information about pfNative codes, see "pfNative Error Codes," later in this paper.) The remainder of the message contains two parts: the Net-Library function called, and (within parenthesis afterward) the underlying network API function called.
[Microsoft][ODBC SQL Server Driver][SQL Server]
These are errors encountered by SQL Server. In this case, the pfNative parameter is the SQL Server error code.
For example, when an application attempts to open a named-pipe connection to a server that is currently shut down, the error string returned is:
[Microsoft][ODBC SQL Server Driver][dbnmpntw]ConnectionOpen (CreateFile())
This indicates that the driver called the dbnmpntw ConnectionOpen function and that dbnmpntw in turn called the named-pipe API function CreateFile.
The value of the pfNative code in an error message is based on the source of the error:
For more information about the codes returned by the different underlying network protocol stacks, see the following sources:
These codes are generally the same as those listed in Operating System Error Codes in Microsoft Knowledge Base article Q116401.
These codes, returned by the Winsock API, are listed in "Appendix A, Error Codes," of the Windows Sockets Specification 1.1. The Windows Sockets Specification can be found on the MSDN Library compact disc.
These codes, returned from Novell, are in Novell NetWare Client Protocol Transport API for C under the section for the API function listed in the szErrorMsg. For example, if the pfNative is 253, and szErrorMsg lists SPXListenForSequencedPacket as the function, the reference manual states a 0xFD (253) return from SPX Listen For Sequenced Packet is a Packet Overflow.
These codes, returned from Banyan Vines, are listed in the Vines Client Developer's Guide.
These codes, returned by the RPC API, are listed in the RPC section of Winerror.h.
The values for the SQLState code are listed in the Microsoft ODBC 2.0 Programmer's Reference and SDK Guide.