Re: Bug in the SQLGetDiagRec function of psqlodbc

On 08/18/2015 07:19 AM, PSequel Support wrote:

There seems to be a bug in the SQLGetDiagRec function - it always returns
the oldest error message. For example, after I run two invalid queries,
calling SQLGetDiagRec gives me the error message for the first query
instead of the second one. However, according to the specs
<https://msdn.microsoft.com/en-us/library/ms716256(v=vs.85).aspx>,

*SQLGetDiagRec* retrieves only the diagnostic information most recently

associated with the handle specified in the *Handle* argument. If the
application calls another ODBC function, except *SQLGetDiagRec*,
*SQLGetDiagField*, or *SQLError*, any diagnostic information from the
previous calls on the same handle is lost.

My psqlodbcw.so file was built from the HEAD of
http://git.postgresql.org/git/psqlodbc.git using Homebrew
<https://github.com/Homebrew/homebrew/blob/master/Library/Formula/psqlodbc.rb#L17>

on
Mac OS X. Here is the code for demonstrating this issue:

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

void printError(SQLHANDLE handle) {
   char message[256];
   SQLSMALLINT i = 0;
   while (SQLGetDiagRec(SQL_HANDLE_DBC, handle, ++i, NULL, NULL,
(SQLCHAR*)message, sizeof(message), NULL) != SQL_NO_DATA) {
     printf("%s\n", message);
   }
}

BOOL runQuery(SQLHSTMT stmt, char* query) {
   SQLRETURN ret = SQLExecDirect(stmt, (SQLCHAR*)query, SQL_NTS);
   printf("Query: %s\n", query);
   return SQL_SUCCEEDED(ret);
}

int main(int argc, const char * argv[]) {
   SQLHENV env;
   SQLHDBC dbc;
   SQLHSTMT stmt;

   SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
   SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
   SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

   char* connStr =
"Driver=/usr/local/Cellar/psqlodbc/HEAD/lib/psqlodbcw.so;Server=localhost;Database=postgres";
   if (SQLDriverConnect(dbc, NULL, (SQLCHAR*)connStr, SQL_NTS, NULL, 0,
NULL, SQL_DRIVER_NOPROMPT) != SQL_SUCCESS) {
     printf("Unable to connect to database\n");
     return 1;
   }

   SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
   for (int i = 0; i < 3; ++i) {
     char query[50];
     sprintf(query, "SELECT * FROM t%d", i);
     if (!runQuery(stmt, query)) {
       printError(dbc); // this always prints 'ERROR: relation "t0" does not
exist' - that's wrong!
     }
   }

   SQLDisconnect(dbc);
   SQLFreeHandle(SQL_HANDLE_STMT, stmt);
   SQLFreeHandle(SQL_HANDLE_DBC, dbc);
   SQLFreeHandle(SQL_HANDLE_ENV, env);
   return 0;
}

Hmm. You're not calling SQLFreeStmt(SQL_CLOSE) after the failed query, so the second SQLExecDirect() is returning SQL_INVALID_HANDLE. According to the ODBC docs, SQLExecDirect() sets the diagnostics if it returns SQL_ERROR.

I'm not 100% sure that's correct behaviour - perhaps it should return SQL_ERROR, with an error message complaining that the statement is busy. Given that no-one's complained about that before, I'm guessing that it's not supposed to execute the query, anyway, i.e. this an application error.

- Heikki