When to Process Results

It is critical that all result sets are processed completely in a timely manner. The result sets need to be processed to avoid problems with subsequent SQL Server queries and to avoid concurrency issues with SQL Server resources.

In most cases, the return code from dbsqlok or dbsqlexec should be ignored. If you send the following batch and the INSERT statement fails due to a duplicate key, a severity 14 error is generated; however, the batch continues.

INSERT INTO tblTest VALUES(1)
SELECT @@VERSION

The dbsqlok and dbsqlexec calls check only the success of the first command. If you do not call dbresults, you will not process the SELECT statement results and can get result pending errors.

The following are the most common problems your application may encounter if result sets are not handled immediately and completely:

• If all result sets are not processed completely and you attempt to send another query to SQL Server using the same connection, you will receive DB-Library error 10038:

  Attempt to initiate a new SQL Server operation with results pending.
  

  DB-Library prevents you from sending additional queries if there are results from a previous query that need to be handled. For more information, see When and How to Use dbcancel().

• If a query is issued to SQL Server and the results are not handled immediately, you may be holding locks and reducing concurrency on your server.

  For example, you issue a query that requires rows from two pages to populate your result set. SQL Server parses, compiles, and runs the query. This means that shared locks are placed on the two pages that contain the rows needed to satisfy your query. Further, not all rows fit onto one SQL Server Tabular Data Stream (TDS) packet. TDS packets are filled and sent to the client. If all rows from the first page fit on the TDS packet, SQL Server releases the shared lock on that page, but leaves a shared lock on the second page. SQL Server then waits for the client to request more data (this is done using dbnextrow/dbresults, SQLNextRow/SQLResults, FetchLast/FetchFirst, and so on).

  This means that the shared lock is held until the client requests the rest of the data. Other processes requesting data from the second page may be blocked. For more information, see Troubleshooting Locking.

  Following is an example of how to handle all result sets correctly.

  BOOL bMoreResults = TRUE;
  BOOL bMoreRows = TRUE;
  RETCODE dbRC = SUCCEED;
  
  //
  // Send query
  
  .
  .
  .
  
  //
  // Process *all* results
  
  bMoreResults = TRUE
  
  while(bMoreResults)
     {
        switch(dbRC = dbresults(pdbproc))
        {
        case SUCCEED:
  
          bMoreRows = TRUE;
  
          while(bMoreRows)
          {
            switch(dbRC = dbnextrow(pdbproc))
            {
              case REG_ROW:
                // Handle regular row
                break;
  
              case NO_MORE_ROWS:
                bMoreRows = FALSE;  
                // All rows in this result set handled
                break;
  
              case BUF_FULL:
                // Handle full buffer when using row buffering
                break;
  
              case FAIL:
                // Any error processing desired
                bMoreRows = FALSE;
                break;
  
              default:
                // Handle compute row
                break;
            }
          }
  
          break;
  
        case NO_MORE_RESULTS:
          bMoreResults = FALSE;  // All result sets handled
          break;
  
        case FAIL:
          // Any error processing desired
          // The current command has returned an error
          // Could be a nonfatal error
          bMoreResults = TRUE;
          break;
  
        case NO_MORE_RPC_RESULTS:
          // Extract stored procedure return information
          break;
  
        default:
          bMoreResults = FALSE;  // unknown
          break;
        }
     } // while(bMoreResults && FALSE == DBDEAD(pdbproc))