Re: How can I obtain SQLCODE in SPL ?

You would have thought that the following was possible: CREATE PROCEDURE system_call (command CHAR(500)) RETURNING CHAR(80); DEFINE result CHAR(80); LET result = '/tmp/' || 'system_call.' || DBINFO('sessionid'); SYSTEM command || ' > ' || result || ' 2>&1'; CREATE TEMP TABLE system_call (result CHAR(80)); LOAD FROM result INSERT INTO system_call; SYSTEM "rm " || result; FOREACH SELECT * INTO result FROM system_call END FOREACH; DROP TABLE system_call; RETURN result; END PROCEDURE; However, LOAD is not available in SPL with IDS 9.30. Why is this? Regards, Doug Lawry www.douglawry.webhop.org "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:pan.2003.11.26.11.04.47.445945.12806@bloomberg.net... > On Wed, 26 Nov 2003 09:50:31 -0500, Francisco Roldan wrote: > > You really cannot. Remember that SPL was never intended as a general purpose > programming language, just a way to can pre-optimized SQL or add some basic flow > control to SQL. Unlike the other major databases Informix has always had > several excellent development tools (ace/perform, 4GL, ESQL/C, JDBC, etc.) so > there was no need for a more generic programming facility in the engine as the > big 'O' once required. The external program will have to put its results into a > table for the SPL to retrieve. If this is more than trivial then likely it > should be an ESQL/C program/library function rather than an SPL. If it is > business rule related and you MUST have it available through the database AND > you have 9.xx consider writing the procedure/function as a Java or C UDF instead > then you will have access to any system calls or external program pipes that you > need. > > Art S. Kagel > > > BTW, how can i make a system call from SPL and get back the result from the > > executed system command ? > > > > Thanks in advance > > > > -----Mensaje original----- > > De: Art S. Kagel [mailto:kagel@bloomberg.net] Enviado el: Mi'rcoles, 26 de > > Noviembre de 2003 07:56 a.m. Para: informix-list@iiug.org Asunto: Re: How can > > I obtain SQLCODE in SPL ? > > > > > > On Wed, 26 Nov 2003 07:53:53 -0500, Marek Radzewicz wrote: > > > > > >> How can I obtain SQLCODE (and sql error message) inside SPL code in Informix > >> ? > >> dbinfo('sqlca.sqlcode') doesn't work ...> > You have to use EXCEPTION handling to retrieve the SQLCODE and ISAM error > > code: > > > > create procedure some_proc( arg1 int, ... ) returning int, int, int;> > > > DEFINE rtnsql INT; -- place holder for exception sqlcode setting > > DEFINE rtnisam INT; -- isam error code. Should be onpload exit status > > DEFINE final_result INT; > > > > ON EXCEPTION SET rtnsql, rtnisam > > ROLLBACK WORK; > > RETURN -1, rtnsql, rtnisam; > > END EXCEPTION; > > > > BEGIN WORK; > > LET final_result = 0; > > ..... > > COMMIT WORK; > > RETURN final_result, 0, 0; > > > > END PROCEDURE; > > > > If you prefer the EXCEPTION handler can simply do nothing other than set the > > variables and fall back to the mainline code where you can test them and > > handle the results yourself; > > > > Art S. Kagel > > > > sending to informix-list

"Doug Lawry" <lawry@nildram.co.uk> wrote > However, LOAD is not available in SPL with IDS 9.30. Why is this? AFAIK LOAD FROM was never available in SPL in any version.

This would work if you could use LOAD in SPL. We this problem a few years back well prior to 9 and instead of using temp tables we used real tables, passed a unique identifier to the system call and got the system call to insert the 'result' using the unique identfier. Very not pretty. I'd suspect that some clever C could probably solve this with 9 and UDRs Doug Lawry wrote: > > You would have thought that the following was possible: > > CREATE PROCEDURE system_call (command CHAR(500)) RETURNING CHAR(80);> DEFINE result CHAR(80); > LET result = '/tmp/' || 'system_call.' || DBINFO('sessionid'); > SYSTEM command || ' ? ' || result || ' 2??1'; > CREATE TEMP TABLE system_call (result CHAR(80)); > LOAD FROM result INSERT INTO system_call;> SYSTEM "rm " || result; > FOREACH SELECT * INTO result FROM system_call END FOREACH; > DROP TABLE system_call;> RETURN result; > END PROCEDURE; > > However, LOAD is not available in SPL with IDS 9.30. Why is this? > > Regards, > Doug Lawry > www.douglawry.webhop.org > > "Art S. Kagel" ?kagel@bloomberg.net? wrote in message > news:pan.2003.11.26.11.04.47.445945.12806@bloomberg.net... > ? On Wed, 26 Nov 2003 09:50:31 -0500, Francisco Roldan wrote: > ? > ? You really cannot. Remember that SPL was never intended as a general > purpose > ? programming language, just a way to can pre-optimized SQL or add some > basic flow > ? control to SQL. Unlike the other major databases Informix has always had > ? several excellent development tools (ace/perform, 4GL, ESQL/C, JDBC, etc.) > so > ? there was no need for a more generic programming facility in the engine as > the > ? big 'O' once required. The external program will have to put its results > into a > ? table for the SPL to retrieve. If this is more than trivial then likely > it > ? should be an ESQL/C program/library function rather than an SPL. If it is > ? business rule related and you MUST have it available through the database > AND > ? you have 9.xx consider writing the procedure/function as a Java or C UDF > instead > ? then you will have access to any system calls or external program pipes > that you > ? need. > ? > ? Art S. Kagel > ? > ? ? BTW, how can i make a system call from SPL and get back the result from > the > ? ? executed system command ? > ? ? > ? ? Thanks in advance > ? ? > ? ? -----Mensaje original----- > ? ? De: Art S. Kagel [mailto:kagel@bloomberg.net] Enviado el: Mi'rcoles, 26 > de > ? ? Noviembre de 2003 07:56 a.m. Para: informix-list@iiug.org Asunto: Re: > How can > ? ? I obtain SQLCODE in SPL ? > ? ? > ? ? > ? ? On Wed, 26 Nov 2003 07:53:53 -0500, Marek Radzewicz wrote: > ? ? > ? ? > ? ?? How can I obtain SQLCODE (and sql error message) inside SPL code in > Informix > ? ?? ? > ? ?? dbinfo('sqlca.sqlcode') doesn't work ... > ? ? You have to use EXCEPTION handling to retrieve the SQLCODE and ISAM > error > ? ? code: > ? ? > ? ? create procedure some_proc( arg1 int, ... ) returning int, int, int; > ? ? > ? ? DEFINE rtnsql INT; -- place holder for exception sqlcode > setting > ? ? DEFINE rtnisam INT; -- isam error code. Should be onpload exit > status > ? ? DEFINE final_result INT; > ? ? > ? ? ON EXCEPTION SET rtnsql, rtnisam > ? ? ROLLBACK WORK; > ? ? RETURN -1, rtnsql, rtnisam; > ? ? END EXCEPTION; > ? ? > ? ? BEGIN WORK; > ? ? LET final_result = 0; > ? ? ..... > ? ? COMMIT WORK; > ? ? RETURN final_result, 0, 0; > ? ? > ? ? END PROCEDURE; > ? ? > ? ? If you prefer the EXCEPTION handler can simply do nothing other than set > the > ? ? variables and fall back to the mainline code where you can test them and > ? ? handle the results yourself; > ? ? > ? ? Art S. Kagel > ? ? > ? ? sending to informix-list -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

On Thu, 27 Nov 2003 07:09:24 -0500, Doug Lawry wrote: > You would have thought that the following was possible: > > CREATE PROCEDURE system_call (command CHAR(500)) RETURNING CHAR(80);> DEFINE result CHAR(80); > LET result = '/tmp/' || 'system_call.' || DBINFO('sessionid'); SYSTEM > command || ' > ' || result || ' 2>&1'; CREATE TEMP TABLE system_call > (result CHAR(80)); LOAD FROM result INSERT INTO system_call; SYSTEM "rm " > || result; > FOREACH SELECT * INTO result FROM system_call END FOREACH; DROP TABLE > system_call; > RETURN result; > END PROCEDURE; > > However, LOAD is not available in SPL with IDS 9.30. Why is this? <SNIP> Doug: LOAD and UNLOAD are verbs implemented by dbaccess, ISQL, and 4GL and are NOT available from the engine at all. That's why it does not work in SPL. Art S. Kagel

My question was rhetorical - LOAD should be in SPL! "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:pan.2003.11.28.11.00.41.228665.12806@bloomberg.net... > >> On Thu, 27 Nov 2003 07:09:24 -0500, Doug Lawry wrote: >> ... >> However, LOAD is not available in SPL with IDS 9.30. Why is this? > > Doug: > > LOAD and UNLOAD are verbs implemented by dbaccess, ISQL, and 4GL and are NOT > available from the engine at all. That's why it does not work in SPL.