This post originated from an RSS feed registered with Java Buzz by Brian McCallister. Original Post: SQL Scripts in Java Feed Title: Waste of Time Feed URL: http://kasparov.skife.org/blog/index.rss Feed Description: A simple waste of time and weblog experiment Latest Java Buzz Posts Latest Java Buzz Posts by Brian McCallister Latest Posts From Waste of Time

Devender demonstrated shelling out to SQL*Plus to run a script, figured I had to trackback with a jDBI example that doesn't have to shell out, just for alternatives (and because this is a common mechanism for me to construct test schema =)

Our script, with various comment styles (this was taken from the regression tests for jDBI, btw):

-- insert 3 lines
insert into something(id, name) values (1, 'one');
# line 2
insert into something (id, name) values (2, 'two');
// line 3
insert into something (id, name) values (3, 'three');

and our code to run the script:

    public void testScriptExample() throws Exception
    {
        Handle h = DBI.open("jdbc:derby:testing");
        assertEquals(0, h.query("select * from something").size());
        h.script("src/test-etc/insert-script-with-comments.sql");
        assertEquals(3, h.query("select * from something").size());
        h.close();
    }

Shelling out to SQL*Plus is always an option I guess =) This did remind me that I need to change jDBI to use a script locator mechanism analogous to its named statement locator, in case you want to store your scripts on a web server, in the database, in JNDI, or... wherever comes to mind. Guess 1.3.3 may be out soon!