Oracle: ADODB.Connection.Execute() implicit COMMIT
John Grandy
i have read in Nick Snowdon's VB+Oracle book that ADODB.Connection.Execute()
will implicitly always add a "COMMIT"
however, he does not specifically state if this functionality is in the ADO
layer or in the OLEDB Provider layer; therefore, perhaps this behaviour is
different for different vendors' OLEDB Providers for Oracle
specifically, i am using MSDAORA , the Microsoft OLEDB Provider For Oracle
shipping with MDAC 2.5 RTM (2.50.4403.12)
Carlos J. Quintero
Oracle has two modes: "autocommit" mode and "transaction" mode. While
SQL*Plus works in transaction mode by default, so you have to execute a
"COMMIT" command afters each INSERT, DELETE or UPDATE command to allow other
connections to see your changes, ODBC drivers and OLEDB Providers work in
"autocommit" mode by default, since it is the natural way in applications:
when you want a transaction, you explicitly ask for it, using the BeginTrans
method of the Connection object.
The "autocommit" functionality is provided by the Oracle Call Interface
(OCI) layer and ODBC drivers or OLEDB Providers use and expose it. With
MSDAORA, internally, just after connecting, the provider calls the ocon
function of the Oracle Call Interface (OCI) API, which enables "autocommit".
All your SQL commands are executed in this mode until you ask for a
transaction; then, the provider calls the ocof OCI function to disable
autocommit, so starting a transaction. When you call CommitTrans, the
provider calls the ocom OCI function to commit the transaction (or orol OCI
function to rollback if you called RollbackTrans) and then calls the ocon
OCI function to enable "autocommit" again.
Hope this helps
--
Best regards,
Carlos J. Quintero
Freeware "all-in-one" Add-In for VB6: TabIndex Assistant, Procedure Callers,
Find in All Projects, Close Windows, Review TabIndex, Review Collections,
Add Property or Function, Add Error Handler, Add Header Comment, Clear
Immediate Window, Project Statistics:
www.mztools.com
John Grandy <JohnA...@csi.com> escribió en el mensaje de noticias
OKobSYa...@cppssbbsa02.microsoft.com...
John Grandy
However, but how do
rs.AddNew
rs.Update
rs.UpdateBatch
behave in this context ? (Oracle)
con.BeginTrans sets auto commits = off, so it should be the case that
rs.Update will normally trigger an implicit commit, but if inside a
con.BeginTrans then it will not commit unless/until con.CommitTrans is
called
but then how does rs.UpdateBatch work ? it would seem that it would have to
be within a con.BeginTrans to be effective, but then it must trigger an
implicit commit only for the rs.Update and other DML issued from the
specific rs, rather than an umbrella commit for all DML issued through the
con?
Carlos J. Quintero wrote in message ...
Carlos J. Quintero
Sorry, but I don´t use those methods of recordsets so I don´t know what they
do behind the scenes (that is one of the reasons to not use them).
--
Best regards,
Carlos J. Quintero
Freeware "all-in-one" Add-In for VB6: TabIndex Assistant, Procedure Callers,
Find in All Projects, Close Windows, Review TabIndex, Review Collections,
Add Property or Function, Add Error Handler, Add Header Comment, Clear
Immediate Window, Project Statistics:
www.mztools.com
John Grandy <JohnA...@csi.com> escribió en el mensaje de noticias
utyBAtl...@cppssbbsa02.microsoft.com...