• Article

Processing Results (ODBC)

After an application submits a SQL statement, SQL Server returns any resulting data as one or more result sets. A result set is a set of rows and columns that match the criteria of the query. SELECT statements, catalog functions, and some stored procedures produce a result set made available to an application in tabular form. If the executed SQL statement is a stored procedure, a batch containing multiple commands, or a SELECT statement containing keywords, such as COMPUTE or COMPUTE BY, there will be multiple result sets to process.

ODBC catalog functions also can retrieve data. For example, SQLColumns retrieves data about columns in the data source. These result sets can contain zero or more rows.

Other SQL statements, such as GRANT or REVOKE, do not return result sets. For these statements, the return code from SQLExecute or SQLExecDirect is usually the only indication the statement was successful.

Each INSERT, UPDATE, and DELETE statement returns a result set containing only the number of rows affected by the modification. This count is made available when application calls SQLRowCount. ODBC 3.x applications must either call SQLRowCount to retrieve the result set or SQLMoreResults to cancel it. When an application executes a batch or stored procedure containing multiple INSERT, UPDATE, or DELETE statements, the result set from each modification statement must be processed using SQLRowCount or cancelled using SQLMoreResults. These counts can be cancelled by including a SET NOCOUNT ON statement in the batch or stored procedure.

Transact-SQL includes the SET NOCOUNT statement. When the NOCOUNT option is set on, SQL Server does not return the counts of the rows affected by a statement and SQLRowCount returns 0. The SQL Server Native Client ODBC driver version introduces a driver-specific SQLGetStmtAttr option, SQL_SOPT_SS_NOCOUNT_STATUS, to report on whether the NOCOUNT option is on or off. Anytime SQLRowCount returns 0, the application should test SQL_SOPT_SS_NOCOUNT_STATUS. If SQL_NC_ON is returned, the value of 0 from SQLRowCount only indicates that SQL Server has not returned a row count. If SQL_NC_OFF is returned, it means that NOCOUNT is off and the value of 0 from SQLRowCount indicates that the statement did not affect any rows. Applications should not display the value of SQLRowCount when SQL_SOPT_SS_NOCOUNT_STATUS is SQL_NC_OFF. Large batches or stored procedures may contain multiple SET NOCOUNT statements so programmers cannot assume SQL_SOPT_SS_NOCOUNT_STATUS remains constant. The option should be tested each time SQLRowCount returns 0.

Several other Transact-SQL statements return their data in messages rather than result sets. When the SQL Server Native Client ODBC driver receives these messages, it returns SQL_SUCCESS_WITH_INFO to let the application know that informational messages are available. The application can then call SQLGetDiagRec to retrieve these messages. The Transact-SQL statements that work this way are:

  • DBCC

  • SET SHOWPLAN (available with earlier versions of SQL Server)

  • SET STATISTICS

  • PRINT

  • RAISERROR

The SQL Server Native Client ODBC driver returns SQL_ERROR on a RAISERROR with a severity of 11 or higher. If the severity of the RAISERROR is 19 or higher, the connection is also dropped.

To process the result sets from an SQL statement, the application:

  • Determines the characteristics of the result set.

  • Binds the columns to program variables.

  • Retrieves a single value, an entire row of values, or multiple rows of values.

  • Tests to see if there are more result sets, and if so, loops back to determining the characteristics of the new result set.

The process of retrieving rows from the data source and returning them to the application is called fetching.

Retrieving COMPUTE and COMPUTE BY Result Sets

The COMPUTE BY clause generates subtotals within a result set; the COMPUTE clause generates a total at the end of the result set. The SQL Server Native Client ODBC driver presents these totals and subtotals to the calling application by generating multiple result sets for each SELECT statement.

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 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 interleaves these result sets until it produces the final result set with the total for the COMPUTE SUM(price), SUM(advance) clause.