Perl DBD :: CSV-SQL语法-“ AND”子句无法正常工作 [英] Perl DBD::CSV - SQL Syntax - "AND" clause is not working properly
问题描述
我正在Ubuntu 10.04上运行Perl 5.10，并使用perl DBI模块。
我试图在Perl DBI下的SQL查询的 WHERE子句中使用 AND条件。我正在使用DBD :: CSV驱动程序。
I am running Perl 5.10 on Ubuntu 10.04 and using perl DBI module. I am trying to use the "AND" condition in "WHERE" clause in SQL Query under Perl DBI. I am using the DBD::CSV driver.
请考虑以下test.csv：
Please consoider below test.csv:
OS,RELEASE,VERSION
Ubuntu,Warty,4
Ubuntu,Hoary,5
Ubuntu,Breezy,5
Fedora,Yarrow,1
Fedora,Tettnang,2
Fedora,Stentz,4
在这里我要检索Fedora Stentz的版本。
这是我的代码：
Here I want to retrieve the VERSION for Fedora Stentz. Here is my code:
#!/usr/bin/perl -w
use strict;
use DBI;
my $table = "test.csv";
my $dbh = DBI->connect ("dbi:CSV:") or die "Cannot connect to the CSV file: $DBI::errstr()";
$dbh->{RaiseError} = 1;
$dbh->{TraceLevel} = 0;
my $query = "select VERSION from $table where OS='Fedora' and RELEASE='Yarrow'";
my $sth = $dbh->prepare ($query);
$sth->execute ();
$sth->dump_results();
$sth->finish();
$dbh->disconnect();
这里是输出；
0 rows
如果我在查询中使用占位符而不是实际的值如下：
If I use Placeholders in my query instead of the actual values as below:
my $query = "select VERSION from $table where OS=? and RELEASE=?";
my $sth = $dbh->prepare ($query);
$sth->execute ('Fedora', 'Yarrow');
$sth->dump_results();
$sth->finish();
$dbh->disconnect();
然后输出是如下错误：
DBD::CSV::st execute failed: You passed 2 parameters where 0 required [for Statement "select VERSION from test.csv where OS=? and RELEASE=?"] at count.pl line 14.
DBD::CSV::st execute failed: You passed 2 parameters where 0 required [for Statement "select VERSION from test.csv where OS=? and RELEASE=?"] at count.pl line 14.
但是如果我在下面的WEHRE子句中仅使用一个条件，则脚本给我正确的输出：
But if i use only one condition in hte WEHRE clause as below, then the script gives me the right output:
my $query = "select VERSION from $table where OS=?";
my $sth = $dbh->prepare ($query);
$sth->execute ('Fedora');
$sth->dump_results();
$sth->finish();
$dbh->disconnect();
然后输出为：
'1'
'2'
'4'
3 rows
所以，最重要的是，当我在 where子句中编写 and条件时，它不起作用。我怀疑查询语法有问题，但还无法弄清楚。任何指针或建议都会有很大帮助。
so, the bottom line and my issue is, when I write the "and" condition in "where" clause, it is not working. I doubt that there is something wrong with my query syntax but not able to figure that out yet. Any pointers or suggestion would be of great help.
此外，对于同一问题，我在perlmonks上也有一个正在进行的线程： http://www.perlmonks.org/?node_id=990214
Also, I have an ongoing thread on perlmonks for the same issue: http://www.perlmonks.org/?node_id=990214
谢谢。
推荐答案
您的所有摘录对我来说都很好。确保您的模块是最新的。来自 Devel :: VersionDump （在退出时调用）：
All of your snippets work fine for me. Make sure your modules are up to date. From Devel::VersionDump (called on exit):
Perl version: v5.16.0 on linux (/home/eric/usr/perlbrew/perls/5.16.0t/bin/perl)
AutoLoader - 5.72
Carp - 1.26
Clone - 0.31
Config - Unknown
Cwd - 3.39_02
DBD::CSV - 0.36
DBD::File - 0.40
DBI - 1.620
DBI::DBD::SqlEngine - 0.03
DBI::SQL::Nano - 1.014600
Data::Dumper - 2.135_06
Devel::VersionDump - 0.02
DynaLoader - 1.14
Errno - 1.15
Exporter - 5.66
Exporter::Heavy - 5.66
Fcntl - 1.11
File::Basename - 2.84
File::Spec - 3.39_02
File::Spec::Unix - 3.39_02
IO - 1.25_06
IO::File - 1.16
IO::Handle - 1.33
IO::Seekable - 1.1
List::Util - 1.23
Params::Util - 1.07
SQL::Dialects::AnyData - 1.33
SQL::Dialects::Role - 1.33
SQL::Eval - 1.33
SQL::Parser - 1.33
SQL::Statement - 1.33
SQL::Statement::Function - 1.33
SQL::Statement::Functions - 1.33
SQL::Statement::Operation - 1.33
SQL::Statement::Placeholder - 1.33
SQL::Statement::RAM - 1.33
SQL::Statement::Term - 1.33
SQL::Statement::TermFactory - 1.33
SQL::Statement::Util - 1.33
Scalar::Util - 1.23
SelectSaver - 1.02
Symbol - 1.07
Text::CSV_XS - 0.91
Tie::Hash - 1.04
XSLoader - 0.16
base - 2.18
bytes - 1.04
constant - 1.23
overload - 1.18
overloading - 0.02
sort - 2.01
strict - 1.07
unicore::Heavy.pl - Unknown
unicore::lib::Perl::Word.pl - Unknown
unicore::lib::Perl::_PerlIDS.pl - Unknown
utf8 - 1.09
utf8_heavy.pl - Unknown
vars - 1.02
warnings - 1.13
warnings::register - 1.02
这篇关于Perl DBD :: CSV-SQL语法-“ AND”子句无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
