Writing MySQL-Based Programs - MySQL - page 85

INSERT INTO profile ( name , birth , color , foods , cats )
VALUES ( 'De\' Mont ',' 1973 - 01 - 12 ',NULL,' eggroll ',' 4 ' )
The Perl DBI placeholder mechanism provides quotes around data values when they
are bound to the statement string, so don't put quotes around the ? characters in the
string.
Note that the placeholder mechanism adds quotes around numeric values. DBI relies
on the MySQL server to perform type conversion as necessary to convert strings to
numbers. If you bind undef to a placeholder, DBI puts a NULL into the statement and
correctly refrains from adding enclosing quotes.
To execute the same statement over and over again, use prepare() once, then call
execute() with appropriate data values each time you run it.
You can use these methods for other types of statements as well. For example, the fol‐
lowing SELECT statement uses a placeholder to look for rows that have a cats value
larger than 2:
my $sth = $dbh -> prepare ( "SELECT * FROM profile WHERE cats > ?" );
$sth -> execute ( 2 );
while ( my $ref = $sth -> fetchrow_hashref ())
{
print "id: $ref->{id}, name: $ref->{name}, cats: $ref->{cats}\n" ;
}
High-level retrieval methods such as selectrow_array() and selectall_arrayr
ef() can be used with placeholders, too. Like the do() method, the arguments are the
statement string, undef , and the data values to bind to the placeholders. Here's an ex‐
ample:
my $ref = $dbh -> selectall_arrayref (
"SELECT name, birth, foods FROM profile WHERE id > ? AND color = ?" ,
undef , 3 , "green"
);
The Perl DBI quote() database- handle method is an alternative to using placeholders.
Here's how to use quote() to create a statement string that inserts a new row in the
profile table. Write the %s format specifiers without enclosing quotes because quote()
provides them automatically as necessary. Non- undef values are inserted with quotes,
and undef values are inserted as NULL without quotes:
my $stmt = sprintf ( "INSERT INTO profile (name,birth,color,foods,cats)
VALUES(%s,%s,%s,%s,%s)" ,
$dbh -> quote ( "De'Mont" ),
$dbh -> quote ( "1973-01-12" ),
$dbh -> quote ( undef ),
$dbh -> quote ( "eggroll" ),
$dbh -> quote ( 4 ));
my $count = $dbh -> do ( $stmt );