As a rule of thumb, you can only use SQL placeholders when the database can still come up with a query plan. Using a different table prevenets your database from coming up with a sensible query plan.

A more concise way to say this is that the placeholders can only stand in place for values, not for arbitrary syntax constructs.

You will have to construct the SQL by creating a string and replacing the variable parts in the string yourself.

There are some query builders, like SQL::Abstract, or DBIx::PreQL. In my experience, I found query builders to be too tedious, as I usually have to fight them too hard to get the SQL I want them to generate. I haven't used DBIx::PreQL, but it should allow you a more parametrized approach.

> There are some query builders, like SQL::Abstract, or DBIx::PreQL.

interesting any others like this and DBIx::Abstract ?

What I especially like about SQL::Abstract is that the problem of variable numbers of placeholders is addressed, it's no fun to generate strings like (?,?,?,?) for IN statements.

> In my experience, I found query builders to be too tedious, as I usually have to fight them too hard to get the SQL I want them to generate.

Maybe this could be addressed with an inner DSL in Perl ( DBIx::PreQL seems to be an outer DSL) .

Let's talk about it at our next meeting. :)

Cheers Rolf
_{(addicted to the Perl Programming Language and ☆☆☆☆ :)
Je suis Charlie!}

What I especially like about SQL::Abstract is that the problem of variable numbers of placeholders is addressed, it's no fun to generate strings like (?,?,?,?) for IN statements.

Hmm, interesting. I'll have to take a closer look at that module. I had previously been building strings like this:

my $insertstring = '['.join('],[', @sqlcolumns).']';
my $valuestring  = join(',', map {'?'} @sqlcolumns);
my $query        = "INSERT INTO table ($insertstring) VALUES ($valuest
+ring)";
[download]

Which is, as you say, tedious.

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.