CODEWITHSUNDEEP
javaregexregex-look-ahead
Mikhail T.
Mikhail T.

Reputation: 3967

A regex to split SQL code into multiple batches

We have a Java program that's used to feed arbitrary SQL code to SQL Servers via JDBC.

The older versions would simply split the input SQL on the ;-character sending one part at a time using JDBC's PreparedStatement.

That was a too simple-minded -- what if the ; is encountered inside a string-literal? -- so the new code tried to throw the entire SQL at the server in one PreparedStatement.

That worked for much of our existing SQL files, except for those, which create stored-procedures and execute them:

INSERT INTO #MEOW VALUES('foo', 1);
INSERT INTO #MEOW VALUES('bar', 2);
CREATE PROCEDURE #MEOW_sp
AS
    BEGIN
        SELECT BAR, FOO INTO #WOOF FROM #MEOW
    END;
INSERT INTO #MEOW VALUES('boo', 11);
exec #MEOW_sp;

According to SQL Server documentation, the CREATE PROCEDURE must be the sole statement in a batch.

So we still need to be splitting the SQL blobs, but I'd like a more sophisticated split-boundary than ;. What would the pattern be, that would turn the above SQL code into three parts:

  1. One with the two INSERTs.
  2. One with the CREATE PROCEDURE.
  3. One with the third INSERT and exec.

I imagine, it'd have to use "look-ahead" regex, something like (?=(((CREATE|ALTER)\\s+PROCEDURE[^;]+;*))) -- but it is not working...

Upvotes: 1

Views: 73

Answers (2)

talex
talex

Reputation: 20436

This problem usually solved by using separator that unlikely be a part of SQL code.

I suggest -----.

Upvotes: 1

Bohemian
Bohemian

Reputation: 424953

Assuming procedures end with END;:

var batches = s.split(";\\R(?=CREATE)|(?<= END);\\R");

Upvotes: 1

Related Questions