Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2008
    Posts
    6

    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:

    $options = array ('autocommit' => DB2_AUTOCOMMIT_OFF);
    $conn = db2_connect (...)

    $sql = "insert into some_table values('test')";
    $stmt = db2_prepare($conn, $sql);
    $result = db2_execute($stmt);
    db2_rollback($conn);
    db2_close($conn);

    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()
    LANGUAGE SQL
    MODIFIES SQL DATA
    COMMIT = *NONE
    BEGIN
    INSERT INTO some_table values ('test');
    END ;


    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.
    Any ideas?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2random View Post
    I have read the following commit behaviour, but I'm not sure how to set the autocommit off for the stored procedure.
    It might be worth it to check System i manuals at SQL CALL statement (stored procedures)

  3. #3
    Join Date
    Oct 2008
    Posts
    6
    Quote Originally Posted by n_i View Post
    It might be worth it to check System i manuals at SQL CALL statement (stored procedures)
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •