|
perlcgi has asked for the wisdom of the Perl Monks concerning the following question:
What's the neatest/coolest way to quote the elements of @line below.
AFAIK dbh->quote() only quotes a scalar.
Much obliged for any pointers.
my $sql_fmt = "INSERT INTO whatever VALUES( %s, %s, %s, %s, %s, %s, %s
+, %s, %s, %s, %s, %s, %s, %s)";
while (<INFILE>) {
my @line = (parse_csv ($_));
my $sql = sprintf ($sql_fmt, @line); #Elements of @line need to b
+e in quotes
$dbh->do($sql);
}
|
Re: DBI Quoting question
by btrott (Parson) on May 10, 2000 at 20:46 UTC
|
Use placeholders--then the values will automatically
be quoted.
my $NUM_FIELDS = 14;
my $sql = "INSERT INTO whatever VALUES (" . join(', ', ('?') x $NU
+M_FIELDS) . ")";
my $sth = $dbh->prepare_cached($sql);
while (<FILE>) {
my @line = (parse_cvs($_));
$sth->execute(@line);
}
$sth->finish;
If you want to be more flexible about the number of fields
in your file, do the prepare inside the loop (but make
sure you use prepare_cached so that you're not preparing
the same statement over and over):
while (<FILE>) {
my @line = (parse_cvs($_));
my $sql = "INSERT INTO whatever VALUES (" .
join(', ', ('?') x @line) . ")";
my $sth = $dbh->prepare_cached($sql);
$sth->execute(@line);
$sth->finish;
}
In fact, I like this last better. :) I'd recommend using
this, because it's nice to be input-independent. | [reply]
[d/l]
[select] |
Re: DBI Quoting question
by plaid (Chaplain) on May 10, 2000 at 20:46 UTC
|
The best is to use the prepare method with placeholders:
my $sth = $dbh->prepare("INSERT INTO whatever VALUES(?, ?, ?, ?, ?, ?,
+ ?, ?, ?, ?, ?, ?, ?, ?)")
while (<INFILE>) {
my @line = (parse_csv ($_));
$sth->execute(@line);
}
This has the benefit of taking care of the quoting for you,
but also of adding a performance increase, as just having
to prepare the query once will save some time, as opposed
to the DBI 'do' subroutine, which has to prepare it each
time. | [reply]
[d/l] |
Re: DBI Quoting question
by httptech (Chaplain) on May 10, 2000 at 20:47 UTC
|
You'll want to use placeholder variables instead, that will take care
of the quoting for you.
my $sql_fmt = "INSERT INTO whatever VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?
+)";
while (<INFILE>) {
my @line = (parse_csv ($_));
$dbh->do($sql_fmt,undef,@line);
}
You just need to make sure you always have the same number
of array elements in @line that you have '?' marks in $sql_fmt
or DBI will give you an error.
Also it's a good idea to format your SQL statements like:
INSERT INTO whatever (column_names) VALUES(?...)
That way all your insert statements won't break if you decide
to add to your database structure. | [reply]
[d/l]
[select] |
RE: DBI Quoting question
by lhoward (Vicar) on May 10, 2000 at 21:03 UTC
|
Placeholders are definitely the way to go. However there is another approach
that can be useful for situations where you can't use
palceholders. The only
example that comes to mind of a time where you can't use placeholders
is
"selet * from foo where bar in (?)"
where you want to specify a list with an unknown number of
elements for ?.
The DBH quote function will quote a variable apropriately for the
DB that your handle is connected to, escaping all necessary metacharacters, etc....
my $s=$dbh->quote($a);
| [reply]
[d/l] |
Re: DBI Quoting question
by BBQ (Deacon) on May 10, 2000 at 20:47 UTC
|
Maybe I'm looking at this the wrong way, but would you need to quote non-scalars if you were sticking to the prepare + execute method?
Updated:
Doesn't the $dbh->do() method skip all DBI internal SQL checking and send it "as-is" for the database to figure out? I remember reading this somehere, and it occured to me that by using the method you had stated above, you could be running into SQL syntax problems if the quoting part didn't work out. Just a thought.
#!/home/bbq/bin/perl
# Trust no1!
| [reply] |
|
|
| [reply] |
|
|
Whoa! Thanks guys - almost as fast as perldoc. Was the answer that obvious? Everyone gets a vote tomorrow.
| [reply] |
Re: DBI Quoting question
by princepawn (Parson) on May 10, 2000 at 22:20 UTC
|
If you can get away with it, use DBIx::Recordset instead of
DBI... DBI was designed to be an interface to databases,
not the interface for an application programmer to databases
Solves MANY of the irksome issues associated with DBI.
Check it out on CPAN
| [reply] |
RE: DBI Quoting question
by Anonymous Monk on May 10, 2000 at 23:04 UTC
|
my $sql_fmt = "INSERT INTO whatever VALUES( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)";
while (<INFILE>) {
my @line = (parse_csv ($_));
my $sql = sprintf ($sql_fmt, map {$dbh->quote($_)} @line); #Elements of @line need to be in quotes
$dbh->do($sql);
}
| [reply] |
|
|
