objSQL Transactions

Transactions are a means to ensure a database's integrity when one or more queries fail. Queries are usually grouped together in logical blocks and are committed or rolled back when certain conditions are met. In general, any statement that alters a database record such as insert, delete and update is considered a transactional statement. Statements such as create database, create table, drop database, etc. should be executed outside of a transaction block.

• Call the obj_transaction() method to initiate a transaction and disable autocommit.

Returns: Transaction instance or false on failure.

• Call the obj_rollback() method to rollback a transaction block.

Returns: True on success or false on failure.

• Call the obj_commit() method to commit a transaction block. Failure to call obj_commit() cancels the entire transaction.

Returns: True on success or false on failure.

See also: Savepoints

<?php

try
{
    $trans = $dbh->obj_transaction();
    
    $data = array( "color" => "blue",
                   "type"  => "leather",
                   "price" => 36.95 );
                   
    $rs = $dbh->obj_update( "products", $data, "prod_id=21" );
    
    if ( $dbh->obj_error() )
    {
        $trans->obj_rollback();
        
        throw new Exception( $dbh->obj_error_message() );
    }
        
    $data = array( "color" => "red",
                   "type"  => "leather",
                   "price" => 32.95 );
                   
    $rs = $dbh->obj_update( "products", $data, "prod_id=49" );
    
    if ( $dbh->obj_error() )
    {
        $trans->obj_rollback();
        
        throw new Exception( $dbh->obj_error_message() );
    }
        
    $trans->obj_commit();    

}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
}

?>

Savepoints

Savepoints allow you set a point within a transaction to rollback to if a failed condition is met without affecting any work done in the transaction before the savepoint was declared.

• Call the obj_savepoint() method to set a savepoint. The name argument is required.

Returns: True on success or false on failure.

• The obj_rollback() method takes an optional argument which is the named savepoint. Rolling back a transaction without a savepoint name cancels the entire transaction.

Returns: True on success or false on failure.

<?php

//usage: $trans->obj_savepoint( str $name );
//usage: $trans->obj_rollback( str $name );

try
{
    $trans = $dbh->obj_transaction();
    
    $data = array( "color" => "blue",
                   "type"  => "leather",
                   "price" => 36.95 );
                   
    $rs = $dbh->obj_update( "products", $data, "prod_id=21" );
    
    if ( $dbh->obj_error() )
    {
        $trans->obj_rollback();
        
        throw new Exception( $dbh->obj_error_message() );
    }
    
    $trans->obj_savepoint( "svp1" );
        
    $data = array( "color" => "red",
                   "type"  => "leather",
                   "price" => 32.95 );
                   
    $rs = $dbh->obj_update( "products", $data, "prod_id=49" );
    
    if ( $dbh->obj_error() )
    {
        $trans->obj_rollback( "svp1" );
        $trans->obj_commit();
        
        throw new Exception( $dbh->obj_error_message() );
    }
        
    $trans->obj_commit();    

}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
}

?>

Back to top