Re: ODBC connections go dead after a failed query?

Adrian, thanks for the reply.

Unfortunately I don't think it's about transactions. We do not use
transactions in our queries, which means neither BEGIN/ROLLBACK/COMMIT nor
ODBC's SQLEndTran().

I also tried the different rollback levels you mentioned by using
"PROTOCOL=7.4-0" or -1 or -2. None of them make a difference.

On Fri, Mar 3, 2017 at 12:27 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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
>