Thread: Bug in the SQLGetDiagRec function of psqlodbc

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,

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 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;

}

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

On 09/18/2015 08:13 AM, PSequel Support wrote:

Thanks for your reply! Actually I've tried calling SQLFreeStmt after each
query, but it doesn't seem to change anything. See the example code below.

#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);
   }}
...

Oh, you're getting the diagnostic from the *connection* handle, not the statement handle. According to the documentation of SQLExecDirect(), it sets the diagnostics on the statement handle.

I agree that psqlODBC's behaviour here is pretty strange, and probably incorrect. I wouldn't expect SQLExecDirect to set any diagnostics on the connection handle, at least for a syntax error. But if it does, the second call probably should do so as well, and overwrite the previous error message.

But the immediate fix for your application would be to get the diagnostics from the statement handle instead of the connection handle.

- Heikki