RE: How can I obtain SQLCODE in SPL ?

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

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