Perl DBI Bind Params Example

Perl DBI Bind Params Example:

Reference: http://docstore.mik.ua/orelly/linux/dbi/ch05_03.htm

A bind value is a value that can be bound to a placeholder declared within an SQL statement.
Instead of interpolating the generated value into the SQL statement, you specify a placeholder and then bind the generated value to that

It is important to remember that bind_ param( ) must be called before execute( )

So, why use bind values? What's the real differences between these and interpolated on-the-fly SQL statements?
For example, most large database systems feature a data structure known as the "Shared SQL Cache," into which SQL statements are stored along with additional related information such as a query execution plan.

The general idea here is that if the statement already exists within the Shared SQL Cache, the database doesn't need to reprocess that statement before returning a handle to the statement.
It can simply reuse the information stored in the cache. This process can increase performance quite dramatically in cases where the same SQL is executed over and over again

Eg., When you want to execute an SQL statement for thousands of times (with different interpolated values), then bind params concept will help in performance improvement.

1) Executing an SQL statement by interpolating or substituting the values
$sth = $dbh->prepare( "
            SELECT name, location
            FROM megaliths
            WHERE name = " . $dbh->quote( $siteName ) . "
        " );
$sth->execute() or die "SQL Error: $DBI::errstr\n";

2) Passing a single bind param in an SQL statement     
$sth = $dbh->prepare( "
            SELECT name, location
            FROM megaliths
            WHERE name = ?
        " );
$sth->bind_param( 1, $siteName );
$sth->execute() or die "SQL Error: $DBI::errstr\n";

3) Passing multiple bind params in an SQL statement      
$sth = $dbh->prepare( "
            SELECT name, location
            FROM megaliths
            WHERE name = ?
            AND mapref = ?
            AND type LIKE ?
        " );
$sth->bind_param( 1, "Avebury" );
$sth->bind_param( 2, $mapreference );
$sth->bind_param( 3, "%Stone Circle%" );       
$sth->execute() or die "SQL Error: $DBI::errstr\n";