SPL and system() - getting the return code.
Had to deal with this recently and checked the archives to see what was out
here - nothing. So I had to figure it out for myself. Here is a writeup of
that for the next fellow. Perhaps an FAQ entry. I am jotting this down
from memory/notes and while checking, I am not testing it as I go. Caveat
Emptor
j.
------------------------
One of the issues with SPL is that there is no dynamic sql. Your choices
are to generate and compile a new SPL on the fly or call an OS script
passing it parameters where you can handle the dynamic stuff. Fine, how do
you know if it worked?
Assuming that you are calling dbaccess at the OS level, the issue becomes
getting the info out of that to determine what happened. Ideally the
sqlcode and isam code. So first you have to trap this in the controlling
script:
SPL()
system('foo.sh') ('foo.bat') for you windoze folks.
------
sh(ell)
echo "some valid sql statement" > tmp.sql
dbaccess <database> tmp.sql 1>stdout 2>stderr
retcode=$?
if [ $retcode -ne 0 ]
then
retcode=`grep ":" stderr | awk -F":" '{print $1}' | head -1`
grep ":" stderr > loadfile
echo "load from loadfile insert into log_table(message);" > tmp.sql
dbaccess <database> tmp.sql
(this loads the actual error code and the text into a log table)
(you could set DBDELIMITER=":" and load into log_table(errcode,message) )
(this provides you both the sqlcode and the isam code).
fi
rm -f stderr stdout tmp.sql loadfile
exit $retcode
------
bat(ch) (if you are in the dos world)
echo some valid sql statement > tmp.sql -- note, no quotes
dbaccess <database> tmp.sql 1>stdout 2>stderr
set RETVAL=%ERRORLEVEL%
IF %RETVAL%==0 GOTO END -- not quite an if block
findstr ":" stderr > loadfile -- hey! it's grep!
echo load from loadfile insert into log_table(message); > tmp.sql
dbaccess <database> tmp.sql
(this loads the actual error code and the text into a log table)
(you could set DBDELIMITER=":" and load into log_table(errcode,message) )
(this provides you both the sqlcode and the isam code).
del stdout
del stderr
del tmp.sql
for /f "tokens=1 delims=:" %%i in (loadfile) do EXIT /B %%i
(this gets the first error (the sqlcode) and uses it as the return code)
(you can now trap it within the exception handling block)
(note the whacked out for /f syntax - this is one of the rare ways
of setting a variable from the contents of a file. Another
method is to write a batch file and execute it ala:
echo set RETVAL= > tmp.bat
copy tmp.bat + stderr tmp2.bat
tmp2.bat)
:END
del stdout
del stderr
del tmp.sql
del loadfile
EXIT /B %RETVAL% (the /B tells COMMAND to close the window)
-------
an issue in the Doze world is the window that pops open whenever you run a
batch file. Have not worked out a way around this yet.
Another issue is that we've left the loadfile hanging about.
Note that you need a log_table which can look something like:
create table log_table (
errcode integer,
message char(128), (or whatever)
msg_datetime datetime year to second default current year to second);
The exception block looks something like:
ON EXCEPTION IN (whatever codes)
SET p_errcode;
SELECT message
INTO err_msg
FROM log_table
WHERE msg_datetime=(SELECT max(msgdatetime) from log_table
WHERE errcode=p_errcode
AND errorcode=p_errcode; RAISE EXCEPTION -746,0, err_msg;
END IF;
END EXCEPTION;
I'm looking after the FAQ now and I'm happy to take content from anyone
- hope to have it up and running soon
Paul Watson
Tel: +44 1414161772
Mob: +44 7818003457
Web: www.oninit.com
Failure is not as frightening as regret.
Attend IDUG 2007 San Jose, North America
May 6-10, 2007
Visit http://www.iiug.org/conf for more information.
> -----Original Message-----
> From: Jack Parker [mailto:jack.parker4@verizon.net]
> Posted At: 18 January 2007 15:11
> Posted To: comp.databases.informix
> Conversation: SPL and system() - getting the return code.
> Subject: SPL and system() - getting the return code.
>
>
>
> Had to deal with this recently and checked the archives to
> see what was out here - nothing. So I had to figure it out
> for myself. Here is a writeup of that for the next fellow.
> Perhaps an FAQ entry. I am jotting this down from
> memory/notes and while checking, I am not testing it as I go.
> Caveat Emptor
>
> j.
>
> ------------------------
>
> One of the issues with SPL is that there is no dynamic sql.
> Your choices are to generate and compile a new SPL on the fly
> or call an OS script passing it parameters where you can
> handle the dynamic stuff. Fine, how do you know if it worked?
>
> Assuming that you are calling dbaccess at the OS level, the
> issue becomes getting the info out of that to determine what
> happened. Ideally the sqlcode and isam code. So first you
> have to trap this in the controlling
> script:
>
> SPL()
>
> system('foo.sh') ('foo.bat') for you windoze folks.
>
> ------
>
> sh(ell)
> echo "some valid sql statement" > tmp.sql dbaccess <database>
> tmp.sql 1>stdout 2>stderr retcode=$?
> if [ $retcode -ne 0 ]
> then
> retcode=`grep ":" stderr | awk -F":" '{print $1}' | head -1`
> grep ":" stderr > loadfile
> echo "load from loadfile insert into log_table(message);" > tmp.sql
> dbaccess <database> tmp.sql
> (this loads the actual error code and the text into a log table)
> (you could set DBDELIMITER=":" and load into
> log_table(errcode,message) )
> (this provides you both the sqlcode and the isam code).
> fi
> rm -f stderr stdout tmp.sql loadfile
> exit $retcode
>
> ------
>
> bat(ch) (if you are in the dos world)
> echo some valid sql statement > tmp.sql -- note, no quotes
> dbaccess <database> tmp.sql 1>stdout 2>stderr set RETVAL=%ERRORLEVEL%
> IF %RETVAL%==0 GOTO END -- not quite an if block
> findstr ":" stderr > loadfile -- hey! it's grep!
> echo load from loadfile insert into log_table(message); >
> tmp.sql dbaccess <database> tmp.sql
> (this loads the actual error code and the text into a log table)
> (you could set DBDELIMITER=":" and load into
> log_table(errcode,message) )
> (this provides you both the sqlcode and the isam code).
> del stdout
> del stderr
> del tmp.sql
> for /f "tokens=1 delims=:" %%i in (loadfile) do EXIT /B %%i
> (this gets the first error (the sqlcode) and uses it as
> the return code)
> (you can now trap it within the exception handling block)
>
> (note the whacked out for /f syntax - this is one of
> the rare ways
> of setting a variable from the contents of a file. Another
> method is to write a batch file and execute it ala:
> echo set RETVAL= > tmp.bat
> copy tmp.bat + stderr tmp2.bat
> tmp2.bat)
> :END
> del stdout
> del stderr
> del tmp.sql
> del loadfile
> EXIT /B %RETVAL% (the /B tells COMMAND to close the window)
>
> -------
>
> an issue in the Doze world is the window that pops open
> whenever you run a batch file. Have not worked out a way
> around this yet.
> Another issue is that we've left the loadfile hanging about.
>
> Note that you need a log_table which can look something like:
>
> create table log_table (
> errcode integer,
> message char(128), (or whatever)
> msg_datetime datetime year to second default current year> to second);
>
> The exception block looks something like:
>
> ON EXCEPTION IN (whatever codes)
> SET p_errcode;
> SELECT message
> INTO err_msg
> FROM log_table
> WHERE msg_datetime=(SELECT max(msgdatetime) from log_table
> WHERE errcode=p_errcode
> AND errorcode=p_errcode;> RAISE EXCEPTION -746,0, err_msg;
> END IF;
> END EXCEPTION;
>
Jack Parker wrote:
> Had to deal with this recently and checked the archives to see what was out
> here - nothing. So I had to figure it out for myself. Here is a writeup of
> that for the next fellow. Perhaps an FAQ entry. I am jotting this down
> from memory/notes and while checking, I am not testing it as I go. Caveat
> Emptor
>
> j.
>
> ------------------------
>
> One of the issues with SPL is that there is no dynamic sql. Your choices
> are to generate and compile a new SPL on the fly or call an OS script
> passing it parameters where you can handle the dynamic stuff. Fine, how do
> you know if it worked?
>
> Assuming that you are calling dbaccess at the OS level, the issue becomes
> getting the info out of that to determine what happened. Ideally the
> sqlcode and isam code. So first you have to trap this in the controlling
> script:
>
> SPL()
>
> system('foo.sh') ('foo.bat') for you windoze folks.
>
> ------
>
> sh(ell)
> echo "some valid sql statement" > tmp.sql
> dbaccess <database> tmp.sql 1>stdout 2>stderr
> retcode=$?
> if [ $retcode -ne 0 ]
> then
> retcode=`grep ":" stderr | awk -F":" '{print $1}' | head -1`
> grep ":" stderr > loadfile
> echo "load from loadfile insert into log_table(message);" > tmp.sql
> dbaccess <database> tmp.sql
> (this loads the actual error code and the text into a log table)
> (you could set DBDELIMITER=":" and load into log_table(errcode,message) )
> (this provides you both the sqlcode and the isam code).
> fi
> rm -f stderr stdout tmp.sql loadfile
> exit $retcode
>
> ------
>
> bat(ch) (if you are in the dos world)
> echo some valid sql statement > tmp.sql -- note, no quotes
> dbaccess <database> tmp.sql 1>stdout 2>stderr
> set RETVAL=%ERRORLEVEL%
> IF %RETVAL%==0 GOTO END -- not quite an if block
> findstr ":" stderr > loadfile -- hey! it's grep!
> echo load from loadfile insert into log_table(message); > tmp.sql
> dbaccess <database> tmp.sql
> (this loads the actual error code and the text into a log table)
> (you could set DBDELIMITER=":" and load into log_table(errcode,message) )
> (this provides you both the sqlcode and the isam code).
> del stdout
> del stderr
> del tmp.sql
> for /f "tokens=1 delims=:" %%i in (loadfile) do EXIT /B %%i
> (this gets the first error (the sqlcode) and uses it as the return code)
> (you can now trap it within the exception handling block)
>
> (note the whacked out for /f syntax - this is one of the rare ways
> of setting a variable from the contents of a file. Another
> method is to write a batch file and execute it ala:
> echo set RETVAL= > tmp.bat
> copy tmp.bat + stderr tmp2.bat
> tmp2.bat)
> :END
> del stdout
> del stderr
> del tmp.sql
> del loadfile
> EXIT /B %RETVAL% (the /B tells COMMAND to close the window)
>
> -------
>
> an issue in the Doze world is the window that pops open whenever you run a
> batch file. Have not worked out a way around this yet.
> Another issue is that we've left the loadfile hanging about.
>
> Note that you need a log_table which can look something like:
>
> create table log_table (
> errcode integer,
> message char(128), (or whatever)
> msg_datetime datetime year to second default current year to second);>
> The exception block looks something like:
>
> ON EXCEPTION IN (whatever codes)
> SET p_errcode;
> SELECT message
> INTO err_msg
> FROM log_table
> WHERE msg_datetime=(SELECT max(msgdatetime) from log_table
> WHERE errcode=p_errcode
> AND errorcode=p_errcode;> RAISE EXCEPTION -746,0, err_msg;
> END IF;
> END EXCEPTION;
Check out http://tinyurl.com/2mobc9 (which goes to a thread in c.d.i at
http://groups.google.com)
Beware that not all versions of DB-Access reliably return zero/non-zero
status for success/failure.
Note that the stuff executed this way is in a separate session, and
hence also in a separate transaction, and the separate session might run
foul of locks held by the other session (whereas statements in the main
session won't run foul of its own locks).
Worry about concurrent execution of this stuff - what happens if one
person runs and gets error -206 and someone else gets error -217? Two
people get -206 on different tables/columns? In the same second?
At some time, you will probably want to clean out your log table.
Since it has no index as defined, you will want it kept small.
If you use the exec datablade, be very careful - worry (a lot) about SQL
injection attacks. If you don't know what they are, ask Google.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/