Re: Can I run stored procedures from 4gl?

>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>