Informix stored procedure does not execute an INSERT

Article ID: 990
Last updated: 05 Feb, 2008
Article ID: 990
Last updated: 05 Feb, 2008
Revision: 1
Views: 9572
Posted: 05 Aug, 1997
by Dean J.
Updated: 05 Feb, 2008
by Dean J.
Problem


When executing a stored procedure from DBTools.h++ that has an INSERT, the INSERT is not executed when that stored procedure also uses a RETURN statement. For example, the stored procedure might look like this:
CREATE PROCEDURE foo(p INT) RETURNING INT; 
INSERT INTO bar VALUES(p);
RETURN 0;
END PROCEDURE;
When executed from a DBTools.h++ app using RWDBStoredProc, the row is not inserted. But when executing the stored procedure from DBACCESS, it works fine. Here is an example of what the DBTools.h++ code might look like.
RWDBStoredProc sp= db.storedProc(foo);
sp.execute();


Cause


We have observed that Informix stored procedures do not commit
 their results unless you be sure to read any result sets returned by
 that same stored procedure. What this means in the example above,
is your DBTools application must read the returned result(s) if you
wish the INSERT to be completed. Note that when this occurs
you do not see an error returned from Informix, simply your INSERT
does not execute. The reason DBACCESS works is because
DBACCESS does retrieve the result.

 




Action


If your stored procedure uses the statement
RETURN
you must read the returned result(s) from your DBTools.h+ application. Here is a DBTools.h++ example showing how to read a stored procedure result.
RWDBStoredProc mySp= myDb.storedProc(foo); 
RWDBResult myResult = mySp.execute();
RWDBTable myResTable = myResult.table();
RWDBReader myRdr = myResTable.reader();
int myInt;
while(myRdr())
{
myRdr >> myInt;
}
Or, if possible, removing the RETURN statement from your stored procedure should also fix it.
This article was:   Helpful | Not helpful
Report an issue
Article ID: 990
Last updated: 05 Feb, 2008
Revision: 1
Views: 9572
Posted: 05 Aug, 1997 by Dean J.
Updated: 05 Feb, 2008 by Dean J.

Others in this category