Обсуждение: Crash when using 'Use Declare/Fetch' and the result set does contain one row only

Hello,

Recently we switched from using plain SQL for transaction handling to the corresponding ODBC functions. Now the
applicationcrashes if 'Use Declare/Fetch' is checked in the ODBC DSN. Not everytime but when the result set does
containone row only. The course of action leading to the crash is: 

1. begin transaction:

SQLSetConnectAttr(Connection, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, SQL_IS_UINTEGER)

2. declare a cursor for a select that does yield one row/value only:

SQLAllocHandle(SQL_HANDLE_STMT, ...)

SQLSetStmtAttr(Statement, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_STATIC, SQL_IS_UINTEGER)

SQLExecDirect(Statement, "SELECT col FROM tab WHERE <primary_key = value>")

SQLDescribeCol(...)

SQLBindCol(...)

3. commit transaction:

SQLEndTran(Connection, SQL_COMMIT)

SQLSetConnectAttr(Connection, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_ON, SQL_IS_UINTEGER)

4. move the cursor to ( or fetch ) the first result row:

SQLExtendedFetch(Statement, SQL_FETCH_FIRST, ...)

There's no crash if step 4 happens before step 3 or when using SQL commands for transaction handling.

As I understand cursors have to be declared 'with hold' to be used outside the transaction they've been declared in.
However,it seems that there's no problem if the result set does contain more than one row. This yields the first result
rowand subsequent SQLExtendedFetch(...) calls work. Alternatively using plain SQL for transaction handling works, too. 

At least psqlODBC 09.03.0210 and 09.03.0300 ( both 32 bit ) seem to be affected - tested against PostgreSQL 9.2.8 64
bit( Ubuntu and Windows ). 

Further tests using the psqlodbc 09.03.0300 unicode version compiled from source and MDAC 2.80 using 'ODBCTest
(Unicode,x86)' using the same ODBC function calls showed a different behavior between debug and release version. The
debugversion simply crashes and the release version returns no data if the result set contains one row only. 

Best regards,

Peter

On 06/23/2014 07:58 PM, Jan-Peter Seifert wrote:
> Hello,
>
> Recently we switched from using plain SQL for transaction handling to the corresponding ODBC functions. Now the
applicationcrashes if 'Use Declare/Fetch' is checked in the ODBC DSN. Not everytime but when the result set does
containone row only. The course of action leading to the crash is: 
>

Any chance of a self-contained, compileable example that demonstrates this?


--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Hello,

I've attached a zip file containing a Visual Studio 2010 project tested on a 'PostgreSQL Unicode' system-DSN named
TestODBCand a dump of a test database named TestODBC. 
The line with the statement that returns one row is commented out.
The other returns 5 rows in the release version - it skips the first line and returns the last row twice though. When
usingthe debugger it crashes on the last row. 

Thank you very much in advance!

Best regards,

Peter Seifert

On 06/24/2014 12:06 PM, Jan-Peter Seifert wrote:
> I've attached a zip file containing a Visual Studio 2010 project
> tested on a 'PostgreSQL Unicode' system-DSN named TestODBC and a dump
> of a test database named TestODBC. The line with the statement that
> returns one row is commented out. The other returns 5 rows in the
> release version - it skips the first line and returns the last row
> twice though. When using the debugger it crashes on the last row.

I committed the attached patch to fix this. I admit I don't understand
very well how the cursor and cached result set row counting is supposed
to work. But I think this is correct, so I'll commit it unless someone
sees a problem with it. This patch also includes a regression test for
this. I created it using your test case as reference; it's essentially
the same test, but using the common regression test facilities. (You'll
have to set UseDeclareFetch=1 in odbc.ini for it to exhibit the bug)

Thanks for the report!

- Heikki

> Gesendet: Dienstag, 24. Juni 2014 um 15:43 Uhr
> Von: "Heikki Linnakangas" <hlinnakangas@vmware.com>
> An: "Jan-Peter Seifert" <Jan-Peter.Seifert@gmx.de>, "Michael Paquier" <michael.paquier@gmail.com>
> Cc: "Craig Ringer" <craig@2ndquadrant.com>, "PostgreSQL mailing lists" <pgsql-odbc@postgresql.org>
> Betreff: Re: [ODBC] Crash when using 'Use Declare/Fetch' and the result set does contain one row only

> I committed the attached patch to fix this.

We did some tests using a version compiled from the current Git master and it worked well for us.

Thank you very much!

Best regards,

Peter