RE: stop dbaccess when a statement in a sql script fails
Hi,
Taken from the IIUG faq.
5.15 How can I stop an SQL script if an error occurs?
girsch@iname.com (Thomas J. Girsch) writes:-
If you create an SQL text file, you can run it with dbaccess by doing:
$ dbaccess <database> <sql-file>This will run the SQL command without firing up the menu interface. Note,
however, that all the statements will run, even if some in the middle fail.
For example, if you run this batch that way:
BEGIN WORK;
INSERT INTO history
SELECT *
FROM current
WHERE month = 11;
DELETE FROM current
WHERE month = 11;COMMIT WORK;
If the INSERT statement fails, the DELETE statement still executes, as does
the commit work. This could be very bad. This behavior can be changed by
setting an undocumented (to my knowledge) environmental variable,
[Maintainers note n 18th Dec 1997 richard_thomas@yes.optus.com.au (Richard
Thomas) corrected the name of the environment variable]
DBACCNOIGN=1
> -----Original Message-----
> From: Carl Y. Wu [SMTP:carlywu2@yahoo.com]
> Sent: Tuesday, July 04, 2000 3:56 PM
> To: informix-list@iiug.org
> Subject: stop dbaccess when a statement in a sql script fails
>
> When using dbaccess to run a batch SQL script, how to instruct dbaccess to
> stop when a sql statement fails?
>
> For example, if mysqlscript.sql contains the following statement:
>
> BEGIN WORK;
> DELETE FROM mytable1 WHERE ......;
> INSERT INTO mytable2 VALUES (......);> COMMIT WORK;
>
> If the delete fails the insert will still be executed and the transaction
> will be committed. Is there any way to let dbaccess stop when any of the
> statement fails?
>
> Regards,
> Carl Y. Wu
>
>
>
>