Re: Q: How to break SQL file into single SQL statements?

Jonathan Leffler <jleffler@informix.com> wrote: > >Oops -- there was a missing 'not' in my posting... > >On Thu, 3 Sep 1998, Jonathan Leffler wrote: >> Hal Larson wrote: >> > Dmitry Korenkov <korenkov@pluto.xTech.RU> wrote: >> > >I have an SQL script file with a bunch of SQL >> > >statements separated by ';'. I have a problem >> > >of breaking this file info single SQL statements >> > >because ':' can be within SQL statement as well. >> > >[...] >> > >> > Use awk or perl. Create a new file every time >> > you run across the semi-colon UNLESS you are >> > inside the definition of a stored procedure >> > or a trigger [...] >> > >> > in_sps==0 && $1=="CREATE" && $2=="PROCEDURE" { in_sps=1 } #in stored proc >> > in_trs==0 && $1=="CREATE" && $2=="TRIGGER" { in_trs=1 } #in trigger >> > [...] >> > in_sps==1 && $1=="END" && $2=="PROCEDURE" { in_sps=0 } #out of stored proc >> > in_sps==1 && $1=="END" && $2=="TRIGGER" { in_trs=0 } #out of trigger >> >> I don't think you can get semi-colons embedded in CREATE TRIGGER >> statements -- but I'm willing to believe I'm wrong. I'm also fairly >> sure that END TRIGGER is part of Informix's SQL syntax. > >I'm sure that END TRIGGER is *NOT* part of Informix's SQL syntax (I've >checked in the manuals now). I've also taken a look at the CREATE TRIGGER >syntax and cannot see how you would get semi-colons into the middle of one >of those statements. > >> The other problem not handled by this code is a statement which >> includes a semi-colon in a string -- that always makes life tougher. > >So do comments. All sorts of techniques work quite well until you run into >either strings or comments. If you don't have recalcitrant strings and >comments, then you're in with a fighting chance of using the simple >pattern-matching techniques in Perl or Awk. If you have to worry about >either, then you're reduced to properly tokenizing the SQL, which is >painful. Yeah, I thought about the odd separator inside a string or comment. OTOH, tokenizing is fairly easy to do if you know lex and yacc. (and the grammar, which might be the pain you refer to). I haven't done it since my salad days, but the O'Reilly nutshell guides really do a good job of getting one started. In the end, I think the original poster will have to fall back on the greatest pattern matching engine known to mankind: the human brain. We can give him all the advice he wants, but it's up to him to either make the effort of automating the process himself or do it manually. ------------------------------------------------------- "You're missing the point," said the fork to the spoon.