Hello,
the function DBMoreResults() from sql toolkit seems to be buggy as hell.
1: It does NOT return a valid (eg. DB_EOF) return code if there are no more results.
Given a stored procedure like:
CREATE PROCEDURE `test01`()
BEGIN
SELECT 1 FROM DUAL;
SELECT 2 FROM DUAL;
END;
and a (pseudo) code routine like
if((hndDB = DBConnect (strDBConnect)) > 0)
{
if((hndStmt = DBNewSQLStatement (hndDB, "test01()")) > 0)
{
iDBRetCode = DBSetStatementAttribute (hndStmt,
ATTR_DB_STMT_CURSOR_TYPE, DB_CURSOR_TYPE_FORWARD_ONLY);
if(iDBRetCode == DB_SUCCESS) iDBRetCode =
DBSetStatementAttribute (hndStmt, ATTR_DB_STMT_LOCK_TYPE,
DB_LOCK_READ_ONLY);
if(iDBRetCode == DB_SUCCESS) iDBRetCode = DBSetStatementAttribute (hndStmt,
ATTR_DB_STMT_COMMAND_TYPE, DB_COMMAND_STORED_PROC);
if(iDBRetCode == DB_SUCCESS) iDBRetCode =
DBOpenSQLStatement (hndStmt);
do {
if( iDBRetCode != DB_SUCCESS ) break;
// bind those
columns
if(iDBRetCode == DB_SUCCESS)
{
// BIND VARIABLES TO THE RESULT SETS ('1',
'2')!!!!!!!!!!!!!!!
}
while(( iDBRetCode = DBFetchNext (hndStmt)) ==
DB_SUCCESS)
{
// DO SOMETHING WITH THE RESULT
} // end while
dbFetch
}
if(
iDBRetCode == DB_EOF ) iDBRetCode = DB_SUCCESS; // DB_EOF is NOT AN
ERROR!!!
// NOTE THE FOLLOWING LINES
} while((
iDBRetCode = DBMoreResults (hndStmt)) == DB_SUCCESS );
// Workaround for
DBMoreResults - bug
if(
iDBRetCode == -10 || iDBRetCode == -11 )
{
iDBRetCode = 0;
}
// END NOTE
DBCloseSQLStatement(hndStmt);
DBDiscardSQLStatement(hndStmt);
} else
{
iDBRetCode =
hndStmt;
}
}
DBDisconnect(hndDB);
you will see that the do - while loop will be executed 3 times!!! that's once too often since there are only two result sets generated by the stored procedure.
Even worse is that the DBMoreResults() function returns DB_SUCCESS all the time until the 3 loop, then the return value is -10 or -11 (system error!!) which
can't be distinguished from a real sytem error any more.
2. After working with the above workaround everything went fine til the returned resultsets grew in size and now the DBMoreResults() - beast returns an ODBC
error which may be true, or not.
All this was tested with CVI 8.5.1 and the latest SQL - toolkit, MYSQL 5.1 and MyODBC 5xxx (latest version, supporting multible result sets)
thx for help
