anyone know how hard splitting a sql batch file up into individual statements is (given the possibility of strings with various delimiters containing ';'s and 'GO' etc) - and if there are any libraries around that do this?
If you could tokenise the SQL with flex then it shouldn't be too hard to pick out the statement delimiters. Making lexers isn't all that hard.
I've used flex as a sort of super grep on several occasions. It's easier than it looks once you understand the tool.
You shouldn't need to implement a grammar to find the delimiter tokens, because flex will be able to recognise comments and string literals.
I did a lexer for a whole language (cecil) in about a day or two for a computer science assignment. I think a lexer for PL/SQL or T-SQL wouldn't be all that grim.
But, I can't think of anything easier than that. It might be quicker to load it into vim and pick it apart by hand.
If they can be picked out by a regular expression they should be OK. Flex uses regexes for token matching, but it's a little bit cleverer than just a regular language.
It has a stack, so you can push and pop states and have state dependent token matching patterns.
The default output of flex is just to print the tokens back to stdout. You can add specific actions to tag a statement delimiter with something that can be picked up with a dumber tool.
Depending on the scale of your problem it might be overkill, but it is quite a bit smarter than grep.
@JackDouglas You can define custom actions to implement when you match specific tokens.
What you would do is to match statement delimiter tokens and take a specific action like redirecting the output to another file when you encounter them.
Or doing it in two hops and tagging the tokens with something that can be picked up with something dumber like a little perl or awk script that switches the output file when it picks up the gag.
@JackDouglas Dead easy from C. It generates C code for the lexer that you can compile and embed into a C program.
I could dig out my old COSC202 assignment and send it to you if you want a worked example of a lexer.
Plus the O'reilly lex & yacc book actually has an example of a YACC brammar for SQL (presumably a standard dialect) if you really want to go to town on parsing.
