It happens, if we are accessing more then one database in one COM+ transaction. We are relying on ODBC connection pooling, so we typically open a connection to one database, run some queries, close the connection, then open connection to another database, run some queries, etc. It used to work nicely with MTS.
Behind the scene, after ODBC method ::SQLDisconnect is called, connection to Oracle is not broken, because transaction still continues, so Oracle needs to be notified of its final outcome.
Unfortunately second call to ODBC method ::SQLConnect returns the very same open connection, that was used before, even if DSN, User name and password are different. That is clearly a bug.
If first connection is kept open during transaction, then everything works correctly. Also everything works correctly, if databases are accessed from different COM+ applications, because they don’t share connection pool.
I’ve come up with small test program, that reproduces the bug.
I also enclosed source code for the COM+ component, and MTS package, if anyone wants to try it on NT (I’ve done that).
I don’t know, if it happens with any database, other then Oracle, but I’ve tried different MDAC versions, and they seem to behave consistently.
There seem to be no good work around. We’ve implemented fair workaround, which uses SQLGetInfo function right after SQLConnect, to verify, that database user is indeed the same, as program has asked for. Obviously it means performance hit, so any other clever ideas are very welcome.
INSTALLATION:
1. Install enclosed odbc_test_app_win2k.MSI package, that contains ODBC_TEST_APP COM+ library application, that requires transactions.
2. Modify enclosed odbc_test.js script to specify your connection parameters and sql statement.
3. Run odbc_test.js and check, if it encounters problems in any tests