how to turn autocommit off with groovy sql?

[Greg Lappen]

Hi All-

I am using a groovy script to do some data migration between different
versions of our product and am going to be executing a bunch of sql
inserts. What i want to do is turn autocommit off in the beginning of
my script, then do either a commit or rollback at the end. Can't seem
to find any documentation on controlling autocommit, but the javadocs
show that there are commit() and rollback() methods in groovy.sql.Sql
so I'm optimistic it can be done. Any pointers would be greatly
appreciated!

Thanks,

Greg

[Jeremy Rayner]

Post by Greg Lappen
What i want to do is turn autocommit off in the beginning of
my script, then do either a commit or rollback at the end. Can't seem
to find any documentation on controlling autocommit, but the javadocs
show that there are commit() and rollback() methods in groovy.sql.Sql
so I'm optimistic it can be done. Any pointers would be greatly
appreciated!
From looking through the source (

http://groovy.codehaus.org/xref/groovy/sql/Sql.html ) it appears that
the handling depends on the connection.

*if* you supplied a DataSource,
then after each call to executeX()/call() etc the connection is
closed, and thus returned to the pool.
(I'm not sure as to the effect this would have on the current
commitment boundaries.)


*However* if you're not using a DataSource, then you are probably in luck.

e.g. simple 'untested' example...

sql = Sql.newInstance(myDatabaseURL)
sql.connection.autoCommit = false
try {
['darv','astra','telson','sharna'].each {
sql.execute("insert into challenger_crew (name) values ${it}")
...
}
sql.commit()
} catch (SQLException e) {
log(e)
sql.rollback()
} finally {
sql.connection.close()
}

Hope this helps

Jez.

[Greg Lappen]

I will give that a try...I'm not using a DataSource so it might work.

Thanks!

Greg

Post by Greg Lappen

Post by Greg Lappen
What i want to do is turn autocommit off in the beginning of
my script, then do either a commit or rollback at the end. Can't seem
to find any documentation on controlling autocommit, but the javadocs
show that there are commit() and rollback() methods in groovy.sql.Sql
so I'm optimistic it can be done. Any pointers would be greatly
appreciated!

From looking through the source (
http://groovy.codehaus.org/xref/groovy/sql/Sql.html ) it appears that
the handling depends on the connection.
*if* you supplied a DataSource,
then after each call to executeX()/call() etc the connection is
closed, and thus returned to the pool.
(I'm not sure as to the effect this would have on the current
commitment boundaries.)
*However* if you're not using a DataSource, then you are probably in luck.
e.g. simple 'untested' example...
sql = Sql.newInstance(myDatabaseURL)
sql.connection.autoCommit = false
try {
['darv','astra','telson','sharna'].each {
sql.execute("insert into challenger_crew (name) values ${it}")
...
}
sql.commit()
} catch (SQLException e) {
log(e)
sql.rollback()
} finally {
sql.connection.close()
}
Hope this helps
Jez.
--
http://javanicus.com/blog2

[Daniel Serodio]

Post by Greg Lappen

Post by Greg Lappen
What i want to do is turn autocommit off in the beginning of
my script, then do either a commit or rollback at the end. Can't seem
to find any documentation on controlling autocommit, but the javadocs
show that there are commit() and rollback() methods in groovy.sql.Sql
so I'm optimistic it can be done. Any pointers would be greatly
appreciated!

From looking through the source (
http://groovy.codehaus.org/xref/groovy/sql/Sql.html ) it appears that
the handling depends on the connection.
*if* you supplied a DataSource,
then after each call to executeX()/call() etc the connection is
closed, and thus returned to the pool.
(I'm not sure as to the effect this would have on the current
commitment boundaries.)
*However* if you're not using a DataSource, then you are probably in luck.
<snip>
Hope this helps
Jez.

Don't we want this behaviour to be deterministic, independent of whether
it's a Connection or a DataSource? I can take a look at it, if that's
the intent.

[]'s
Daniel Serodio