问题描述
在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.
推荐答案
支持哪种占位符(如果有)取决于驱动程序:
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.
但是您很幸运,支持命名或编号参数:
But you're in luck, the PostgreSQL driver supports named or numbered parameters:
和也支持它们:
缺点是您最终将使用 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&中使用命名占位符公司?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!