From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | "Jack C(dot) Wei" <jackcwei+pgsql(at)gmail(dot)com>, pgsql-odbc(at)postgresql(dot)org |
Subject: | Re: ODBC connections go dead after a failed query? |
Date: | 2017-03-03 20:27:01 |
Message-ID: | d7512064-ba67-9c5b-6960-116aeb0a92b9@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
On 03/03/2017 12:14 PM, 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.
I am betting you dealing with something like this:
test=> begin ;
BEGIN
test=> select 1/0;
ERROR: division by zero
test=> select 1;
ERROR: current transaction is aborted, commands ignored until end of
transaction block
test=> rollback ;
ROLLBACK
test=> select 1;
?column?
----------
1
(1 row)
Basically an error in a transaction stops everything until you rollback.
Here:
https://odbc.postgresql.org/docs/config.html
there is :
Level of rollback on errors: Specifies what to rollback should an error
occur.
Nop(0): Don't rollback anything and let the application handle the
error.
Transaction(1): Rollback the entire transaction.
Statement(2): Rollback the statement.
Notes in a setup: This specification is set up with a PROTOCOL
option parameter.
PROTOCOL=[6.2|6.3|6.4|7.4][-(0|1|2)]
default value is a sentence unit (it is a transaction unit before 8.0).
I have never used that, so I am not sure exactly how to invoke it and
what the consequences are.
>
>
> Thank you,
> Jack Wei
>
>
>
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Jack C. Wei | 2017-03-03 21:53:15 | Re: ODBC connections go dead after a failed query? |
Previous Message | Jack C. Wei | 2017-03-03 20:14:43 | ODBC connections go dead after a failed query? |