Perl DBD :: CSV-SQL syntax - AND clause does not work correctly

Question

I am running Perl 5.10 on Ubuntu 10.04 and using the Perl DBI module. I am trying to use the AND clause in the WHERE clause in SQL Query under Perl DBI. I am using the DBD :: CSV driver.

Please split test.csv below:

OS,RELEASE,VERSION Ubuntu,Warty,4 Ubuntu,Hoary,5 Ubuntu,Breezy,5 Fedora,Yarrow,1 Fedora,Tettnang,2 Fedora,Stentz,4 

Here I want to get 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(); 

Here's the output of hte;

 0 rows 

If I use Placeholders in my query instead of the actual values, as shown 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(); 

then the output will be an error as shown below:

 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. 

But if I use only one condition in the hte WEHRE clause, as shown below, then the script gives me the correct output:

 my $query = "select VERSION from $table where OS=?"; my $sth = $dbh->prepare ($query); $sth->execute ('Fedora'); $sth->dump_results(); $sth->finish(); $dbh->disconnect(); 

And hte output:

 '1' '2' '4' 3 rows 

So, in the bottom line and my problem, when I write the condition "and" in the "where" section, it does not work. I doubt that there is something wrong with my query syntax, but I still cannot figure it out. Any pointers or suggestions would be very helpful.

In addition, I have a constant thread on perlmonks for the same problem: http://www.perlmonks.org/?node_id=990214

Thanks.

Answer 1

All your snippets work great 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