groovy

I'd love to see this included by default also, and it would be great if this usage as shown in the email above (which I found through Google) was added to the groovy sql docs. (In the meantime can someone add this good closure example to the docs?)

The online documentation is backed by Confluence, so you can freely help us improve the documentation, add more examples, etc.
You just need to register on the site.
Such documentation contributions are very much appreciated.

Also, you can submit a patch for this feature, and we'll review it and perhaps include it in an upcoming version of Groovy.

Potential patch attached. I haven't had time to test it yet.

added to trunk, pending merge onto 1_6_X branch

merged

I can't seem to get this to work if I'm using a DataSource. I'll end up with
INFO: Caught exception closing connection: java.sql.SQLException: Connection is closed.
java.sql.SQLException: Connection is closed.

I tried different ways to get it to work and the only thing that seemed to work for me was using what Guillaume posted a while back (I just modified it to call a closure.) It's not as nice though (or maybe it's ok), because it also requires passing back a new Sql instance. When I tried this not passing back a new sql instance, the transaction never rolled back.

So what I have is:

Sql.metaClass.useTransaction = { Closure closure ->
java.sql.Connection conn = null
try { conn = createConnection() conn.autoCommit = false Sql sql2 = new Sql( conn ) closure(sql2) conn.commit() } catch (e) { if(conn != null) conn.rollback() e.printStackTrace() throw e } finally {
if ( conn != null ) {
conn.autoCommit = true
if ( dataSource ) { conn.close() }
}
}
}

which of course requires our closure to use an instance of the Sql passed back in the closure. I'm sure there is probably a way to get the patched version to work using a datasource but you guys would know best.

Not sure if this should a new issue?

Can you try again using trunk? I hope the problem has gone away with the latest changes.

Now in the 1_6_X branch as well. If you have any feedback before the 1.6.3 release it would be most appreciated. You might want to look at the relevant test cases to see the usage patterns.

Paul, I finally got back to looking at this stuff, I have a quick question...

I'm a bit confused on the logic within the withTransaction method. What is the purpose of setting cacheConnection = false in the finally block only to then turn around and set it again with the savedCacheConnection. Does closeResources do something where you want to make sure the cacheConnection is always false first before calling closeResources().. that's the only reason I could see to setting it to false first, but it doesn't look closeResources would care? Was just curious.

(I also have another question related to the class being Thread safe which I'll ask on the list since it could be useful in case anyone else is searching the forums.)

Thanks again.