jdbc

[toe_cutter]

My company use Maven to run a DBRefresh goal. There are prepared .sql bootstrap file that need to be run when installing the product. I wrote a framework that automates the setup and execution of the junit funcitonal tests.

My problem now is that the java class that was written (I didn't write it) to parse the .sql bootstrap file reads in the .sql file line by line and does some find and replace of a couple of things, and then it will execute the sql statement. The biggest problem is how it decides when a sql statement is complete.

Here is the code:

if (!isComment) {

// Concatenate lines until separator line is reached.
// FIXME: The parsing is quite broken, as it doesn't handle
// things like stored procs and triggers well...  needs to either
// be a proper parser or should just go away and use the vendor's tools
// to run scripts.
  if( trimmed.equals(".") ) {
    multiLine += ";\n";
  } else if ( trimmed.equalsIgnoreCase( "run" ) ) {
    executeStatement( multiLine, continueOnFailure );
    multiLine = "";
  } else if( multiLine.equals( "" ) || currentLine.matches( "^\\s+.*" ) ) {
    multiLine += (currentLine + "\n");
  } else if ( !multiLine.equals( "" ) ) {
    executeStatement( multiLine, continueOnFailure );
    multiLine = trimmed.equals( ";" ) ? "" : currentLine + "\n";
  }
}

( "^\\s+.*" ) So when the code find a line with whitespace and some number of characters after it will concatenate it to the previous line. This means we have to make sure anyone who changes the schema must put at least a single whitespace character before the next line of their statement. I do not what to make the developers do this. The file is a valid sql file because I ran it through sqlplus and it execute flawlessly.

I am hoping there is someway I can just execute an entire sql file through jdbc or some other mechanism? I can use ant's replace functionality to edit the file as needed but I have no idea how to parse the sql file without make a whole bunch of rules.

-Toe

[Belisarius]

Do all the valid SQL statements end in ";"?

[toe_cutter]

Originally Posted by Belisarius

Do all the valid SQL statements end in ";"?

Yes they do, but there can be a problem of queries inside queries.

I solved it by not using Java at all. I wrote a maven plugin that installs the oracle instant client which allows you to run sqlplus commands to a remote host.

-Toe