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

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
>