How can I support the SQL GO statement in a Java / jtds application?

Question

I'm working on a Java based OSS app SqlHawk which as one of its features is to run upgrade sql scripts against a server.

Microsoft have made it a convention to split a script into batches with the GO statement, which is a good idea but just asking for false matches on the string.

At the moment I have a very rudimentary:

// split where GO on its own on a line
Pattern batchSplitter = Pattern.compile("^GO", Pattern.MULTILINE);
...
String[] splitSql = batchSplitter.split(definition);
...

which kind of works but is prone to being tripped up by things like quoted GO statements or indentation issues.

I think the only way to make this truly reliable is to have an SQL parser in the app, but I have no idea how to go about this, or whether that might actually end up being less reliable (especially given this tool supports multiple DBMSs).

What ways could I solve this problem? Code examples would be very helpful to me here.

Relevant sqlHawk code on github.

Currently using jtds to execute the batches found in the scripts.

Answer 1

GO is a client batch seperator command. You can replace it with ;. It should not be sent in your EXEC dynamic SQL.

USE master
GO --<----- client actually send the first batch to SQL and wait for a response
SELECT * from sys.databases
GO

Should be translated in

Application.Exec("USE master");
Application.Exec("SELECT * from sys.databases");

or you can write it this way:

Application.Exec("'USE master;SELECT * from sys.databases")

More about GO http://msdn.microsoft.com/en-us/library/ms188037(v=sql.90).aspx

Answer 2

Ok, so this isn't going to be exactly what you want, but you might find it a start. I released SchemaEngine (which forms the core of most of my products) as open source here. In there, you will find C# code that does what you want very reliably (i.e. not tripping up with strings, comments etc etc). It also support the 'GO x' syntax to repeat a batch x times.

If you download that and have a look in /Atlantis.SchemaEngine/Helpers you'll find a class called BatchParser.cs which contains a method called ParseBatches - which does pretty much what it says on the tin.