Re: Calling SPL from informix 4GL w/ returns

Handling stored procedures from I4GL should really be in the FAQ, but it isn't in the version 2.2 FAQ, so it bears repeating. Kerry, please would you consider adding what follows to the FAQ -- edit as you think necessary. Thanks. Paul, As a general rule, you cannot prepare INTO clauses such as "SELECT * INTO x, y, z FROM Somewhere". An "INTO TEMP" clause is different from a plain INTO clause. You also cannot prepare USING clauses, as in "OPEN c_cursor USING x, y, z". That means your prepare was bound to fail with a syntax error. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >From: marvin@ixc.net (Paul Marvin) >Date: 18 Aug 1995 00:23:43 GMT >X-Informix-List-Id: <news.16342> > >I am exploring using SPL to store functions that preform specific tasks and >return a result. These fuctions i currently store in a library. I am >hoping that by making them SPL code that I can save the time a 4GL uses to >verify the SQL's involved in the task. What i am having trouble doing is >getting the SQL procedure to execute and return a value. > >sample code block that i am currently trying: > >main > >define v float >define s char(40) > >let s = "execute procedure p() into v" >prepare e_SPL from s >execute e_SPL >display v > >end main > >help! >thanks =========================================================================== Date: Tue, 29 Jun 93 14:39:54 GMT From: johnl@informix.com (Jonathan Leffler) Subject: Re: Can I run stored procedures from 4gl? X-Informix-List-Id: <list.2440> >From: uunet!academ01.mty.itesm.mx!rromero (Ricardo Javier Romero Elizondo) >Subject: Can I run stored procedures from 4gl? >Date: 28 Jun 93 15:23:23 GMT >X-Informix-List-Id: <news.3659> I have slightly re-worded Ricardo's question, and he obviously knew some of the answer, though not quite all of it. >Can stored procedures be executed from 4gl? And if so, how can I do it, >and how can I handle the returned values from a procedure? More FAQ material: Yes, you can execute stored procedures from I4GL 4.10 (and earlier). You cannot simply write EXECUTE PROCEDURE; you have to prepare the statement: PREPARE p_exec FROM "EXECUTE PROCEDURE procname(?,?,?)" What you do next depends on whether the procedure returns any values or not. If it does not, then you can simply use: EXECUTE p_exec USING value01, value02, value03 If it does return parameters, then you should declare a cursor for the statement: DECLARE c_exec CURSOR FOR p_exec OPEN c_exec USING value01, value02, value03 WHILE STATUS = 0 FETCH c_exec INTO return01, return02, return03, return04 IF STATUS != 0 THEN EXIT WHILE END IF ... END WHILE CLOSE c_exec Obviously, you can either hardwire the parameters into the procedure argument list or have an argument-less procedure, and in both cases, you can avoid the USING clause, and you can use a FOREACH loop to fetch the data. If the procedure can raise exceptions, then you do not have to worry about them as return values, because the values you supply in the RAISE EXCEPTION clause will be placed in SQLCA.SQLCODE (and thence into STATUS) and SQLCA.SQLERRD[2] and SQLCA.SQLERRM. You should of course be using error -746 for returning your own error messages. The 4.10 manuals state that you cannot prepare the EXECUTE statement. This is still correct, because EXCECUTE PROCEDURE is a different statement from the EXECUTE statement. I think that summarises all the important points. Yours, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>