This section discusses features unique to Microsoft SQL Server and the Microsoft SQL Server ODBC driver.
The COMPUTE BY clause generates subtotals within a result set, and the COMPUTE clause generates a total at the end of the result set. The SQL Server ODBC driver presents these totals and subtotals back to the calling application by generating multiple result sets for each SELECT.
The following example uses COMPUTE BY to generate subtotals and COMPUTE to generate a total:
SELECT title = CONVERT(char(20), title), type, price, advance
FROM titles
WHERE ytd_sales IS NOT NULL
AND type like '%cook%'
ORDER BY type DESC
COMPUTE AVG(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)
These statements cause a subtotal calculation for the average price and sum of advances for each book type and then cause a final total sum of both the price and advance data. The following ODBCTest GetDataAll output shows how the ODBC driver presents these subtotals and totals back to the calling application as separate result sets intermixed with the primary result set:
"title", "type", "price", "advance"
"Onions, Leeks, and G", "trad_cook ", 20.9500, 7000.0000
"Fifty Years in Bucki", "trad_cook ", 11.9500, 4000.0000
"Sushi, Anyone? ", "trad_cook ", 14.9900, 8000.0000
3 rows fetched from 4 columns.
"AVG", "SUM"
15.9633, 19000.0000
1 row fetched from 2 columns.
"title", "type", "price", "advance"
"Silicon Valley Gastr", "mod_cook ", 19.9900, .0000
"The Gourmet Microwav", "mod_cook ", 2.9900, 15000.0000
2 rows fetched from 4 columns.
"AVG", "SUM"
11.4900, 15000.0000
1 row fetched from 2 columns.
"SUM", "SUM"
70.8700, 34000.0000
1 row fetched from 2 columns.
You can see from the output above that the driver presents the first result set for the rows from books having the first book type. It then produces a second result set with the two COMPUTE BY columns for the AVG(price) and SUM(advance) for this first set of books. Then it produces a third result set for the next group of books, and a fourth result set with the COMPUTE BY subtotals for that group. The driver keeps interleaving these result sets until the end, when it produces the final result set with the total for the COMPUTE SUM(price), SUM(advance) clause.
Applications running SQL Server statements with COMPUTE BY and COMPUTE clauses must be coded to handle the multiple result sets returned by the driver.
The Microsoft SQL Server ODBC driver only supports COMPUTE BY or COMPUTE with the default forward_only, read_only cursors with a rowset size of one. The driver implements all other cursor types (dynamic, static, or keyset-driven) using server cursors, which do not support COMPUTE BY or COMPUTE.
The Microsoft Distributed Transaction Coordinator (MS DTC) allows applications to distribute transactions across two or more SQL Servers. It also allows applications to participate in transactions managed by transaction managers that comply with the X/Open DTP XA standard. (For more information, see What's New in SQL Server 6.5 and the Guide to Microsoft Distributed Transaction Coordinator in the SQL Server 6.5 manuals.) ODBC applications using the driver that ships with SQL Server 6.5 can participate in MS DTC transactions.
Normally, all transaction management commands go through the ODBC driver to the server (see "Autocommit vs. ANSI Transaction Management"). The application starts a transaction by calling:
SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
The application then performs the updates comprising the transaction and calls SQLTransact with either the SQL_COMMIT or SQL_ROLLBACK option.
When using MS DTC, however, MS DTC is the transaction manager and the application no longer uses SQLTransact. The application:
SQLSetConnectOption(hdbc,
SQL_COPT_SS_ENLIST_IN_DTC,
(UDWORD)pTransaction);
where pTransaction is a pointer to the transaction object.
For information about MS DTC, see the Guide to Microsoft Distributed Transaction Coordinator, which includes a sample ODBC SQL Server MS DTC application.
SQL Server 6.5 introduced fallback support. In fallback support, one server is defined as a fallback server to another, primary, server. If the primary server fails for some reason, then applications can switch to the fallback server. This feature depends on special hardware and operating system support. For more information, see Microsoft SQL Server What's New in SQL Server 6.5.
ODBC applications can take advantage of the SQL Server fallback feature by setting a driver-specific option before connecting:
SQLSetConnectOption(hdbc,
SQL_COPT_SS_FALLBACK_CONNECT,
SQL_FB_ON);
Then, when the driver connects to the primary server, it retrieves from the primary server all the information it needs to connect to the fallback server and stores the information in the client's registry. If the application loses its connection to the primary server, it should clean up its current transaction and attempt to reconnect to the primary server. If the ODBC driver cannot reconnect to the primary server, it uses the registry information to attempt connecting to the fallback (secondary) server.
The way SQL Server presents information from the Transact-SQL SET, DBCC, PRINT, and RAISERROR statements was originally designed for the architecture of DB-Library applications. DB-Library applications have separate callback functions for handling messages and errors. These separate callback functions are difficult to code in multithreaded applications, so the designers of ODBC chose the method of having the application call a SQLError function to receive error messages. This means the SQL Server ODBC driver maps errors and messages originally returned by the DB-Library callback functions to the ODBC model.
Note that the SQL Server ODBC drivers that ship with SQL Server 6.0 and 6.5 do not return the severity-level or state codes associated with messages from SQL Server.
The Transact-SQL SET statement options SHOWPLAN, STATISTICS TIME, and STATISTICS IO can be used to get information that aids in diagnosing long-running queries. An ODBC application can set these options by executing the following statements:
SQLExecDirect(hstmt, "SET STATISTICS IO ON", SQL_NTS);
SQLExecDirect(hstmt, "SET STATISTICS TIME ON", SQL_NTS);
SQLExecDirect(hstmt, "SET SHOWPLAN ON", SQL_NTS);
When SET STATISTICS TIME or SET SHOWPLAN are ON, SQLExecute and SQLExecDirect return SQL_SUCCESS_WITH_INFO, and, at that point, the application can retrieve the SHOWPLAN or STATISTICS TIME output by calling SQLError until it returns SQL_NO_DATA_FOUND. Each line of SHOWPLAN data comes back in the format:
szSqlState="01000", *pfNativeError=6223,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Table Scan"
Each line of STATISTICS TIME comes back in the format:
szSqlState="01000", *pfNativeError= 3613,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
SQL Server Parse and Compile Time: cpu time = 0 ms."
The output of SET STATISTICS IO is not available until the end of a result set. To get STATISTICS IO output, the application calls SQLError at the time SQLFetch or SQLExtendedFetch returns SQL_NO_DATA_FOUND. The output of STATISTICS IO comes back in the format:
szSqlState="01000", *pfNativeError= 3615,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Table: testshow scan count 1, logical reads: 1,
physical reads: 0."
DBCC statements return data to an ODBC application in two ways:
An application can turn on various trace flags using the DBCC statement. No data is returned by the DBCC statement that turns on the trace flag, but the trace data is returned on subsequent SQL statements.
For example, if the application sets on a 3604 trace flag along with another flag or flags that return output, subsequent calls to SQLExecDirect or SQLExecute return SQL_SUCCESS_WITH_INFO, and the application can retrieve the trace flag output by calling SQLError until it returns SQL_NO_DATA_FOUND:
SQLExecDirect(hstmt, "dbcc traceon(3604, 4032)", SQL_NTS);
For example, after the above SQLExecDirect completes, the following subsequent call to SQLExecDirect returns SQL_SUCCESS_WITH_INFO:
SQLExecDirect(hstmt, "select * from authors", SQL_NTS);
Calling SQLError returns:
szSqlState = "01000", *pfNativeError = 0,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
96/02/02 11:08:45.26 10 LangExec: 'select * from authors"
All other DBCC statements return data when they are executed. SQLExecDirect or SQLExecute returns SQL_SUCCESS_WITH_INFO, and the application retrieves the output by calling SQLError until it returns SQL_NO_DATA_FOUND.
For example, the following statement returns
SQL_SUCCESS_WITH_INFO:
SQLExecDirect(hstmt, "dbcc checktable(authors)", SQL_NTS);
Calls to SQLError return:
szSqlState = "01000", *pfNativeError = 2536,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Checking authors"
szSqlState = "01000", *pfNativeError = 2579,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
The total number of data pages in this table is 1."
szSqlState = "01000", *pfNativeError = 7929,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Table has 23 data rows."
szSqlState = "01000", *pfNativeError = 2528
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
DBCC execution completed. If DBCC printed error messages,
see your System Administrator."
Transact-SQL PRINT and RAISERROR statements also return data through calling SQLError. PRINT statements cause the SQL statement execution to return SQL_SUCCESS_WITH_INFO, and a subsequent call to SQLError returns a SQLState of 01000. A RAISERROR with a severity of ten or lower behaves the same as PRINT. A RAISERROR with a severity of 11 or higher causes the execute to return SQL_ERROR, and a subsequent call to SQLError returns SQLState 37000. For example, the following statement returns SQL_SUCCESS_WITH_INFO:
SQLExecDirect (hstmt, "PRINT 'Some message' ", SQL_NTS);
Calling SQLError then reports:
szSQLState = "01000", *pfNative Error = 0,
szErrorMsg= "[Microsoft] [ODBC SQL Server Driver][SQL Server]
Some message"
The following statement returns SQL_SUCCESS_WITH_INFO:
SQLExecDirect (hstmt, "RAISERROR ('Sample error 1.', 10, -1)", SQL_NTS)
Calling SQLError then reports:
szSQLState = "01000", *pfNative Error = 50000,
szErrorMsg= "[Microsoft] [ODBC SQL Server Driver][SQL Server]
Sample error 1."
The following statement returns SQL_ERROR:
SQLExecDirect (hstmt, "RAISERROR ('Sample error 2.', 11, -1)", SQL_NTS)
Calling SQLError then reports:
szSQLState = "37000", *pfNative Error = 50000,
szErrorMsg= "[Microsoft] [ODBC SQL Server Driver][SQL Server]
Sample error 2."
The timing of calling SQLError is critical when output from PRINT or RAISERROR statements are included in a result set. The call to SQLError to retrieve the PRINT or RAISERROR output must be made immediately after the statement that receives SQL_ERROR or SQL_SUCCESS_WITH_INFO. This is straightforward when only a single SQL statement is executed, as in the examples above. In these cases, the call to SQLExecDirect or SQLExecute returns SQL_ERROR or SQL_SUCCESS_WITH_INFO and SQLError can then be called. It is less straightforward when coding loops to handle the output of a batch of SQL statements or when executing SQL Server stored procedures.
In this case, SQL Server returns a result set for every SELECT statement executed in a batch or stored procedure. If the batch or procedure contains PRINT or RAISERROR statements, the output for these is interleaved with the SELECT statement result sets. If the first statement in the batch or procedure is a PRINT or RAISERROR, the SQLExecute or SQLExecDirect returns SQL_SUCCESS_WITH_INFO or SQL_ERROR and the application needs to call SQLError until it returns SQL_NO_DATA_FOUND to retrieve the PRINT or RAISERROR information.
If the PRINT or RAISERROR statement comes after other SQL statements (such as a SELECT), then the PRINT or RAISERROR information is returned when SQLFetch or SQLExtendedFetch is called for the result set, before the PRINT or RAISERROR returns SQL_NO_DATA_FOUND or SQL_ERROR.
For example, in the following procedure, the SQLExecute or SQLExecDirect call returns SQL_SUCCESS_WITH_INFO and a call to SQLError at that point returns the first print message. If the ODBC application then processes through the result set using SQLFetch, the application can get the second print statement by calling SQLError when SQLFetch returns SQL_NO_DATA_FOUND:
CREATE PROCEDURE odbcproc AS
PRINT 'First PRINT Message.'
SELECT name FROM sysusers WHERE suid < 2
PRINT 'Second PRINT Message.'
GO