Re: How to exit dbaccess if sqlerror ..!

On Wed, 17 Feb 1999 shivaa@my-dejanews.com wrote: > I am running multiple/sequential sql statements contained in a sql file using > 'dbaccess' under unix/informix environment .. If one of the sql statements > fail I would like to exit out, without processing any of the subsequent sql > statements .. If I had to do something similar in ORACLE environment with > 'sqlplus', I would do something like the following .. > > sqlplus user/passwd@somewhere <<! > whenever sqlerror exit sql.sqlcode; > select count(*) from xxx; > select count(*) from yyy;> ! > > If in the above code the first select count(*) fails then sqlplus exits out > without processing the second select statement .. I like to do a similar thing > in Informix preferably with dbaccess .. Any suggestions .. ! There's an environment variable DBACCNOIGN recognized by some more recent versions of DB-Access which forces an exit after an error, but the control is crude. The value (0, 1, empty string, etc) doesn't matter. An alternative is to use SQLCMD from the IIUG archives; that has much more complete control over whether an error stops the script or not: sqlcmd -d dbase <<! continue off; select count(*) from xxx; select count(*) from yyy;! As a matter of idle fact, for a script like this, continue is off by default anyway. The script will only continue if you do 'continue on'. There's a stack of continue states, so you can also do: continue push; continue on; ...command that can fail... continue pop; > I know that I can wrap each of the sql with a shell and check for error and > exit out if needed .. But that is not an option for me since I am creating > temporary tables that will be lost when I close the dbaccess session and open > another .. ! I do not want to use any procedural language like 4GL or esqlc > either .. I would like to accomplish this with dbaccess or a similar tool .. > Any input will be greatly appreciated .. Any views I express about SQLCMD are coloured by the fact that I wrote it. Yours, Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h> Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn