Re: [ODBC] ODBC connections go dead after a failed query?
От | Adrian Klaver |
---|---|
Тема | Re: [ODBC] ODBC connections go dead after a failed query? |
Дата | |
Msg-id | d7512064-ba67-9c5b-6960-116aeb0a92b9@aklaver.com обсуждение исходный текст |
Ответ на | [ODBC] ODBC connections go dead after a failed query? ("Jack C. Wei" <jackcwei+pgsql@gmail.com>) |
Ответы |
Re: [ODBC] ODBC connections go dead after a failed query?
("Jack C. Wei" <jackcwei+pgsql@gmail.com>)
|
Список | 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.klaver@aklaver.com
В списке pgsql-odbc по дате отправления:
Следующее
От: "Jack C. Wei"Дата:
Сообщение: Re: [ODBC] ODBC connections go dead after a failed query?