ODBC connections go dead after a failed query?

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