Re: [ODBC] ODBC connections go dead after a failed query?

Hi Hiroshi,

I just tried the test driver 9.6.0102 and it seems to have fixed the bug. Thank you for the good work.

When can we expect an official release of the new driver? We're looking to deploy PostgreSQL in six weeks. Currently I'm using 9.3.0400 which is the last official version that does not have this issue. 9.5.0100 starts having this problem. So is 9.5.0400 and 9.6.0100 that I tried.

Regards,

Jack

On Sat, Mar 4, 2017 at 5:48 AM, Inoue, Hiroshi <h-inoue@dream.email.ne.jp> wrote:

Hi Jack,

Probably I found the cause.
Could you try the test drivers 9.6.0102 at
http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/
?

regards,
Hiroshi Inoue

On 2017/03/04 5:14, Jack C. Wei wrote:

Hi,

We have some legacy SQL queries that works on SQL Server but not on PostgreSQL due to SQL Server-specific syntax, which we are working to migrate. The problem is sometimes psqlodbc just cannot seem to reset itself after a bad query and simply go dead. All subsequent queries on the same connection, legitimate or not, would fail.

I've verified this can happen with the latest PostgreSQL 9.6.2 and psqlodbc 09_06_0100, on a Windows 7 x64 host.

The offending SQL query is something like "UPDATE site SET image=image_old;" where [image] is BYTEA (used to be MEDIUMBLOB on SQL Server) and [image_old] is TEXT. PostgreSQL does not allow this, which is understandable. But after this query the connection goes dead, failing all subsequent queries with ODBC error 08S01 or HY000. While it is possible to detect this and reconnect as a workaround, it would be cumbersome and lead to unwarranted overhead. See the following excerpt from my ODBC trace log:


PsqlOdbcBug     f68-e10    EXIT  SQLPrepareW  with return code 0 (SQL_SUCCESS)
        HSTMT               0x00033EE8
        WCHAR *             0x010F0F28 [      -3] "UPDATE site SET image=image_old

;\ 0"
        SDWORD                    -3

PsqlOdbcBug     f68-e10    ENTER SQLExecute
        HSTMT               0x00033EE8

PsqlOdbcBug     f68-e10    EXIT  SQLExecute  with return code -1 (SQL_ERROR)
        HSTMT               0x00033EE8

        DIAG [42804] ERROR: column "image" is of type bytea but expression is of type text;
Error while preparing parameters (1)

PsqlOdbcBug     f68-e10    ENTER SQLFreeHandle
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x00033EE8

PsqlOdbcBug     f68-e10    EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x00033EE8

PsqlOdbcBug     f68-e10    ENTER SQLAllocHandle
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x000338D8
        SQLHANDLE *         0x0035EB90

PsqlOdbcBug     f68-e10    EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x000338D8
        SQLHANDLE *         0x0035EB90 ( 0x00D51C98)

PsqlOdbcBug     f68-e10    ENTER SQLPrepareW
        HSTMT               0x00D51C98
        WCHAR *             0x010F0DE0 [      -3] "SELECT id FROM site;\ 0"
        SDWORD                    -3

PsqlOdbcBug     f68-e10    EXIT  SQLPrepareW  with return code 0 (SQL_SUCCESS)
        HSTMT               0x00D51C98
        WCHAR *             0x010F0DE0 [      -3] "SELECT id FROM site;\ 0"
        SDWORD                    -3

PsqlOdbcBug     f68-e10    ENTER SQLExecute
        HSTMT               0x00D51C98

PsqlOdbcBug     f68-e10    EXIT  SQLExecute  with return code -1 (SQL_ERROR)
        HSTMT               0x00D51C98

        DIAG [08S01] Could not send Query(connection dead);
Could not send Query(connection dead) (26)


I've attached a minimal working sample code (we develop in Visual Studio 2013) and also the ODBC trace log. Hopefully they make it through. Any feedback is welcome.


Thank you,
Jack Wei