Handle count leak when connecting to Oracle via ODBC on Windows

[Kong Li]

Environments:
1. WinXP SP1, MDAC 2.7 SP1 (Microsoft ODBC for Oracle is
2.573.9030.0), Oracle client 9.2.0.1.
2. Win2000 SP4, MDAC 2.7 SP1 (Microsoft ODBC for Oracle is
2.573.9030.0), Oracle client 9.2.0.1.
3. Win2000 SP4, MDAC 2.8 (Microsoft ODBC for Oracle is 2.573.1022.0),
Oracle client 9.2.0.1.

I have a simple program calling SQLDriverConnect (not OLEDB, not ADO,
but ODBC) to an Oracle server 9.2, and then disconnect. The value of
handle count increases by 2 each time I did the connect/disconnect.
This has been verified on the above 3 environments. Turning on/off
ODBC connection pooling does not change the handle count leakage.

Connection caching is not an option for me since the application
connects to a set of Oracle servers that keep changing.

Is this a known problem? I did not try Oracle's own ODBC driver.

Any pointers, ideas?

Thanks.

Kong
li****@email.com

[sybrandb]

li****@email.com (Kong Li) wrote in message news:<9e**************************@posting.google. com>...

Environments:
1. WinXP SP1, MDAC 2.7 SP1 (Microsoft ODBC for Oracle is
2.573.9030.0), Oracle client 9.2.0.1.
2. Win2000 SP4, MDAC 2.7 SP1 (Microsoft ODBC for Oracle is
2.573.9030.0), Oracle client 9.2.0.1.
3. Win2000 SP4, MDAC 2.8 (Microsoft ODBC for Oracle is 2.573.1022.0),
Oracle client 9.2.0.1.

I have a simple program calling SQLDriverConnect (not OLEDB, not ADO,
but ODBC) to an Oracle server 9.2, and then disconnect. The value of
handle count increases by 2 each time I did the connect/disconnect.
This has been verified on the above 3 environments. Turning on/off
ODBC connection pooling does not change the handle count leakage.

Connection caching is not an option for me since the application
connects to a set of Oracle servers that keep changing.

Is this a known problem? I did not try Oracle's own ODBC driver.

Any pointers, ideas?

Thanks.

Kong
li****@email.com

From the Microsoft ODBC's version number it looks like you are using a
Oracle 7 compatible ODBC driver. I don't think Oracle 9.2 supports
Oracle 7 connections any longer. You could verify the sqlnet version
being used by selecting from v$session. If it shows TNS V1-V2 instead
of TNS V1-V3 you are using an obsolete driver.

Sybrand Bakker
Senior Oracle DBA

[Billy Verreynne]

li****@email.com (Kong Li) wrote

I have a simple program calling SQLDriverConnect (not OLEDB, not ADO,
but ODBC) to an Oracle server 9.2, and then disconnect. The value of
handle count increases by 2 each time I did the connect/disconnect.
This has been verified on the above 3 environments. Turning on/off
ODBC connection pooling does not change the handle count leakage.
If it works the same way on different versions, maybe then it works as
designed. I would think that a handle counter cannot really "leak" in
the same way as allocated handles leak memory when not properly
freed...
Is this a known problem? I did not try Oracle's own ODBC driver.
Is this the right forum to ask ODBC related questions? IMO not.
Any pointers, ideas?

Try the question in a ODBC/Microsoft forum. Use a MSAccess or
SQL-Server driver to determine if this is related to Oracle ODBC
driver only. Read the ODBC API specification to determine exactly how
the handle count works. Connect your app to the ODBC driver via an
ODBC debugger (there used to be a great one in the ODBC DDK that
shipped with DevNet in the 90's).

--
Billy

[Kong Li]

Follow up to this thread,
Instead of Microsoft ODBC for Oracle, I also used Oracle ODBC driver
(version 9.02.00.54) while keeping everything else the same. There is
also handle count leak. 2 per
AllocHandle/connect/disconnect/FreeHandle, identical to the behavior
of using Microsoft ODBC for Oracle.

To be more specific, after a successful SQLDriverConnect, the handle
count increases quiet a few. After the SQLFreeHandle(SQL_HANDLE_DBC,
hdbc), the handle count drops back but is still 2 more than the handle
count value before SQLDriverConnect.

I used the same code to connect to SQL 2000 with MSSQL ODBC driver
(version 2000.81.9042.00). There is no handle count leak. This leads
me to believe the code itself is correct.

The Oracle server is 9i (9.2.0).

Here is the skelton code:

SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
while (true) {
construct connection string for a new Oracle server
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (SQL_SUCCEEDED(SQLDriverConnect(hdbc,...))) {
SQLDisconnect(hdbc);
}
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}

Any ideas or pointers?

Thanks.

Kong

vs****@onwe.co.za (Billy Verreynne) wrote in message news:<1a**************************@posting.google. com>...

li****@email.com (Kong Li) wrote

I have a simple program calling SQLDriverConnect (not OLEDB, not ADO,
but ODBC) to an Oracle server 9.2, and then disconnect. The value of
handle count increases by 2 each time I did the connect/disconnect.
This has been verified on the above 3 environments. Turning on/off
ODBC connection pooling does not change the handle count leakage.

If it works the same way on different versions, maybe then it works as
designed. I would think that a handle counter cannot really "leak" in
the same way as allocated handles leak memory when not properly
freed...

Is this a known problem? I did not try Oracle's own ODBC driver.

Is this the right forum to ask ODBC related questions? IMO not.

Any pointers, ideas?

Try the question in a ODBC/Microsoft forum. Use a MSAccess or
SQL-Server driver to determine if this is related to Oracle ODBC
driver only. Read the ODBC API specification to determine exactly how
the handle count works. Connect your app to the ODBC driver via an
ODBC debugger (there used to be a great one in the ODBC DDK that
shipped with DevNet in the 90's).

[G Dahler]

"Kong Li" <li****@email.com> a écrit dans le message de
news:9e**************************@posting.google.c om...

Follow up to this thread,
Instead of Microsoft ODBC for Oracle, I also used Oracle ODBC driver
(version 9.02.00.54) while keeping everything else the same. There is
also handle count leak. 2 per
AllocHandle/connect/disconnect/FreeHandle, identical to the behavior
of using Microsoft ODBC for Oracle.

To be more specific, after a successful SQLDriverConnect, the handle
count increases quiet a few. After the SQLFreeHandle(SQL_HANDLE_DBC,
hdbc), the handle count drops back but is still 2 more than the handle
count value before SQLDriverConnect.

I used the same code to connect to SQL 2000 with MSSQL ODBC driver
(version 2000.81.9042.00). There is no handle count leak. This leads
me to believe the code itself is correct.

The Oracle server is 9i (9.2.0).

Here is the skelton code:

SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
while (true) {
construct connection string for a new Oracle server
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (SQL_SUCCEEDED(SQLDriverConnect(hdbc,...))) {
SQLDisconnect(hdbc);
}
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}

Any ideas or pointers?

Thanks.

Kong

Does it actually break after N connections ? I've seen this occuring in the
OLD days of oracle 7.1.6 wit OCI programs that repeatedly
connected/diconnected.

Why don't you use the newest ORACLE ODBC drivers instead of Micro$oft ? You
could use the new features of the database and maybe fix the problem at the
same time.