Re: ADO,ODBC and transaction

"coltnet" <gh_at_hakom.at> wrote in message news:vyU_8.2$Eh.170_at_stuart.coltnet.at...
> 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.

It is pretty well impossible to tell as you do not post your sql statements,code or the definition of the trigger.

However it looks likely to me that your best approach would be to write a stored procedure to perform your update and then call that from ADO you can store your id value in a bind variable for use in later statements in the transaction using the returning clause in this case. Issuing SET TRANSACTION LEVEL SERIALIZABLE rings all sorts of alarm bells in particular it says to me you don't really want a multi user system.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************