如何为DB对象名称使用postgres占位符
如何为DB对象名称使用postgres占位符
提问于 2015-05-13 00:09:18
(或者如何使用perl (DBD::PG)和占位符来迭代信息模式？)
Windows 7，ActiveState Perl 5.20.2，PostgreSQL 9.4.1。
下面的案例A、B和C在为列值使用占位符时是成功的。按顺序
- 没有使用占位符
- 通过文字
- 传递一个变量(使用相同的文字填充)
将其提升到DB对象的级别将是很好的。(表格、意见等)
下面是输出结果，并给出了用例D的错误：
Z:\CTAM\data_threat_mapping\DB Stats\perl scripts>test_placeholder.pl
A Row Count: 1
B Row Count: 1
C Row Count: 1
DBD::Pg::st execute failed: ERROR: syntax error at or near "$1"
LINE 1: SELECT COUNT(*) FROM $1 WHERE status = 'Draft';
^ at Z:\CTAM\data_threat_mapping\DB Stats\perl
scripts\test_placeholder.pl line 34.对任何方向都非常感激！
#!/usr/bin/perl -w
use strict;
use diagnostics;
use DBI;
my $num_rows = 0;
# connect
my $dbh = DBI->connect("DBI:Pg:dbname=CTAM;host=localhost",
"postgres", "xxxxx",
{ 'RaiseError' => 1, pg_server_prepare => 1 });
#---------------------
# A - success
my $sthA = $dbh->prepare(
"SELECT COUNT(*) FROM cwe_compound_element WHERE status = 'Draft';"
);
$sthA->execute(); # no placeholders
#---------------------
# B - success
my $sthB = $dbh->prepare (
"SELECT COUNT(*) FROM cwe_compound_element WHERE status = ?;"
);
$sthB->execute('Draft'); # pass 'Draft' to placeholder
#---------------------
# C - success
my $status_value = 'Draft';
my $sthC = $dbh->prepare(
"SELECT COUNT(*) FROM cwe_compound_element WHERE status = ?;"
);
$sthC->execute($status_value); # pass variable (column value) to placeholder
#---------------------
# D - failure
my $sthD = $dbh->prepare(
"SELECT COUNT(*) FROM ? WHERE status = 'Draft';"
);
$sthD->execute('cwe_compound_element'); # pass tablename to placeholder我试过单/双/无引号(q，qq).
回答 1
Stack Overflow用户
发布于 2015-05-13 01:08:50
如果
SELECT * FROM Foo WHERE field = ?手段
SELECT * FROM Foo WHERE field = 'val'然后
SELECT * FROM ?手段
SELECT * FROM 'Table'这显然是错误的。占位符只能在表达式中使用。修正：
my $sthD = $dbh->prepare("
SELECT COUNT(*)
FROM ".$dbh->quote_identifier($table)."
WHERE status = 'Draft'
");
$sthD->execute();页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30203473
复制相关文章
相似问题
腾讯云开发者
