I played around with the WHENEVER SQLCODE command. See my code example below. I put the line at the top of the command file and had it go to a particular label when it encounters an error. You could have this label do something cool like insert data into an error table (name of command file, person running the program, etc), or have it email the error to you.
I could probably incorporate this right away except for one thing it would report that would mess me up. If you do a "select into" and the value would be null, it will generate an error unless you have included an indicator variable. I do NOT as a rule use indicator variables, so I would have to add those to my "select into" commands. I hope this formats okay to you to understand. It was pretty interesting to test. Code below. Karen WHENEVER SQLERROR GOTO ErrorLab -- These are valid commands and pass through UPDATE clients SET client = client INSERT INTO clients (client) VALUES 'HELLO' SELECT ALL FROM clients WHERE client LIKE 'A%' -- This returns "warning - no rows", but does not trigger an error SELECT ALL FROM clients WHERE client LIKE 'YYY%' -- You get the "warning - value will be truncated", but it -- does not generate the SQLERROR INSERT INTO clients (client) VALUES 'VALUEISTOOLONG' -- There are 0 rows to update or delete, does not generate SQLERROR UPDATE clients SET client = client WHERE client = 'YYY' DELETE ROWS FROM clients WHERE client = 'YYY' -- This returns a null variable. If you include the indicator -- variable it does not trigger an error. If NO indicator variable -- then it will return a SQLERROR. SELECT hourrate INTO vRate IND iv1 FROM clients WHERE client = 'ERR' -- Both of these will fail but because they are not -- SQL commands they do not generate an error SET VAR vSum DOUBLE = (.vNoSuchVariable + .vAnotherVar) EDIT USING NoSuchForm -- This is a typo; it does not return an error UDPATE clients SET client = client -- Each of these will generate the WHENEVER error condition and -- immediately jump down to the ErrorLab label. I separately -- commented these out to test each one. -- This has an invalid column name UPDATE clients SET NoSuchColumn = 'HELLO' -- The variable for this insert does not exist INSERT INTO clients (hourrate) VALUES .vNoSuchVariable -- This tablename already exists CREATE TEMP TABLE testklt (AccountNo INT) -- Created a temp table with a column datatype that is invalid CREATE TEMP TABLE testwhenever (AccountNo TEXT 5) -- The "from" table does not exist PROJECT tmpProject FROM NoSuchTable USING ALL LABEL endprog RETURN LABEL ErrorLab PAUSE 2 USING 'An error has occurred' RETURN
