Re: 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(at)dream(dot)email(dot)ne(dot)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
>>
>>
>