ADO,ODBC and transaction

Hi,

I am using following programming enviroment:
[*] ODBC Driver Merant 3.6 for Oracle
[*] Microsoft OLEDB Driver for ODBC
[*] ADO
[*] VB6.0
[*] UDL-File to connect via ADO.Connection to Oracle 8.1.7

I know that I could use directly an "OLEDB Provider for Oracle" but the problem is that our own libraries are based on the ODBC Merant Drivers.

The problem is that the Merant Driver or "MS OLEDB" Driver seems not to understand all transaction isolation levels although the MSDN or the DataDirect Reference tells the opposite.

I want to use the transaction isolation level "serializable" for multi-user enviroment beacause the normal read-commit transaction would see changes from other users.
Although I am setting this transaction level I get the wrong data !!

I am starting the transaction with following code:

Dim cnn As new ADO.Connection
cnn.Open "FileName=c:\x.udl"

g_DBOra.IsolationLevel = adXactIsolated ' or adXactSerializable g_DBOra.BeginTrans

Then I am inserting a recordset by the command.Execute methode or by the recordset.AddNew methode.

Before the programm commits the data, I set a breakpoint.

In a second client (for example SQL*Plus or TOAD) I insert a second recordset in the same table and commit the data and notice the ID of the recordset. (The table has a trigger-field)

In the first client I read the ID of the first client by the code "SELECT Max(ID) FROM Table"
but I get the ID from the second client which is wrong.

Who has experience or an idea what is wrong. I want to use the ID of the table as reference to a second table.

Thanx a lot. Received on Mon Jul 22 2002 - 09:22:19 CDT