Unanswered: Rollback not working with stored procedures
We're currently running PHP 5.3 on linux, and we're connecting to DB2 on the iSeries version 6.1.
We want to able to use commitment control in our PHP code. I've successfully been able to rollback an insert statement executed within the php code, however, when I try putting that same insert statement into a stored procedure and call the procedure from PHP, it won't rollback the changes.
Here's some sample code that will rollback the insert:
However, when I change the $sql variable above to call a procedure using:
$stmt = "call test_procedure ()", with test_procedure having the following definition, it won't rollback:
CREATE PROCEDURE test_procedure()
MODIFIES SQL DATA
COMMIT = *NONE
INSERT INTO some_table values ('test');
I have read the following commit behaviour, but I'm not sure how to set the autocommit off for the stored procedure.
If I explicitly set COMMIT = *CS (or any setting other setting than *None), the procedure will rollback. However, I don't know if this is the right approach. I would think the procedure should inherit the isolation level from the client.
I’ve read some other similar threads, but haven’t seen any solution.
Thanks for the link. Basically what I got out of that was to ensure the stored procedure runs in the same activation group. I verified that my procedure is already using *Caller for the activation group.