Rdb error does not make a VMS error

[Flewdur]

When a VMS/DCL script makes an (Oracle) Rdb database call (through MC SQL$), a severe error (SQL-F-...) in Rdb (wrong table name etc) will make the DCL script stop, but a normal error (SQL-E-...) in Rdb (trying to break unique constraint etc) will make the DCL script continue (same $STATUS as for a successful SQL statement).

In other words:
Rdb success => VMS success
Rdb error => VMS success (!)
Rdb severe error => VMS severe error

How do I achieve "Rdb error => VMS error" so that my script will see all kinds of errors that occur in the database?

[Steven Schweda]

What is the command which leads to the
"normal error (SQL-E-...)"?

> [...] same $STATUS [...]

And that status value is ...?

Sometimes, showing the actual commands and
their actual output can be more helpful than
vague descriptions.

[Flewdur]

Sorry.

Error example:
insert into my_table values (1, "Frank");
(where this row already exists in the table, and there is a unique index on the numeric column)
=> calling DCL script keeps running

Severe error example:
select * from my_ttable;
(spelling error on table name).
=> calling DCL script stops

$STATUS value (checked in the first DCL statement after leaving Rdb) is %X10000001 for the error, as well as for a successful SQL statement. Same thing in interactive DCL/SQL. $SEVERITY was 1 in both cases.

Can't remember the exact $STATUS value for the severe error, but it was something like %X0408A8D3 (made that up, but it was NOT a row of 0's). $SEVERITY was 4, I think.

It says in the VMS manual somewhere (SQL programming, I think), that the VMS condition handler treats an Rdb severe error as an true error but an Rdb error as a success.

So it works like this by design, but I thought there might be a workaround of some sort. My script would need to capture any errors in VMS no matter what the SQL statement is.

My script (A.COM) is intended to work as a "host" script for other scripts that make changes in the Rdb database. Simplified:

$! A.COM
$ ON ERROR GOTO FINISH
$ @B.COM !WITH SQL STATEMENTS
$!
$ @C.COM !WITH SQL STATEMENTS
$!
$ @D.COM !WITH SQL STATEMENTS
$ FINISH:
$ EXIT

I would like that an error in the B.COM SQL call would generate an error in B.COM and A.COM, so that C.COM and D.COM do not run.

It doesn't help to move any checks to B.COM, C.COM etc, because the "damage" is done as soon as the Rdb error is "sent" to VMS.

[GuentherF]

There's nothing you can do from VMS. If an image/program (here SQL) decides to exit with success after an error then that is what DCL receives.

Didn't see a way to change that in Rdb/SQL.

/Guenther

[Chris Barratt]

I haven't tried this, but I notice in the GET DIAGNOSTIC sql command, you do something like

GET DIAGNOSTIC EXCEPTION RETURN SQLCODE

so perhaps you could use this within your SQL script to check after the insert to see if a duplicate error was returned - and handle as needed.

cheers,
chris

P.S. This may depend what version of Rdb you are on...

[Kees L.]

The way I tend to solve these problems is like:
$ pipe mc sql$ select * from table; > test.txt
or like your example:
pipe mc sql$ @b.sql; > test.txt
$ search test.txt "-E-"
This isn't the neat way but it works.

[Wim Van den Wyngaert]

I would capture the output and after each execution I would set the status myself based upon the contents of the file.
something like

f=0
e=0
w=0
open
r: read x log_rcd
if f$loc("DUPLICATE KEY",log_rcd) .lt f$len(log_rcd) then w=1
if f$locate("xxx",log_rcd) .lt f$len(log_rcd) then e=1
...
goto r
close
if f then exit 4
if e then exit 2
if w then exit 0

This script will of course be alive because there are a lot of possibilities why something could go wrong. The alternative is to say that it goes fine if all lines contain known text and else it's wrong (so, only F).

Wim

[Paul Jerrom]

Howdy,

I create a temporary file with my sql statements, and have a "SET OUTPUT" statement in it.

ie sql.tmp is:

set output sql_read.out;
attach 'filename myrdb.rdb';
select time from record where value='12:00'
rollback;
disconnect all;

Then run it:
$ sql @sql.tmp

Then check sql_read.out for "-E-" or "-W-" or "-F-" as applicable

A bit of playing about, but them's the delights of DCL...

have fun,

PJ

[Craig A]

I think the only thing that I would add is if you do end up wrapping commands up in a bit of DCL, then, if at all possible, ASSUME that a failure has occured and check for a string to suggest success.

Craig

[Hoff]

The massive brute-force fix: don't use SQL commands directly. Use your own embedded SQL commands within your own SQL tool images; the SQL$PRE stuff is fairly easy, all things considered. You then have rather more control over the error handling. This presumes you're not (also) assembling your own SQL commands on the fly, and that you have a fixed set of SQL commands of interest, whether with selectable search targets or not.

[Hein van den Heuvel]

Good advice Craig!
The absence of a recognized error message does not imply success.

The presence of a success string like "xyz created" or "nnn records inserted" is much more convincing.

If one were to parse SQL output for errors, then PERL of course offers a nice selection of string matching and reporting tools. For examples:

# perl -ne "$e{$1}++ if /SQL-(.)-/} { for (keys %e) { print qq($_ : $e{$_}\n) }" sql_output.txt
F : 1
E : 2

# perl -ne "$e{$1}++ if /SQL-(.)-/
} {
@codes=qw(F E W I);
for (@codes) {
if ($e{$_}) { print qq(Worst = $_\n); last}
}
print qq(no Errors found\n)" tmp.txt

Worst = W

I guess that part of the problem is that those 'normal errors', in some way of looking at it, are not errors.
SQL worked just fine. I found a dupiclate key and refused to enter it. It did exactly as requested. Success! No 'error' there.

Hein.

[Jean-François Piéronne]

As previously suggested don't use directly SQL.

Most of the time, when I have to do this, I wrote a small script like this Python example (probably can be done in PERL):
$ type foo.py ! space character used fo indentation replace by _ character because itrc remove all leading spaces
import sys, rdb
try:
____ rdb.attachDB('foo')
____ stm = rdb.Statement('select * from bar')
____ rdb.read_only()
____ stm.execute()
____ rdb.commit()
except Exception, e:
____ print e
____ sys.exit(44)

$ python foo.py
(-1041, '%SQL-F-RELNOTDEF, Table BAR is not defined in database or schema', '42000')
%SYSTEM-F-ABORT, abort
$ sh sym $status
$STATUS == "%X0000002C"
$

JF

[Craig A]

One thing to bear in mind is the old
%JBC-F-JOBABORT, job aborted during execution
exit message (This is where e bath job gets DELETE/ENTRY'ed)

There is only a limited amount of time/rprocessing available when the error brancy is taken, so you need to work out what to do.

Something I've used previously:

$ set on
$ on error goto error
$! processing here
$ exit 1
$ error:
$ exit_status := '$status'
$ submit/nolog sys$manager:signal.com -
param=(procedure, exit_status)

and then leave the signal.com to work out how to check for succes/failure and notify accordingly.

It might be worth running some tests to see
whether this approach wioudfl be suitable.

Craig