There isn't any particularly simple way to do it. Essentially, you have to try to start a transaction and see whether you get an error.
For example, this stored procedure determines the transaction state. Remember that Informix supports unlogged databases where transactions are not available.
```sql
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;
```
### Exception handling in SPL (Stored Procedure Language)
Since [ON EXCEPTION](https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_1351.htm) 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:
``` sql
-- 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:
``` SQL
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:
``` SQL
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.
<hr>
### Executing statements and not committing on failure
If you're using DB-Access to execute a sequence of commands, you can create your script (call it `script.sql`) using the outline:
``` SQL
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.
``` sh
$ 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](https://www.iiug.org/en/iiug-software-repository/#ESQL)), 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).
``` sh
$ 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.
