Informix: procedure with output parameters??

Question

Hi all,

I searched a lot, but couldn't find anything.. I just want to ask if there's any way to create and call a procedure (Informix) with out parameters. I know how to return one or more values (for procedures and for functions), but this is not what I want. It would be really strange, if Informix does not allow output parameters..

Thanks in advance!

EDIT: Yes, I saw it's possible, but I still can't execute such procedure. For example:

  CREATE PROCEDURE mytest(batch INT,OUT p_out INT)  
  DEFINE inc INTEGER;  
  LET inc = 1;  
  LET p_out = 5;  
  END PROCEDURE;  

and what I receive is:

The routine mytest can not be resolved

and this happens only on executing functions with output parameters..

Answer 1

Yes, allow..

Check this link: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.netpr.doc/ininout.htm?resultof=%22%70%72%6f%63%65%64%75%72%65%22%20%22%70%72%6f%63%65%64%75%72%22%20%22%6f%75%74%22%20%22%70%61%72%61%6d%65%74%65%72%22%20%22%70%61%72%61%6d%65%74%22%20

Answer 2

Why do you need 'out' parameters? Informix procedures can return multiple values from a single call (or, in this case, a single value):

  CREATE PROCEDURE mytest(batch INT) RETURNING INT AS p_out;
      DEFINE inc INTEGER;
      DEFINE p_out INTEGER;
      LET inc = 1;
      LET p_out = batch + inc;
      RETURN p_out;
  END PROCEDURE;

There are only a limited number of places where you can use an OUT parameter. One is in a query - there is a name SLV (statement local variable) that turns up in some error messages. I believe there's a way to get to OUT parameters via Java (JDBC) too. AFAIK, other APIs do not allow it.

Code written for Informix assumes that it won't need output parameters. Code migrated to Informix from other (impoverished?) systems that do not provide multiple output values from a single procedure need to be rethought to work sensibly with Informix.