SQLWindows error 163 "Result sets not active"

Result sets

by Charles McLouth, Centura Global AST Support Engineer

06/13/97

This document gives an explanation of how to avoid circumstances that can cause the 163 error..

163-error "result sets not active"

00163 SQL RNA Result sets not active

Reason: Attempting to get the number of rows in a result set with the API function call sqlnrr() or attempting to set a position within a result with the API function call sqlprs() and the application program is not in Result Set Mode

Result Sets and Result Set Mode

For the purposes of this document, result set(s) refer to the rows returned from an executed SQL command or stored procedure. Two types of result sets will be mentioned, Back End Result Sets (BERS) and Front End Result Sets (FERS). It is not the intention of this document to give a technical account of result sets although BERS and FERS will be more clearly defined, later in this section.

 Result Set Mode (RSM )tells the application or database what positioning can or cannot be done within a given result set and can be set in the client application, in the database server, or both.

 If RSM is off (FALSE) and a result set is built, only the function sqlfet (fetch next row from the result set) can be used to position to the next sequential row. Subsequent sqlfet calls will position forward from the row returned by the previous sqlfet.

 In contrast, if RSM is on (TRUE) and a result set is built, not only can sqlfet be used to position to the next sequential row, but the function sqlprs(position in result set) can be used to move to any row within the given result set. Furthermore, subsequent sqlfet or sqlprs will start reading from the position returned by the previous sqlfet or sqlprs. Lastly, sqlnrr (number of rows in result set) can be used to determine the number of rows in the currently built result set.

 Back End Result Set (BERS)

The BERS is the result set created and maintained by the database. With the exception of SQLBase and MS-SQLServer, databases do not support RSM allowing only sequential positioning within the BERS with the sqlfet. Alternately SQLBase and MSSQLServer database servers support RSM, allowing non-sequential positioning within the result set via sqlprs. This functionality for these two databases is called "back end scroll-ability," having "back end scrollable cursors", or Database-side RSM.

Front End Result Set(FERS)

Since most databases do not support RSM, Centura has implemented a client-side RSM called FERS(Front End Result Sets.) This special feature allows Centura’s connectivity products to simulate the "back end scroll-ability" or Database-side RSM of SQLBase(Microsoft’s SQLServer began supporting this with version 6.0.) Centura’s implementation of this simulation is controlled by the api(sqlapiw.dll) and involves the creation of a temporary physical files on the client machine called FERS files. Each cursor that connects to the database creates two FERS files, of which one is for long data, in the Winodws’ TEMP directory.

How are FERS implemented?
    1. An application that is in RSM connects to a database that is not in RSM.
    2. The BERS is created when sqlcom and sqlexe of an SQL select statement is called.
    3. The client application calls either sqlfet or sqlprs.
    4. If the FERS file has not been created then one is created.
    5. The FERS file is checked to see if the requested row is in the file. If "yes" then the position in the FERS is the successful sqlfet or sqlprs. If "no" then a sqlfet is executed against the BERS adding each fetched row to the FERS, until the requested row has been inserted into the FERS. The position in the BERS is the last successful sqlfet.

How to turn RSM ON or OFF?

RSM is "ON," by default, in SQLWindows applications connected to non-SQLBase databases but this setting can be changed programmatically by calling SqlSetResultSet( hSql, bMode) where hSql is a connected Sql Handle and bMode is either TRUE or FALSE indicating "ON" or "OFF" respectively. Also, this can be changed Globally by changing the value of the SQLWindows System Variable SqlResultSet. Setting this to either TRUE or FALSE turns RSM "ON" or "OFF" for all Sql Handles connected following this setting change.

SQLWindows Functions

The following SQLWindows functions require RSM because they call either the api function sqlnrr or sqlprs:

SqlGetResultSetCount, SqlFetchRow, SqlFetchPrev, or SqlGetModifiedRows.

The SQLWindows function SqlSetResultSet turns RSM on or off by calling the api function sqlpfrs.

Identifying the Cause of the 163 Error

  1. One of the SQLWindows functions that require RSM to be "ON" has been called and RSM is not "ON."
  2. Calling the function SqlPLSQLCommand automatically turns RSM off. This behavior is not documented in SQLWindows and is currently logged as defect 20002. In the opinion of the author, this will be deemed as expected behavior due to the fact that this behaviour is documented on page 1-22 of "Connecting Centura Objects to Databases:" ‘Oracle—When you call SqlPLSQLCommand, Centura Builder automatically turns off Result Set Mode for the SQL handle you supply as an argument. Once you return from the call to SqlPLSQLCommand, you can turn Result Set Mode back on if you wish.’
  3. The application has run out of file handles, which is controlled by the sqlapiw.dll. A SqlWindows application can use 45 file handles and a basic application will use 11 of them, leaving 34 remaining. Each connected cursor creates two FERS files using a file handle for each. Thus, this will bring the limit to 17 cursors with RSM on. Also, some ODBC drivers create temp files themselves, reducing the number of available file handles and in turn, reducing the amount of cursors with RSM on.

Resolving the Problem

Situations 1 and 2 from above are easily resolved by executing SqlSetResultSet with a parameter of "TRUE" after the SqlConnect or after the SqlPLSQLCommand.

Situation 3 is more difficult. The restricted number of file handles is a current limitation of the sqlapiw.dll. Therefore, you must either not use RSM and code your applications accordingly, or only use RSM when it is absolutely required. Unfortunately, If you are migrating applications from a database that had RSM to a database that does not have RSM, you will be forced to change the design of your application.

The limitation mentioned in the previous paragraph has been changed with a new version of the sqlapiw.dll. This new api allows 200 file handles of which only 3 are used for I/O. Also, it only creates one FERS file, unless the result set contains a "Long" column, in which a second FERS file will be created. This would allow 98 cursors, with RSM. This version of the sqlapiw.dll is available with SQLBase 6.1.2 or 6.0.1 ptf 10.