如何在带引号的占位符中使用查询? (Perl / PostgreSQL) [英] How can I use a query with placeholder inside quotes? (perl / postgresql)
问题描述
我正在尝试执行以下脚本:
I'm trying to execute the following script:
#!/usr/bin/perl -w
use strict;
use DBI;
my $db = "Pg";
my $db_database = "whatever";
my $user = "whatever";
my $password = "whatever";
my $dbh = DBI->connect("dbi:$db:dbname=$db_database", $user, $password);
my $query = $dbh->prepare (q{SELECT
arrival_date - INTERVAL '? MINUTE'
FROM emails LIMIT 1})
or die ("unable to prepare");
$query->execute(60) or die("unable to execute");
print $query->fetchrow_array, "\n";
(到达日期的格式为:带时区的时间戳记,缺省为CURRENT_TIMESTAMP,不为NULL)
(arrival_date has this format: timestamp with time zone NOT NULL default CURRENT_TIMESTAMP)
问题在于未检测到问号占位符,因为其内部单引号:
The problem is that the question mark placeholder is not detected because its inside single quotes:
DBD::Pg::st execute failed: called with 1 bind variables when 0 are needed
如果我使用$ 1占位符qq {},并尝试了$ dbh-> quote的一些变体,那会有所帮助。我该如何工作?
It does not help if I use qq{}, $1 placeholder, and tried a few variations with $dbh->quote. How can I make this work?
推荐答案
您不能在引号内使用占位符。您可以使用SQL字符串连接,但是在这种情况下,使用乘法更容易做到:
You can't use placeholders inside quotes. You can use SQL string concatenation, but in this case, it's easier to do it by using multiplication:
my $query = $dbh->prepare (q{SELECT
arrival_date - ? * INTERVAL '1 MINUTE'
FROM emails LIMIT 1});
$query->execute(60);
这样,您不必附加'分钟'
改为执行查询时的数字。
That way, you don't have to append ' minutes'
to the number when you execute the query.
这篇关于如何在带引号的占位符中使用查询? (Perl / PostgreSQL)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!