Groovy. Batch. Prepared statement. Nice!
[This article was first published on novyden, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
Scripting with Groovy is exciting thing. You get a feeling that the language was inspired by an Oracle who read your mind and then made it 10 times better. So imagine how I felt after finding out that Groovy can NOT batch prepared statements.Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
Batching sql updates, inserts or deletes is one of the top features that database scripts would need. Without prepared statements I had to resort to generating SQL with GString:
sql.withBatch { stmt -> mymap.each { k,v -> stmt.addBatch("""UPDATE some_table SET some_column = '${v}' WHERE id = ${k} """) } }Besides SQL injection this presents the problem of escaping strings in SQL: big pain in some cases. By arguing that injection is not an issue for internal script (it’s not in a wild on the web after all) you would leave yourself with the loop hole anyway. Don’t forget about performance. The bottom line: I need support for prepared statements!
I would have to stop here joining ranks of complaints like this if not for Groovy 1.8.1. This latest stable version (as of today) addresses bunch of bugs and just couple of features. And one of two is batch support for prepared statements. Below is secure and reliable (as well as more readable) version with batch support for prepared statement in 1.8.1:
sql.withBatch(20, """UPDATE some_table SET some_column = ? WHERE id = ? """) { ps -> mymap.each { k,v -> ps.addBatch(v, k) } }
You can find more options on how use batching with prepared statements in Groovy 1.8.1 docs.
To leave a comment for the author, please follow the link and comment on their blog: novyden.
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.