Oracle commits vs Sybase/SQL-Server commits 
Author Message
 Oracle commits vs Sybase/SQL-Server commits

Hi,

I recently finished a system to access DBs on any platform
using middleware (with ADO). As part of the system
user info on reporting etc will be held in SQL-
Server/Sybase/Oracle database. I simply ask the DBA to
provide the connection string for ADO in an INI file. I
have tested the system on SQL-Server and Sybase but not
yet on Oracle. A DBA informed me that unlike SQL-S/Sybase
that commits automatically, Oracle does not therefore my
following code would need changing to use commit/rollback
syntax. I kept the SQL syntax pure or so I thought...

Is this true for Oracle ?
Can ADO auto-enforce the commit for me with COMMIT syntax?
The public functions of my DLL on successful completion of
the internal private functions issue an MTS setcomplete,
can this commit in some way ?

I really do not want to re-write all the private functions
of my VB DLL !!!!

The format I'm using inside the private functions is as
follows:

setConnection   ' issue open statement

gSQLSyntax = " Insert into tab1 ...."

LocalConn.Execute (gSQLSyntax)

CloseConnection  ' issue close statement

Which is fine as the Userid is the start of all keys on
the relevant tables in the DB, so locking is not needed.

Thanks in Advance



Mon, 30 Aug 2004 17:55:48 GMT  
 Oracle commits vs Sybase/SQL-Server commits
While the default mode for tools such Oracle SQL*Plus is autocommit-off, so
you must type "COMMIT" after each statement (which is what the DBA told
you), the default mode for Oracle ODBC/OLEDB/ADO connections is
autocommit-on (the ODBC driver/ OLEDB provider takes care of this). That
means that is the same behaviour than SQL Server, Access and others, no need
to change the source code. As in other databases, if you want a transaction
with manual commit, you must call the BeginTrans (or similar function) and
later call the Commit or Rollback function.

--
Best regards,

Carlos J. Quintero

MZ-Tools 3.0 freeware 'all-in-one' Add-In for VB6 and VBA: Code Templates,
Detect dead code, Favorite Procedures, Favorite Projects, External
Utilities, XML Documentation,  Private Clipboards, Select Case Assistant,
Sort Procedures, Collapse Projects, Split/Combine Lines, Open Folder of
File, Edit File As Text, MsgBox Assistant, Connect String Assistant and many
more features:
www.mztools.com


Quote:
> Hi,

> I recently finished a system to access DBs on any platform
> using middleware (with ADO). As part of the system
> user info on reporting etc will be held in SQL-
> Server/Sybase/Oracle database. I simply ask the DBA to
> provide the connection string for ADO in an INI file. I
> have tested the system on SQL-Server and Sybase but not
> yet on Oracle. A DBA informed me that unlike SQL-S/Sybase
> that commits automatically, Oracle does not therefore my
> following code would need changing to use commit/rollback
> syntax. I kept the SQL syntax pure or so I thought...

> Is this true for Oracle ?
> Can ADO auto-enforce the commit for me with COMMIT syntax?
> The public functions of my DLL on successful completion of
> the internal private functions issue an MTS setcomplete,
> can this commit in some way ?

> I really do not want to re-write all the private functions
> of my VB DLL !!!!

> The format I'm using inside the private functions is as
> follows:

> setConnection   ' issue open statement

> gSQLSyntax = " Insert into tab1 ...."

> LocalConn.Execute (gSQLSyntax)

> CloseConnection  ' issue close statement

> Which is fine as the Userid is the start of all keys on
> the relevant tables in the DB, so locking is not needed.

> Thanks in Advance



Mon, 30 Aug 2004 20:35:59 GMT  
 Oracle commits vs Sybase/SQL-Server commits
How would you go about disabling the Auto Commit in the OleDB driver in VB?


Quote:
> While the default mode for tools such Oracle SQL*Plus is autocommit-off, so
> you must type "COMMIT" after each statement (which is what the DBA told
> you), the default mode for Oracle ODBC/OLEDB/ADO connections is
> autocommit-on (the ODBC driver/ OLEDB provider takes care of this). That
> means that is the same behaviour than SQL Server, Access and others, no need
> to change the source code. As in other databases, if you want a transaction
> with manual commit, you must call the BeginTrans (or similar function) and
> later call the Commit or Rollback function.

> --
> Best regards,

> Carlos J. Quintero

> MZ-Tools 3.0 freeware 'all-in-one' Add-In for VB6 and VBA: Code Templates,
> Detect dead code, Favorite Procedures, Favorite Projects, External
> Utilities, XML Documentation,  Private Clipboards, Select Case Assistant,
> Sort Procedures, Collapse Projects, Split/Combine Lines, Open Folder of
> File, Edit File As Text, MsgBox Assistant, Connect String Assistant and many
> more features:
> www.mztools.com



> > Hi,

> > I recently finished a system to access DBs on any platform
> > using middleware (with ADO). As part of the system
> > user info on reporting etc will be held in SQL-
> > Server/Sybase/Oracle database. I simply ask the DBA to
> > provide the connection string for ADO in an INI file. I
> > have tested the system on SQL-Server and Sybase but not
> > yet on Oracle. A DBA informed me that unlike SQL-S/Sybase
> > that commits automatically, Oracle does not therefore my
> > following code would need changing to use commit/rollback
> > syntax. I kept the SQL syntax pure or so I thought...

> > Is this true for Oracle ?
> > Can ADO auto-enforce the commit for me with COMMIT syntax?
> > The public functions of my DLL on successful completion of
> > the internal private functions issue an MTS setcomplete,
> > can this commit in some way ?

> > I really do not want to re-write all the private functions
> > of my VB DLL !!!!

> > The format I'm using inside the private functions is as
> > follows:

> > setConnection   ' issue open statement

> > gSQLSyntax = " Insert into tab1 ...."

> > LocalConn.Execute (gSQLSyntax)

> > CloseConnection  ' issue close statement

> > Which is fine as the Userid is the start of all keys on
> > the relevant tables in the DB, so locking is not needed.

> > Thanks in Advance



Sat, 18 Sep 2004 23:04:46 GMT  
 Oracle commits vs Sybase/SQL-Server commits
If you want to disable autocommit, then you want a transaction. The ADO
Connection object provides the methods to begin, commit and rollback
transactions. Not sure now about the exact names. See the help or use the
Intellisense.

--
Best regards,

Carlos J. Quintero

MZ-Tools 3.0 freeware 'all-in-one' Add-In for VB6 and VBA: Code Templates,
Detect dead code, Favorite Procedures, Favorite Projects, External
Utilities, XML Documentation,  Private Clipboards, Select Case Assistant,
Sort Procedures, Collapse Projects, Split/Combine Lines, Open Folder of
File, Edit File As Text, MsgBox Assistant, Connect String Assistant and many
more features:
www.mztools.com


Quote:
> How would you go about disabling the Auto Commit in the OleDB driver in
VB?




Quote:
> > While the default mode for tools such Oracle SQL*Plus is autocommit-off,
so
> > you must type "COMMIT" after each statement (which is what the DBA told
> > you), the default mode for Oracle ODBC/OLEDB/ADO connections is
> > autocommit-on (the ODBC driver/ OLEDB provider takes care of this). That
> > means that is the same behaviour than SQL Server, Access and others, no
need
> > to change the source code. As in other databases, if you want a
transaction
> > with manual commit, you must call the BeginTrans (or similar function)
and
> > later call the Commit or Rollback function.

> > --
> > Best regards,

> > Carlos J. Quintero

> > MZ-Tools 3.0 freeware 'all-in-one' Add-In for VB6 and VBA: Code
Templates,
> > Detect dead code, Favorite Procedures, Favorite Projects, External
> > Utilities, XML Documentation,  Private Clipboards, Select Case
Assistant,
> > Sort Procedures, Collapse Projects, Split/Combine Lines, Open Folder of
> > File, Edit File As Text, MsgBox Assistant, Connect String Assistant and
many
> > more features:
> > www.mztools.com



> > > Hi,

> > > I recently finished a system to access DBs on any platform
> > > using middleware (with ADO). As part of the system
> > > user info on reporting etc will be held in SQL-
> > > Server/Sybase/Oracle database. I simply ask the DBA to
> > > provide the connection string for ADO in an INI file. I
> > > have tested the system on SQL-Server and Sybase but not
> > > yet on Oracle. A DBA informed me that unlike SQL-S/Sybase
> > > that commits automatically, Oracle does not therefore my
> > > following code would need changing to use commit/rollback
> > > syntax. I kept the SQL syntax pure or so I thought...

> > > Is this true for Oracle ?
> > > Can ADO auto-enforce the commit for me with COMMIT syntax?
> > > The public functions of my DLL on successful completion of
> > > the internal private functions issue an MTS setcomplete,
> > > can this commit in some way ?

> > > I really do not want to re-write all the private functions
> > > of my VB DLL !!!!

> > > The format I'm using inside the private functions is as
> > > follows:

> > > setConnection   ' issue open statement

> > > gSQLSyntax = " Insert into tab1 ...."

> > > LocalConn.Execute (gSQLSyntax)

> > > CloseConnection  ' issue close statement

> > > Which is fine as the Userid is the start of all keys on
> > > the relevant tables in the DB, so locking is not needed.

> > > Thanks in Advance



Sat, 18 Sep 2004 23:35:39 GMT  
 Oracle commits vs Sybase/SQL-Server commits
Yep, that works great. Unfortuately, the VB documentation doesn't
really mention that if you don't explicitly include your SQL or SP
call in a transaction, it automatically issues a commit after each
statement. Which, by the way, was really screwing me up because I was
using a temp table with on commit delete rows.

I also tried to disable autocommit in the OLEDB drivers using a
parameter in the connection string. I couldn't get this to work
though.


Quote:
> If you want to disable autocommit, then you want a transaction. The ADO
> Connection object provides the methods to begin, commit and rollback
> transactions. Not sure now about the exact names. See the help or use the
> Intellisense.

> --
> Best regards,

> Carlos J. Quintero

> MZ-Tools 3.0 freeware 'all-in-one' Add-In for VB6 and VBA: Code Templates,
> Detect dead code, Favorite Procedures, Favorite Projects, External
> Utilities, XML Documentation,  Private Clipboards, Select Case Assistant,
> Sort Procedures, Collapse Projects, Split/Combine Lines, Open Folder of
> File, Edit File As Text, MsgBox Assistant, Connect String Assistant and many
> more features:
> www.mztools.com



> > How would you go about disabling the Auto Commit in the OleDB driver in
>  VB?



> > > While the default mode for tools such Oracle SQL*Plus is autocommit-off,
>  so
> > > you must type "COMMIT" after each statement (which is what the DBA told
> > > you), the default mode for Oracle ODBC/OLEDB/ADO connections is
> > > autocommit-on (the ODBC driver/ OLEDB provider takes care of this). That
> > > means that is the same behaviour than SQL Server, Access and others, no
>  need
> > > to change the source code. As in other databases, if you want a
>  transaction
> > > with manual commit, you must call the BeginTrans (or similar function)
>  and
> > > later call the Commit or Rollback function.

> > > --
> > > Best regards,

> > > Carlos J. Quintero

> > > MZ-Tools 3.0 freeware 'all-in-one' Add-In for VB6 and VBA: Code
>  Templates,
> > > Detect dead code, Favorite Procedures, Favorite Projects, External
> > > Utilities, XML Documentation,  Private Clipboards, Select Case
>  Assistant,
> > > Sort Procedures, Collapse Projects, Split/Combine Lines, Open Folder of
> > > File, Edit File As Text, MsgBox Assistant, Connect String Assistant and
>  many
> > > more features:
> > > www.mztools.com



> > > > Hi,

> > > > I recently finished a system to access DBs on any platform
> > > > using middleware (with ADO). As part of the system
> > > > user info on reporting etc will be held in SQL-
> > > > Server/Sybase/Oracle database. I simply ask the DBA to
> > > > provide the connection string for ADO in an INI file. I
> > > > have tested the system on SQL-Server and Sybase but not
> > > > yet on Oracle. A DBA informed me that unlike SQL-S/Sybase
> > > > that commits automatically, Oracle does not therefore my
> > > > following code would need changing to use commit/rollback
> > > > syntax. I kept the SQL syntax pure or so I thought...

> > > > Is this true for Oracle ?
> > > > Can ADO auto-enforce the commit for me with COMMIT syntax?
> > > > The public functions of my DLL on successful completion of
> > > > the internal private functions issue an MTS setcomplete,
> > > > can this commit in some way ?

> > > > I really do not want to re-write all the private functions
> > > > of my VB DLL !!!!

> > > > The format I'm using inside the private functions is as
> > > > follows:

> > > > setConnection   ' issue open statement

> > > > gSQLSyntax = " Insert into tab1 ...."

> > > > LocalConn.Execute (gSQLSyntax)

> > > > CloseConnection  ' issue close statement

> > > > Which is fine as the Userid is the start of all keys on
> > > > the relevant tables in the DB, so locking is not needed.

> > > > Thanks in Advance



Sun, 19 Sep 2004 21:32:15 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Access vs SQL Server vs Sybase vs Oracle

2. Using commit/rollback with Oracle and RDO

3. oracle connected via odbc commits implicitly

4. RDO and Committing to Oracle

5. Oracle: Need commit?

6. ADO hanging waiting for Oracle commit

7. RDO ResultSets vs 'Commit'

8. RollBack vs. Commit

9. Commit data to access database on network server

10. SQL commit statement for Access?

11. Begintrans-Commit with SQL-Server7??

12. Datagrid 6.0 won't commit changes to 7.0 SQL Database

 

 
Powered by phpBB® Forum Software