Re: ODBC connections go dead after a failed query?

Hi Jack,

On 2017/03/08 7:10, Jack C. Wei wrote:
> 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.

Probably we would make a new release in 10 days.

regards,
Hiroshi Inoue

>
> Regards,
> Jack
>
>
> On Sat, Mar 4, 2017 at 5:48 AM, Inoue, Hiroshi
> <h-inoue(at)dream(dot)email(dot)ne(dot)jp <mailto: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/
> <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
>
>
>