Description:
It's easiest to describe with some pseudo-code
SQLCHAR* insert = "INSERT INTO ThreeCol VALUES (?, ?, ?)"
SQLPrepare(hStmt, insert, SQL_NTS);
SQLNumResultCols(hStmt, &i);
The SQLNumResultCols has an unexpected side-effect: An extra "blank" row is inserted into the ThreeCol tables. Even though you never bind params nor invoke SQLExecute, you still have an extra "blank" row. This extra "blank" row has default values for the columns (e.g. zero for integers, null for null-allowed columns, etc).
The above example might look a bit silly at first. After all, why would a program ever invoke SQLNumResultCols for an INSERT? The value will always be zero.
However, we are using a library to put a friendlier C++ interface on top of ODBC. Our library must call SQLNumResultCols after every SQLPrepare to handle any kind of statement. FYI, our library is similar in concept to odbc++ (http://libodbcxx.sourceforge.net).
If the INSERT does not have any bound params, the SQLNumResultCols does not insert an extra blank row (it works correctly).
FYI, I'm pretty sure that bug #4264 has the same underlying problem with SQLNumResultCols. Bug #4264 is about an extra blank row inserted using the Perl DBI with MyODBC. I'll bet that the Perl DBI for ODBC also does an SQLNumResultCols after every SQLPrepare.
We are using unixODBC v2.2.8, but that's not the problem. If we link directly to MyODBC, SQLNumResultCols still inserts the extra blank row.
How to repeat:
Create a table with three columns like
CREATE TABLE ThreeCol (colA INTEGER, colB VARCHAR(10), colC DATETIME)
Compile and run the sample program below.
Run a select on ThreeCol and you will see a blank row like the one below, even though the program never even ran SQLExecute:
+------------+-----------+--------------------+
| colA | colB | colC |
+------------+-----------+--------------------+
| 0 | NULL | 0000-00-00 00:00:00|
+------------+-----------+--------------------+
Here's the complete sample program. This should compile with any C++ compiler (we're using Sun's C++ compiler):
/*
CC -I${ODBC_HOME}/include -L${ODBC_HOME}/lib -lodbc \
-g -o myOdbcBug myOdbcBug.cc
*/
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
void CheckRC(SQLRETURN rc, char* msg, char* filename, int lineno);
#define CHECKRC(FUNCSHN,MSG) CheckRC(FUNCSHN, MSG, __FILE__, __LINE__)
SQLHENV hEnv = SQL_NULL_HENV;
SQLHDBC hDbc = SQL_NULL_HDBC;
SQLHSTMT hStmt = SQL_NULL_HSTMT;
SQLCHAR* connString;
SQLCHAR connOut[255]; /* buffer for connection output */
SQLSMALLINT szConnOut; /* num bytes returned in connOut */
short numResultCol;
//-------- main() -----------
int main(int argc, char** argv)
{
if (argc < 2 || strlen(argv[1]) == 0)
{
fprintf(stderr,"ERROR: ODBC connect string not specified!\n");
return 1;
}
connString = (SQLCHAR *)argv[1];
//---- SETUP -------------------------------------------
if (SQLAllocEnv(&hEnv) != SQL_SUCCESS)
{
fprintf(stderr, "Unable to allocate SQLHENV\n");
return(1);
}
CHECKRC( SQLAllocConnect(hEnv, &hDbc), "SQLAllocConnect()" );
CHECKRC( SQLDriverConnect(hDbc, NULL, connString, SQL_NTS, connOut,
255, &szConnOut, SQL_DRIVER_NOPROMPT),
"SQLDriverConnect()" );
//---- STATEMENTS --------------------------------------
CHECKRC( SQLAllocStmt(hDbc, &hStmt), "SQLAllocStmt()" );
SQLCHAR* sqlStmt = (SQLCHAR*) "INSERT INTO ThreeCol VALUES (?, ?, ?)";
CHECKRC( SQLPrepare(hStmt, sqlStmt, SQL_NTS), "SQLPrepare()" );
CHECKRC( SQLNumResultCols(hStmt,&numResultCol), "SQLNumResultCols()");
/****
* At this point, even though we have not actually bound any params nor
* have we even run SQLExecute(), you will see a "blank" row in the
* database!
*****/
//---- TEAR DOWN -------------------------------------------
CHECKRC( SQLFreeStmt(hStmt, SQL_DROP), "SQLFreeStmt()" );
CHECKRC( SQLDisconnect(hDbc), "SQLDisconnect()" );
CHECKRC( SQLFreeConnect(hDbc), "SQLFreeConnect()" );
CHECKRC( SQLFreeEnv(hEnv), "SQLFreeEnv()" );
return 0;
}
//---- Helper function -------------
void CheckRC(SQLRETURN rc,
char* msg,
char* filename, int lineno)
{
#define MSG_LNG 512
SQLCHAR szSqlState[MSG_LNG];
SQLINTEGER pfNativeError;
SQLCHAR szErrorMsg[MSG_LNG];
SQLSMALLINT pcbErrorMsg;
SQLRETURN ret = SQL_SUCCESS;
bool error = false;
if (rc != SQL_SUCCESS && rc != SQL_NO_DATA_FOUND)
{
if (rc != SQL_SUCCESS_WITH_INFO)
{
// An error, not a warning
fprintf(stderr, "\nERROR (%s:%d): %s\n",
filename, lineno, msg);
error = true;
}
// Find the reason for the err/warning
while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
ret = SQLError(hEnv, hDbc, hStmt,
szSqlState, &pfNativeError,
szErrorMsg, MSG_LNG,
&pcbErrorMsg);
switch (ret)
{
case SQL_SUCCESS: // This means SQLError() returned normally
if (rc == SQL_SUCCESS_WITH_INFO)
{
fprintf(stderr, "\nINFO: (%s:%d): %s\n",
filename, lineno, msg);
}
fprintf(stderr,
"*** %s\n*** ODBC Code: %s, "
"Driver Code: %ld\n\n",
szErrorMsg, szSqlState, pfNativeError);
break;
case SQL_NO_DATA_FOUND: // from SQLError()
break;
default:
fprintf(stderr,
"*** SQLError() failed with code=%d\n\n", ret);
break;
}
}
}
if (error) exit(1);
}
Description: It's easiest to describe with some pseudo-code SQLCHAR* insert = "INSERT INTO ThreeCol VALUES (?, ?, ?)" SQLPrepare(hStmt, insert, SQL_NTS); SQLNumResultCols(hStmt, &i); The SQLNumResultCols has an unexpected side-effect: An extra "blank" row is inserted into the ThreeCol tables. Even though you never bind params nor invoke SQLExecute, you still have an extra "blank" row. This extra "blank" row has default values for the columns (e.g. zero for integers, null for null-allowed columns, etc). The above example might look a bit silly at first. After all, why would a program ever invoke SQLNumResultCols for an INSERT? The value will always be zero. However, we are using a library to put a friendlier C++ interface on top of ODBC. Our library must call SQLNumResultCols after every SQLPrepare to handle any kind of statement. FYI, our library is similar in concept to odbc++ (http://libodbcxx.sourceforge.net). If the INSERT does not have any bound params, the SQLNumResultCols does not insert an extra blank row (it works correctly). FYI, I'm pretty sure that bug #4264 has the same underlying problem with SQLNumResultCols. Bug #4264 is about an extra blank row inserted using the Perl DBI with MyODBC. I'll bet that the Perl DBI for ODBC also does an SQLNumResultCols after every SQLPrepare. We are using unixODBC v2.2.8, but that's not the problem. If we link directly to MyODBC, SQLNumResultCols still inserts the extra blank row. How to repeat: Create a table with three columns like CREATE TABLE ThreeCol (colA INTEGER, colB VARCHAR(10), colC DATETIME) Compile and run the sample program below. Run a select on ThreeCol and you will see a blank row like the one below, even though the program never even ran SQLExecute: +------------+-----------+--------------------+ | colA | colB | colC | +------------+-----------+--------------------+ | 0 | NULL | 0000-00-00 00:00:00| +------------+-----------+--------------------+ Here's the complete sample program. This should compile with any C++ compiler (we're using Sun's C++ compiler): /* CC -I${ODBC_HOME}/include -L${ODBC_HOME}/lib -lodbc \ -g -o myOdbcBug myOdbcBug.cc */ #include <sql.h> #include <sqlext.h> #include <stdio.h> #include <string.h> #include <stdlib.h> void CheckRC(SQLRETURN rc, char* msg, char* filename, int lineno); #define CHECKRC(FUNCSHN,MSG) CheckRC(FUNCSHN, MSG, __FILE__, __LINE__) SQLHENV hEnv = SQL_NULL_HENV; SQLHDBC hDbc = SQL_NULL_HDBC; SQLHSTMT hStmt = SQL_NULL_HSTMT; SQLCHAR* connString; SQLCHAR connOut[255]; /* buffer for connection output */ SQLSMALLINT szConnOut; /* num bytes returned in connOut */ short numResultCol; //-------- main() ----------- int main(int argc, char** argv) { if (argc < 2 || strlen(argv[1]) == 0) { fprintf(stderr,"ERROR: ODBC connect string not specified!\n"); return 1; } connString = (SQLCHAR *)argv[1]; //---- SETUP ------------------------------------------- if (SQLAllocEnv(&hEnv) != SQL_SUCCESS) { fprintf(stderr, "Unable to allocate SQLHENV\n"); return(1); } CHECKRC( SQLAllocConnect(hEnv, &hDbc), "SQLAllocConnect()" ); CHECKRC( SQLDriverConnect(hDbc, NULL, connString, SQL_NTS, connOut, 255, &szConnOut, SQL_DRIVER_NOPROMPT), "SQLDriverConnect()" ); //---- STATEMENTS -------------------------------------- CHECKRC( SQLAllocStmt(hDbc, &hStmt), "SQLAllocStmt()" ); SQLCHAR* sqlStmt = (SQLCHAR*) "INSERT INTO ThreeCol VALUES (?, ?, ?)"; CHECKRC( SQLPrepare(hStmt, sqlStmt, SQL_NTS), "SQLPrepare()" ); CHECKRC( SQLNumResultCols(hStmt,&numResultCol), "SQLNumResultCols()"); /**** * At this point, even though we have not actually bound any params nor * have we even run SQLExecute(), you will see a "blank" row in the * database! *****/ //---- TEAR DOWN ------------------------------------------- CHECKRC( SQLFreeStmt(hStmt, SQL_DROP), "SQLFreeStmt()" ); CHECKRC( SQLDisconnect(hDbc), "SQLDisconnect()" ); CHECKRC( SQLFreeConnect(hDbc), "SQLFreeConnect()" ); CHECKRC( SQLFreeEnv(hEnv), "SQLFreeEnv()" ); return 0; } //---- Helper function ------------- void CheckRC(SQLRETURN rc, char* msg, char* filename, int lineno) { #define MSG_LNG 512 SQLCHAR szSqlState[MSG_LNG]; SQLINTEGER pfNativeError; SQLCHAR szErrorMsg[MSG_LNG]; SQLSMALLINT pcbErrorMsg; SQLRETURN ret = SQL_SUCCESS; bool error = false; if (rc != SQL_SUCCESS && rc != SQL_NO_DATA_FOUND) { if (rc != SQL_SUCCESS_WITH_INFO) { // An error, not a warning fprintf(stderr, "\nERROR (%s:%d): %s\n", filename, lineno, msg); error = true; } // Find the reason for the err/warning while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { ret = SQLError(hEnv, hDbc, hStmt, szSqlState, &pfNativeError, szErrorMsg, MSG_LNG, &pcbErrorMsg); switch (ret) { case SQL_SUCCESS: // This means SQLError() returned normally if (rc == SQL_SUCCESS_WITH_INFO) { fprintf(stderr, "\nINFO: (%s:%d): %s\n", filename, lineno, msg); } fprintf(stderr, "*** %s\n*** ODBC Code: %s, " "Driver Code: %ld\n\n", szErrorMsg, szSqlState, pfNativeError); break; case SQL_NO_DATA_FOUND: // from SQLError() break; default: fprintf(stderr, "*** SQLError() failed with code=%d\n\n", ret); break; } } } if (error) exit(1); }