objSQL Prepared Statements

A prepared statement is a pre-compiled SQL query that returns a resource/object. This resource can then be used to execute an SQL statement multiple times and helps reduce some overhead on the server. Prepared statements use parameterized values which are executed after the prepared statement is registered and are a proven deterent against SQL injection as the query is not exposed repeatedly.

Prepared statements allow you to embed markers within the SQL statement that will be replaced with bound parameter data. The obj_prepare_statement() method utilizes a question mark (?) as its parameter binding marker and is called directly from the objSQL class. A prepared statement can be executed as a stand-alone SQL query without any binding parameters.

• Calling the obj_prepare_statement() registers the prepared statement. The $query argument is required.
• The obj_bind() method processes the binding parameter and requires a single value argument.
• The binding markers and methods are executed in the order they appear. You must sync the order of the binding markers (?) with its respective binding method.
• The obj_execute() method executes the prepared statement and take no arguments.

Returns: obj_prepare_statement() - Statement resource/object or false on failure.

Returns: obj_execute() - Result or resultset resource/object or false on failure.

See also: Freeing resources | Multiple prepared statements | SQL Server and prepared statements

<?php

//usage: $dbh->obj_prepare_statement( $query );

try
{
    $stmt = $dbh->obj_prepare_statement( "update mytable set dept=? where location=?" );
    
    $stmt->obj_bind( 'Sales' );
    $stmt->obj_bind( 'Chicago' );  
    
    $rs = $stmt->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>   

Freeing Prepared Statement Resources

A prepared statement can be executed multiple times, but you must free the statement resources between statement executions. Failure to do so will throw an exception.

• The obj_free_statement() method releases the previously executed prepared statement resource.

Returns: True on success or false on failure.

• The obj_close_statement() method closes the prepared statement and releases all resources. Attempting to execute a prepared statement after calling obj_close_statement() will throw an exception.

Returns: True on success or false on failure.

<?php

try
{
    $stmt = $dbh->obj_prepare_statement( "update mytable set dept=? where location=?" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    $stmt->obj_bind( 'Sales' );
    $stmt->obj_bind( 'Chicago' );  
    
    $rs = $stmt->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();  
    
    $stmt->obj_free_statement();
    
    $stmt->obj_bind( 'Advertising' );
    $stmt->obj_bind( 'New York' );  
    
    $rs = $stmt->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();   
    
    $stmt->obj_free_statement();
    
    $stmt->obj_bind( 'Human Resources' );
    $stmt->obj_bind( 'Atlanta' );  
    
    $rs = $stmt->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();  
    
    $stmt->obj_close_statement();
    
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>   >

Back to top

Multiple Prepared Statements

A prepared statement can be executed multiple times and you can register as many statements as required.

<?php

try
{
    $stmt1->obj_prepare_statement( "update mytable set dept=? where location=?" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    $stmt2->obj_prepare_statement( "select id from mytable" );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );

    $stmt1->obj_bind( 'Sales' );
    $stmt1->obj_bind( 'Chicago' );  
    
    $stmt1->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    $stmt1->obj_free_statement();

    $rs = $stmt2->obj_execute();
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    echo $rs->obj_num_rows(); 

    $stmt2->obj_free_statement();

    $stmt1->obj_bind( 'Advertising' );
    $stmt1->obj_bind( 'New York' );  

    $stmt1->obj_execute();
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );

    $rs = $stmt2->obj_execute();
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    echo $rs->obj_num_rows();   

    $stmt1->obj_close_statement();
    $stmt2->obj_close_statement();
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>    

Back to top

SQL Server and Prepared Statements

Prepared statements on SQL Server do not use a binding method and instead require that you declare your parameter variables in an array with a reference before registering the prepared statement.

• The obj_prepare_statement() method takes a second argument where you register your parameter variables. All other database types ignore this argument.
• Declare the parameter values in the obj_bind() method and execute the statement as you normally would.
• If you are planning to support SQL Server, the following example works with all supported database types.

<?php

try
{
    $params = array( &$var1, &$var2 );
    
    $stmt = $dbh->obj_prepare_statement( "update mytable set dept=? where location=?", $params );
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    $stmt->obj_bind( $var1="Sales" );
    $stmt->obj_bind( $var2="Chicago" );  
    
    $rs = $stmt->obj_execute();  
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
        
    echo $rs->obj_affected_rows();   
    
    $stmt->obj_close_statement();
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>        

Back to top