[Copying the list on a reply to a private email, with permission]
A placeholder consists of a single question mark ("?") in your query
statement. It replaces a single value in your SQL statement. For example:
Without placeholders:
INSERT INTO MYTABLE VALUES ('Mark', 'Nutter', 'foo', 'bar', 'baz')
With placeholders
INSERT INTO MYTABLE VALUES (?, ?, ?, ?, ?)
Your code looks like this:
> my $query1=<<"QUERY";
> INSERT INTO DIFFAMNTS VALUES ($1,$2,$3,$4,$5,$6,$7,$8) # ARE THESE
> CORRECT???
> QUERY
This is correct, but it does not use place holders. At run-time, Perl does
a string substitution, so if, say, $1 is equal to "Mark", $2 is equal to
"Nutter", and so on, then the query that gets passed to DBI is
INSERT INTO DIFFAMNTS VALUES (Mark,Nutter,foo,bar,baz,...)
Now, if your columns are non-numeric, that query is not correct, because you
need to wrap your non-numeric data values in single quotes, like this:
INSERT INTO DIFFAMNTS VALUES ('Mark','Nutter','foo','bar','baz',...)
What's cool about placeholders is that you can stop worrying about the
difference between quoted and unquoted values -- you don't need to use
quotes at all with placeholders. This comes in real handy if you ever need
to insert large, multi-line values into a single database column:
my $query = <<EOM;
INSERT INTO PAGES (URL, PAGE) VALUES (?, ?)
EOM
my $sth = $dbh->prepare($query) or die $DBI::errstr;
$sth->execute($url, $page) or die $DBI::errstr;
Now the $page variable can include newlines, quotes, etc, and you don't have
to worry about it. The alternative would blow up in your face:
my $query = "INSERT INTO PAGES VALUES($url, $page)"; # Boom!
Behind the scenes what's happening is that, with placeholders, key pieces of
your data are being processed by DBI/DBD rather than by the Perl language
itself. For more sophisticated databases engines like Oracle, placeholders
are more efficient than query strings with embedded values. Here's how:
# Ordinary query
for $bar (qw(Eeny Meeny Miny Mo))
{
$query = EOM;
INSERT INTO FOO VALUES ('$bar')
EOM
$sth = $dbh->prepare($query);
$sth->execute();
}
Each time through the loop, Perl interpolates the value of $bar into the
query string, and the database engine gets a new query string that is
different from all previous strings:
INSERT INTO FOO VALUES ('Eeny')
INSERT INTO FOO VALUES ('Meeny')
INSERT INTO FOO VALUES ('Miny')
INSERT INTO FOO VALUES ('Mo')
The database engine has to parse each SQL query, which takes a certain
amount of time. Once the query is parsed, then the database actually moves
the data from the input buffer into the actual tables. Contrast this with
the placeholder approach:
# Query with placeholders
$query = EOM;
INSERT INTO FOO VALUES (?)
EOM
$sth = $dbh->prepare($query);
for $bar (qw(Eeny Meeny Miny Mo))
{
$sth->execute($bar);
}
Thanks to placeholders, we were able to move the prepare() statement outside
the loop. The database engine only parses the query once (in the prepare()
statement). Then, in the execute() statement, each value is simply copied
to the database engine's input buffer, and moved straight to the table. For
products that cache queries (e.g. Oracle), this can amount to a significant
time savings. Not all database engines support placeholders, so your
mileage may vary. I believe some DBD packages fake it by internally doing a
plain old string interpolation, so you should be able to safely use
placeholders in any DBI application -- worst case it would be no better than
not using placeholders, so it's a good bet.
Limitations: You can't use placeholders for table or column names.
Cheers.
Mark Nutter
Manager, Internet Applications Development
Marconi
[EMAIL PROTECTED]
It's not necessarily an advantage to have better brakes than the guy behind
you.
