BUG: dBASE: SQL_ERROR w/ SQLExecute/SQLFreeStmt(SQL_CLOSE)

SYMPTOMS

Calling SQLExecute, SQLFetch, and SQLFreeStmt/SQL_CLOSE in a loop on a prepared statement can cause SQLExecute to return SQL_ERROR after several iterations. The exact number of iterations before this error occurs depends on the number of columns in the table. Users of MFC Database classes note that CRecordSet::Requery followed by CRecordSet::MoveNext in a loop can cause the same behavior.

To produce the problem, do the following with the ODBC dBASE driver:

create table test (col1 char(5), col2 char(5))
   SQLPrepare: select * from test order by col2
   Loop
     SQLExecute
     SQLFetch
     SQLFreeStmt/SQL_CLOSE
   End Loop
				

At the 925th iteration of the above loop, SQLExecute will fail with SQL_ERROR.

Calling SQLError will return SQL_NO_DATA_FOUND. The number of iterations before failure is dependent on the number of columns in the table. Here is some data that will provide a rough idea of this dependency:

   Table with                 Iterations to SQL_ERROR
-----------------------------------------------------
    2 columns,    1 row                925
    2 columns, 1000 rows               925
    6 columns, 1000 rows               919
   10 columns, 1000 rows               599
   15 columns, 1000 rows               476
   17 columns, 1000 rows               399
   19 columns, 1000 rows               393
				

MFC Database class users will see the same behavior with the following loop, after the same number of iterations as above:

   CRecordSet::Open, szSQLStr = select * from test order by col2
   Begin Loop
      CRecordSet::Requery
      CRecordSet::MoveNext
   End Loop