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.

I did it your way when I had to do a similar thing. It's only used internally and works fine for us. — brain
– brain, Commented May 24, 2012 at 9:41
Idem here... It's far from fool-proof and you need to decide if you want to support things like 'GO 100'. The main hurdle IMHO is when the GO is part of a /* comment block */. Those require quite a bit of extra processing. (strip out all comments & comment block first, then do the split is what we did) PS: You can prefix the code with SET PARSEONLY ON and if there is no 'Incorrect syntax near GO' there is no need to split the code.. otherwise you might use the line-location indicated by the error to 'pinpoint where to split on a GO... might work, feels more complex than needed though... — deroby
– deroby, Commented May 24, 2012 at 10:40
example to complicate things : EXEC (' SET PARSEONLY ON;SELECT * FROM t_entity; GO; PRINT 0') — deroby
– deroby, Commented May 24, 2012 at 10:41
Could your project export the ddl modifications in an XML format then process the statements separately? db.apache.org/ddlutils/documentation.html does something like this. — pd40
– pd40, Commented Jun 2, 2012 at 13:37
Gave up, left an open issue github.com/timabell/sqlHawk/issues/49 & went for requiring GO to be on its own on a line. — Tim Abell
– Tim Abell, Commented Jun 5, 2012 at 11:32

PollusB · Accepted Answer · 2012-05-28 19:40:36Z

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

answered May 28, 2012 at 19:40

PollusB

1,8762 gold badges25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Abell Over a year ago

Thanks but that doesn't give me any clues how I might reliably perform the split in Java. I think you are right about EXEC but I haven't checked.

Matt Whitfield · Accepted Answer · 2012-06-03 21:15:10Z

1

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.

answered Jun 3, 2012 at 21:15

Matt Whitfield

6,6343 gold badges31 silver badges47 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related