Possible to use named placeholders in DBI's selectcol_arrayref & Co.?

Question

Is it somehow possible to use named placeholders where DBI allows @bind_values? E. g., I would like to make statements like:

my $s = $DB->selectcol_arrayref ("SELECT a FROM b
                                  WHERE c = ? OR d = ? OR e = ?;",
                                  {},
                                  $par1, $par2, $par1) or
        die ($DB->errstr ());

less prone to mistakes. I'm using DBD::Pg and DBD::SQLite.

Sublease

Placeholder support (and what sorts of placeholders are supported) depends on the driver so I added some more tags. – Twobyfour

Is there any reason to think you can't? DBI says $ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values). Note that you can pass undef for the second arg instead of creating an empty hash. – Kadiyevka

@ikegami: Maybe you could explain how to use named placeholders in the @bind_values arguments to any of the DBI methods. If you're using prepare/bind_param/execute it is easy but I've never figured out how to do it without a bunch of explicit bind_param calls. – Twobyfour

docstore.mik.ua/orelly/linux/dbi/ch05_03.htm – Santee

Answer 1

What sorts of placeholders (if any) are supported depends on the driver:

Placeholders and Bind Values

Some drivers support placeholders and bind values.
[...]
Some drivers also allow placeholders like :name and :N (e.g., :1, :2, and so on) in addition to ?, but their use is not portable.

But you're in luck, the PostgreSQL driver supports named or numbered parameters:

There are three types of placeholders that can be used in DBD::Pg. The first is the "question mark" type, in which each placeholder is represented by a single question mark character.
[...]
The method second type of placeholder is "dollar sign numbers".
[...]
The final placeholder type is "named parameters" in the format ":foo".

And the SQLite driver also supports them:

SQLite supports several placeholder expressions, including ? and :AAAA.

The downside is that you'll end up using bind_param a lot with the named parameters so you won't be able to use conveniences like selectcol_arrayref and $sth->execute(1,2,3) (Note: If anyone knows how to use named placeholders with execute I'd appreciate some pointers in a comment, I've never figured out how to do it). However, you can use the various forms of number placeholders (such as select c from t where x = $1 for PostgreSQL or select c from t where x = ?1 for SQLite).

Also be aware that PostgreSQL uses colons for array slices and question marks for some operators so sometimes the standard ? placeholders and :name named placeholders can cause problems. I've never had any problems with ? but I've never used the geometric operators either; I suspect that sensible use of whitespace would avoid any problems with ?. If you're not using PostgreSQL arrays, then you probably don't have to worry about array slices fighting with your :name named placeholders.

---

Executive Summary: You can't use named placeholders with selectcol_arrayref or similar methods that work with @bind_params. However, with SQLite and Postgresql, you can use numbered placeholders ($1, $2, ... for Postgresql or ?1, ?2, ... for SQLite) with the methods that work with @bind_params or you can use named placeholders (:name for both PostgreSQL and SQLite) if you're happy using the longer prepare/bind_param/execute/fetch sequence of methods and you'll have to be careful if you use PostgreSQL arrays in your queries.

Twobyfour

Thanks. Using "$1" & Co. in selectcol_arrayref isn't that fool-proof, so I'll probably use the prepare/bind_param/execute/fetch where I think it is necessary. – Sublease

@Tim: You could always write your own utility function that took an SQL string and a hash-ref, then it could prepare/bind_param/execute/fetch and return the summarized results. That sort of thing would be a reasonable compromise. – Twobyfour

Yep, but that's just another cog I would have to "develop" and maintain. In general, I stick as much as possible to the products of other people's brains who usually have thought much more about any corner cases that could be encountered than I could even imagine :-). – Sublease