I'm looking at the quote method, the pod says not to be used with placeholders and bindvariables.
From the example shown I dont see how I could use it to dynamically build up a SQL query anyway. To better explain my problem. I have a form with fields foo bar and baz. In the perl code I have a sql query "select col1, col2, col3 from tab_name where foo=? and bar=? and baz=?". Is there a way I can remove parameters from the where clause, while still doing things safely, or do I need to learn a better way of dynamically generating a sql query based on input?
| [reply] [Watch: Dir/Any] |
You generally use placeholders or DBI's quote but usually not both. I never use quote myself. placeholders are generally the way to go because you can often prepare the SQL once with the placeholders and execute as many times as you like - thus saving parsing and preparing of the SQL repeatedly.
When DBI says don't quote placeholders it means don't run the quote method on placeholders i.e., don't do:
$s = $h->prepare(/select * from table where a = ?/);
$s->execute($h->quote("myvalueforcolumna"));
You use quote when you are building all the SQL yourself and not using placeholders so the above would be:
$s = $h->prepare(q/select * from table where a = / . $h->quote("myva
+lueforcolumna");
Try and stick with placeholders. I don't know your schema but you can usually do something like:
select col1, col2, col3 from tab_name where (? is null or foo = ?) a
+nd (? is null or bar = ?) and (? is null or baz = ?)
then pass each value for foo, bar and baz twice
| [reply] [Watch: Dir/Any] [d/l] [select] |
I never use quote myself.
Actually, quote() is the more general solution. I've run into quite a few different problems trying to use placeholders. And I've never noticed any performance problem with my failure to re-use prepared queries. I've actually had more problems due to people being too aggressive about trying to re-use prepared queries such as using prepare_cached() way too much.
A significant fraction (perhaps a majority) of the environments where I've done significant DBI work were such that placeholders had no performance impact at all. For example, the default with DBD::mysql seems to still be to not do server-side prepares.
At more than one recent employer I've experienced upgrading DBD::Pg such that prepares started happening server-side and then having to disable server-side prepares due to more than one resulting problem (the query planner got significantly stupider in a few important cases, it was incompatible with PgBouncer or other middle-layer tools, and other problems).
We had some breakage at PerlMonks because "limit ?" worked fine on older versions of DBD::mysql but then stopped working after some ugprade.
And I've had significant problems with placeholders when using DBD::ODBC. The first batch of problems were around certain data type checking such that "where somedate = '2011-01-01'" is no problem but "where somedate = ?" plus passing '2011-01-01' to execute() gave some "type mismatch" error.
The scariest problem was using DBD::ODBC and having "where somedate between cast(? as datetime) and cast(? as datetime)" work without emitting any errors or warnings but then returning the wrong data. But, of course:
join ' ',
...
"where somedate between", $dbh->quote($start),
"and", $dbh->quote($end),
worked exactly as expected (whether "cast( ... as datetime)" was included or not).
So, I like being able to use placeholders. But way too often that actually requires being able to disable server-side prepares which then just boils down to $dbh->quote() except that the DBD module is doing that call for you (and thus removes your claimed advantage).
Actually, the majority of my experience using DBI is via some wrapper (quite a few different ones, some extremely thin, some only moderately thin) that takes my column names and column values and assembles at least some of the SQL for me.
| [reply] [Watch: Dir/Any] [d/l] |
Thanks for this reply. I just tried this in sqlplus and I'm starting to understand your advice. Thank you again.
I'll test this with DBI in a while. Is there any way via DBI or SQL plus I can easily benchmark two statements? Thank you.
| [reply] [Watch: Dir/Any] |
You can also stick with placeholders by doing something like:
my @where, @params);
if (defined $foo) {
push @where, "foo = ?";
push @params, $foo;
}
if (defined $bar) {
push @where, "bar = ?";
push @params, $bar;
}
my $sth = $dbh->prepare($sql . " WHERE ". join(" AND ", @where));
$sth->execute(@params);
| [reply] [Watch: Dir/Any] [d/l] |