#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    37
    Rep Power
    10

    Transactions not working


    I am connecting to a firebird db using php. I am just testing things atm and need to get transactions working. I have the following code

    Code:
    $dbh = ibase_pconnect ($host, $username, $password);
    
    $sql1 =  "INSERT INTO customer_invoice (INVOICE_NO,ID) 
              VALUES (9999, $NextInvID)"; 
    
    $sql2 =  "INSERT INTO customer_invoice_items (ITEM_CODE, INVOICE_NO, STOCK_CODE, STOCK_DESC, INV_ID) 
              VALUES ($NextInvItemID,9999, 'PROD1', 'Product Test',$NextInvID)";
    
    $trans = ibase_trans( $dbh );
    $q1 = ibase_prepare($trans,$sql1);
    ibase_execute($q1);
    $q2 = ibase_prepare($trans,$sql2);
    ibase_execute($q2);
    
    if( ibase_commit($trans) )
    {
      echo "Order Saved";
    }
    else
    { 
      ibase_rollback($trans);
    }
    ibase_free_query($q1);
    ibase_free_query($q2);
    The first time this script is run everything works as expected and a record is inserted into the customer_invoice and customer_invoice_items tables.

    When it is run the second time, it should fail because there is already a record in CUSTOMER_INVOICE with invoice_no 9999 (the one inserted the first time). I do get a message saying there is a duplicate BUT the record for CUSTOMER_INVOICE_ITEM is still inserted.

    My understanding of transactions were that all of the queries have to succeed before records are commited to the db.

    Can anyone see a problem with the code or point me to a sample using transactions. Many thanks.

    PS using Apache 2.2, PHP 5.2.13?, FB 2.1.2
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    353
    Originally Posted by raytechpro.
    Can anyone see a problem with the code or point me to a sample using transactions.
    Did you disable autocommit?

    Comments on this post

    • clivew agrees : My thoughts exactly!
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    25
    Rep Power
    0
    Originally Posted by raytechpro.
    ...My understanding of transactions were that all of the queries have to succeed before records are commited to the db....
    That is not true. If you start a transaction then you can commit/rollback it anytime. It doesn't matter, that SQL commands where failed. If you commit, then all the changes made by the successfull SQL commands will be commited.

    Comments on this post

    • shammat agrees : Good point
    Last edited by E_Pluribus_Unum; April 22nd, 2010 at 01:31 AM. Reason: typo
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    37
    Rep Power
    10
    Originally Posted by shammat
    Did you disable autocommit?
    No, I haven't. Never come across autocommit before. Had a look on google and closest I can find is the command

    set autoddl off

    which I have now done in isql. Not sure if this is what you mean? It still didn't work doing this.

    Originally Posted by E_Pluribus_Unum
    That is not true. If you start a transaction then you can commit/rollback it anytime. It doesn't matter, that SQL commands where failed. If you commit, then all the changes made by the successfull SQL commands will be commited.
    Oh, looks like I have misunderstood how it works then. So, what I should do is test the result of each query. If it fails then rollback. If all queries are ok then commit?

    I will try this. Thanks
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    353
    Originally Posted by raytechpro.
    No, I haven't. Never come across autocommit before. Had a look on google and closest I can find is the command
    That is controlled by your "driver layer", so in your case the PHP connector.

    Some languages default to autocommit on (which means every statement is automatically committed even when you do not issue a commit), some languages default to autocommit off.

    Consult the manual of the PHP connector to find out how it behaves.

    So, what I should do is test the result of each query. If it fails then rollback. If all queries are ok then commit?
    Correct.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    37
    Rep Power
    10
    Seems to work now. Thanks again.

IMN logo majestic logo threadwatch logo seochat tools logo