Can I use named placeholders in DBI selectcol_arrayref & Co.?

Question

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

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 errors. I am using DBD :: Pg and DBD :: SQLite.

Answer 1

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

Placeholders and Binding Values

Some drivers support placeholders and binding values.
[...]
Some drivers also allow you to fill in fields such as: name and: N (for example :: 1 ,: 2, etc.) 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 you can use in DBD :: Pg. The first is the “question mark” type, in which each placeholder is represented by one question mark.
[...]
The method of the second type of placeholder is "signed dollar signs."
[...]
The final placeholder type is "named parameters" in the format ": foo".

And SQLite driver also supports them:

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

The disadvantage is that you will significantly increase the use of bind_param with the specified parameters so that you cannot use amenities like selectcol_arrayref and $sth->execute(1,2,3) ( Note: If anyone knows how to use named placeholders with execute , I would appreciate some pointers in the comment, I never figured out how to do this). However, you can use various forms of numeric placeholders (for example, select c from t where x = $1 for PostgreSQL or select c from t where x = ?1 for SQLite).

Also keep in mind that PostgreSQL uses colons to slice arrays and question marks for some statements, so sometimes the standard? placeholders and: name named placeholders can cause problems. Have I ever had a problem? but I never used geometric operators ; I suspect that prudent use of spaces will avoid any problems with ?. If you don't use PostgreSQL arrays, you probably won't have to worry about arrays of slices fighting your tags :name .

---

Summary You cannot 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) using methods that work with @bind_params or you can use named placeholders ( :name for PostgreSQL and SQLite) if you are happy with the longer prepare / bind_param / execute / fetch sequence and you need to be careful if you use PostgreSQL arrays in your queries.