I am working with Informix database. I want to execute a few queries inside of the transaction and in case of an error I want to rollback all of the edits, so something equivalent to this in SQL Server:
BEGIN TRY
BEGIN TRAN
-- insert, update, delete statements etc.
COMMIT TRAN
END TRY
BEGIN CATCH
IF(@@TRANCOUNT > 0)
ROLLBACK TRAN;
END CATCH
Here is the Informix syntax for a transaction:
BEGIN WORK;
-- statements
COMMIT WORK;
When I search for "informix try catch" the first link I get is about "On Exception", however, non of the pages show a complete example of the syntax on how to catch exception and rollback and I cannot find such example anywhere on internet. I tried executing the following query:
begin work;
create table test_abc
(
test_value int
);
on exception
rollback work;
end exception
commit work;
My expectation was that the first time this query would work, but on a second time it would fail because table already exists. However, on the first time I got this error:
begin work;
create table test_abc
(
test_value int
);
on exception
#^
# 201: A syntax error has occurred.
#
rollback work;
end exception
commit work;
so that syntax is invalid.
What is the correct syntax?
Edit: a bit more info on what I am doing. I am having a new task for which I need to add a few new tables and put in a few rows into some of them. Even though it is possible to simply execute all statements at once (or one by one) on all environment (test, prod, etc.) I was hoping I could package all statements inside a transaction to avoid having to clean up if something goes wrong. Jonathan Leffler suggested creating stored procedure and handle exception catching there using on exception, but I only need transaction to be used 1 time, which does not warrant creation of a stored procedure. Technically I can create stored procedure with transaction, run it and delete it, but if there is a way to avoid having to create a stored procedure it would be nice to know how.
Since ON EXCEPTION is a possibility, I assume you're working in a stored procedure.
Your example is along the right lines, but is misplacing the exception handling code:
-- incorrect code from question
begin work;
create table test_abc
(
test_value int
);
on exception
rollback work;
end exception
commit work;
You need to specify the exception handling before the code that might generate the exceptions. A more nearly correct code fragment would be:
CREATE PROCEDURE make_test_abc()
BEGIN WORK;
BEGIN
ON EXCEPTION
ROLLBACK WORK;
END EXCEPTION;
CREATE TABLE test_abc
(
test_value INT
);
COMMIT WORK;
END;
END PROCEDURE;
EXECUTE PROCEDURE make_test_abc();
The notation above works; multiple similar versions did not work. SPL gets very fiddly at times.
I also have a 'transaction state' function in my library which uses ON EXCEPTION:
CREATE PROCEDURE tx_state() RETURNING VARCHAR(14);
DEFINE errcode INTEGER;
ON EXCEPTION IN (-256, -535) SET errcode
IF errcode = -256 THEN
RETURN "TX-Unavailable";
ELIF errcode = -535 THEN
RETURN "In-TX";
END IF;
END EXCEPTION
BEGIN WORK;
ROLLBACK WORK;
RETURN "No-TX";
END PROCEDURE
These days, there are also alternative mechanisms for the table creation exercise:
DROP TABLE IF EXISTS test_abc;
CREATE TABLE IF NOT EXISTS test_abc(test_value INT);
Similar options exist for creating and dropping procedures:
DROP PROCEDURE IF EXISTS make_test_abc;
CREATE PROCEDURE IF NOT EXISTS make_test_abc()
…
END PROCEDURE;
CREATE OR REPLACE PROCEDURE make_test_abc()
…
END PROCEDURE;
The two variant CREATE PROCEDURE statements are compatible with different DBMS other than Informix. The OR REPLACE option requires a 14.10 version (and one of the more recent ones; it wasn't available in 14.10.FC1, I think). The EXISTS options are available in 12.10 too (but again, not necessarily in the older 12.10 versions).
Even with the IF [NOT] EXISTS clauses (or the OR REPLACE clause), the operations can fail if you don't have the relevant permissions, of course.
However, I'm assuming that the question is more general than how to create a table without exceptions. This should give you pointers to get going. The ON EXCEPTION clause is not available except in SPL (stored procedure language), which means it can only be used inside a CREATE PROCEDURE (or CREATE FUNCTION) statement.
If you're using DB-Access to execute a sequence of commands, you can create your script (call it script.sql) using the outline:
BEGIN WORK;
CREATE …
DROP …
CREATE …
GRANT …
…
COMMIT WORK;
You can then run it in batch mode with the environment variable DBACCNOIGN (DB-Access "no ignore errors") set in the environment.
$ DBACCNOIGN=1 dbaccess yourdb script
…voluble output — some to standard output, some to standard error…
$
There will be output from the statements as they succeed, but if one fails, the script will be stopped and the transaction rolled back. If the statements all succeed, the COMMIT WORK will be executed and the transaction committed.
Also, consider checking out SQLCMD (available from the IIUG Software Archive), which I wrote to behave consistently in shell scripting contexts where DB-Access doesn't. It dates back to 1986 (before there was dbaccess; in those days, you used isql instead — DB-Access was carved out of isql in an evening). It bears no relation to Microsoft's johnny-come-lately program of the same name — except for the name and having the same general purpose (manipulate SQL databases). Unlike DB-Access, SQLCMD does not continue on error unless you tell it to do so (-c command-line option, or continue on; in a script file).
$ sqlcmd -d yourdb -f script.sql
$
Unlike DB-Access, there won't be any output from SQLCMD unless you encounter an error, or select something, or request it with the -x (trace) option or -v (verbose) option, or ask for benchmarking (timing) with -B, or otherwise request output.