RE: SPL and system() - getting the return code.

For future reference, on the informix site there is a datablade you can install that gives you dynamic sql in a SP. see here: http://www-128.ibm.com/developerworks/db2/zones/informix/library/samples/db_downloads.html ________________________________ From: informix-list-bounces@iiug.org on behalf of Jack Parker Sent: Thu 1/18/2007 3:11 PM To: informix-list@iiug.org 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; _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list