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

Hal Larson wrote: > > Dmitry Korenkov <korenkov@pluto.xTech.RU> wrote: > > > 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. > >Does anyone know a program that can break SQL > >file into single SQL statements? > > > >=Dmitry > ><korenkov@pluto.iis.nsk.su> > >:q > > Dmitry, > 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 (might be a few other other similar > situations, but I'm too lazy to go to the syntax > manual and figure them out). > > do it like this: > > sed -e 's/;/;\\n/' filename.sql | awk ' > BEGIN { in_sps=0; in_trs=0 } #not in a stored proc or trigger stmt > 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 > > { print } #print every line- guaranteed to work because we add a newline > #after every semi-colon with the sed command above. Sneaky. > > 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 > > # if not in stored procedure or trigger or (add other conditions here) > # then, print a line telling you to start another file. Or use the > # appropriate awk syntax to create the file yourself: > > in_sps==0 && in_trs==0 { print; print "===========NEXT FILE============" } > ' > > As you can see, I copped out on the awk code to create a new file, but > what's there should be enough to get you started. Good luck with your > project! 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. The other problem not handled by this code is a statement which includes a semi-colon in a string -- that always makes life tougher. Just a warning... -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>