in reply to Perl DBI '?' substitution with prepare/execute

The single quotes around the added parameter are inserted automatically.

Technically, that is (or at least should be) incorrect. Single quotes identify a text literal. Placeholder identify literals. If the placeholder added single quotes, the quotes would be a part of the literal, which is unlikely to be what is desired. It would also be problematic is there was a single quote in the datum.

But anyway, as Corion pointed out, placeholders cannot be used for object names. The reason for this is that placeholders provide data to the RDBMS, to be used in a prepared query, not object names. Nor does it make sense to do so. Aside from security concerns of dynamic sql, the reason for prepared queries is to use the same query each time. And not only from the code perspective, but from the RDBMS's perspective too. Permit me to explain:

When a query is submitted to the RDBMs (even via the "interface," as it is just a program using the CLI) it is handled by the database parser to validate the query. Then, (assuming the RDBMS supports it) it is handed to the query rewriter which makes the query more efficient, adds simple aliases, and whatnot. The query is then stored in the cache, (possibly given a key for reference) and finally executed. But, i skipped a step. Before being executed (and possibly the first or second step), the query cache is checked for the very same query (this may include whitespace!) If it is there, the old query is executed instead, possibly using cached results. It is more efficient overall.

Now, if the same query is run, but the data is different, the query is technically different. However, if the data is not inside the query, but instead provided via placeholders, the query is the same for different data, allowing the cached query to be used. When run once or twice, this is unlikely to help (other than the table data or indexes being now in memory, but that's not what we're talking about). Run thousands or millions of times and the difference can be significant.

This all works when the data is different. However, if the objects are different, this scheme would not help at all. Hence, little point in using a placeholder query for that anyway.

If you really want one query to rule them all, a view with union all could be concocted to return all the possibilities, with a where clause to limit it to what you want (and, depending on the RDBMS, the rewriter might remove the unused parts of the query) but this is confusing and wrong. It is better to simply write multiple queries when the table changes. If the column changes, return them all (by name) and only retrieve the ones you want from the data set. This is slightly inefficient due to pulling unneeded data, but unless the (unused) data is very large, unlikely to make much of a difference, and would perhaps be a worthwhile tradeoff.

  • Comment on Re: Perl DBI '?' substitution with prepare/execute