INQUIRE_SQL Examples
1. Execute some database statements, and handle errors by displaying the message and aborting the transaction.
exec sql whenever sqlerror goto err_handle;
exec sql select name, sal
into :name, :sal
from employee
where eno = :eno;
if name = 'Badman' then
exec sql delete from employee
where eno = :eno;
else if name = 'Goodman' then
exec sql update employee set sal = sal + 3000
where eno = :eno;
end if;
exec sql commit;
...
err_handle:
exec sql whenever sqlerror continue;
exec sql inquire_sql (:err_msg = errortext);
print 'INGRES error: ', sqlca.sqlcode;
print err_msg;
exec sql rollback;
2. The following example demonstrates the use of the WHENEVER statement for intercepting trace messages from a database procedure. The messages are written to a trace file.
exec sql whenever sqlerror stop;
exec sql whenever sqlmessage call trace_message;
exec sql execute procedure proc1 into :retstat;
...
/* Inside the "trace_message" host language
procedure */
exec sql inquire_sql (:msgnum = messagenumber,
:msgtxt = messagetext);
if (msgnum = 0) then
print logfile, msgtxt;
else
print logfile, msgnum, '-'msgtxt;
end if;
Last modified date: 11/09/2022