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

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. > Just a warning... Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn