RE: How can I obtain SQLCODE in SPL ?
What about using a SYSTEM call, to the dbload command,
after creating the dbload file ...
-----Mensaje original-----
De: Doug Lawry [mailto:lawry@nildram.co.uk]
Enviado el: Jueves, 27 de Noviembre de 2003 06:09 a.m.
Para: informix-list@iiug.org
Asunto: 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
sending to informix-list
Indeed, but that's massively slower than a little datablade as Art suggests.
Regards,
Doug Lawry
www.douglawry.webhop.org
"Francisco Roldan" <froldan@5b.com.gt> wrote in message
news:bq59ei$koh$1@terabinaries.xmission.com...
>
>
> What about using a SYSTEM call, to the dbload command,
> after creating the dbload file ...
>
>
> -----Mensaje original-----
> De: Doug Lawry [mailto:lawry@nildram.co.uk]
> Enviado el: Jueves, 27 de Noviembre de 2003 06:09 a.m.
> Para: informix-list@iiug.org
> Asunto: 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
>
>
> sending to informix-list