可以在DBI的selectcol_arrayref&中使用命名占位符公司？ [英] Possible to use named placeholders in DBI's selectcol_arrayref & Co.?
问题描述
在DBI允许@bind_values的地方，可以使用命名占位符吗？例如，我想发表以下声明：
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 ());
不容易出错。我正在使用DBD :: Pg和DBD :: SQLite。
less prone to mistakes. I'm using DBD::Pg and DBD::SQLite.
推荐答案
支持哪种占位符（如果有）<一个href = https://metacpan.org/pod/distribution/DBI/DBI.pm#Placeholders-and-Bind-Values rel = nofollow noreferrer>取决于驱动程序：
What sorts of placeholders (if any) are supported depends on the driver:
占位符和绑定值
某些驱动程序支持占位符并绑定
[...]
一些驱动程序还允许使用占位符，例如：name 和：N （例如，：1 ，：2 等），但是它们的使用不可移植。
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.
但是您很幸运， PostgreSQL驱动程序支持命名或编号参数：
But you're in luck, the PostgreSQL driver supports named or numbered parameters:
可以使用三种类型的占位符：在DBD :: Pg中使用。第一种是问号类型，其中每个占位符都由一个问号字符表示。
[...]
方法的第二种占位符是美元符号数字。
[...]
最终占位符类型为命名参数，格式为：foo。
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".
和 SQLite驱动程序也支持它们：
SQLite支持多个占位符表达式，包括？
SQLite supports several placeholder expressions, including ? and :AAAA.
缺点是您最终将使用 bind_param 具有很多命名参数，因此您将无法使用 selectcol_arrayref 和 $ sth-> execute（1,2， 3）（注意：，如果有人知道如何在 execute 中使用命名占位符，我会很高兴看到评论，我从来没有想过怎么做）。但是，您可以使用各种形式的数字占位符（例如，对于PostgreSQL，t中的 select c，对于PostgreSQL，x = $ 1 ；对于t中的 select t，其中x =？1 （对于SQLite）。
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).
另外请注意，PostgreSQL对数组切片和问号使用冒号表示某些运算符，因此有时标准？占位符和：name 命名占位符会引起问题。我从没有对有任何疑问？，但从未使用过几何运算符之一；我怀疑明智地使用空格会避免？出现任何问题。如果您不使用PostgreSQL数组，那么您可能不必担心数组切片与您的：name 命名占位符作斗争。
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.
执行摘要：您不能将命名占位符与 selectcol_arrayref 配合使用，也不能与 @bind_params 。但是，对于SQLite和Postgresql，您可以使用带编号的占位符（ $ 1 ， $ 2 ，...用于Postgresql或？1 ，？2 ，...（对于SQLite），以及与 @一起使用的方法bind_params ，或者如果您愿意使用更长的，则可以使用命名的占位符（对于PostgreSQL和SQLite，均使用 / ：name ）准备 bind_param / 执行 / 获取方法序列，如果在查询中使用PostgreSQL数组，则必须小心。
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.
这篇关于可以在DBI的selectcol_arrayref&中使用命名占位符公司？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
