Re: Bug in the SQLGetDiagRec function of psqlodbc

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

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

for (int i = 0; i < 3; ++i) {
SQLHSTMT stmt;
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
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!
}
SQLFreeStmt(stmt, SQL_CLOSE);
}

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

On Wed, Sep 16, 2015 at 5:36 AM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:

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