ODBC SQLFreeStmt leave open cursor "select null from dual" on Oracle 9

Hi all,

We have an application server running against Oracle 8i, 9i and 10g databases on Windows using ODBC. With Oracle 8i it runs without issues, but with Oracle 9i and 10g we have seen a strange behavior with regard to open cursors: there a lot of open cursors for the statement "select null from dual" can be seen.
Please look at my previous thread for details: http://groups.google.com/groups?hl=de&lr=&threadm=91b29f39.0504260723.486876d%40posting.google.com&rnum=5&prev=/groups%3Fq%3Dmuchinger%40web.de%26hl%3Dde or search web for "select null from dual"

Now I have dug a little deeper into this. I debugged the component issuing the SQL statements via ODBC. I did a step-by-step debugging watching the open cursors on the connection, using the following SQL statements:
select count(*) from v$open_cursor where sid = <session id> and sql_text = "SELECT NULL FROM DUAL"
and
select count(*) from v$open_cursor where sid = <session id> and sql_text <> "SELECT NULL FROM DUAL"

The interesting thing happens when in the C++ code I come to the point where we free the statement that we allocated earlier. I stopped with the debugger at

      SQLFreeStmt(tmp_hstmt, SQL_DROP);
and looked at the open cursors before executing it. I had e. g. 10 open cursors with "SELECT NULL FROM DUAL" and 7 others. Then in the debugger I stepped over the statement and again looked at the open cursors and now had 11 open cursors with "SELECT NULL FROM DUAL" and 6 others.

So to me this looks like I am destroying the statement and closing the cursor, but somehow on Oracle the cursor is not completely freed, but remains as kind of a zombie and continues allocating resources, because after a while the system would run out of open cursors.

Ok, somebody might tell me that SQLFreeStmt with option SQL_DROP is deprecated in ODBC 3, but
1. my researches showed me that the ODBC driver manager should map the call correctly to SQLFreeHandle
2. I tried with SQLAllocHandle and SQLFreeHandle as well and saw the same effect
3. the code works fine on an Oracle 8i db with an 8.1.7 ODBC driver which is already ODBC 3

Any input is highly welcome. Thanks in advance, Michael Received on Tue May 10 2005 - 01:07:56 CDT