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