DBD::CSV - SQL Syntax - "AND" clause is not working properly

[slayedbylucifer]

Hello Monks.

I am using DBI and the CSV driver DBD::CSV for manipulating the the CSV files. my SELECT query is not interpreting the way I want it ot.

e.g., consider following table called test.csv:

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

So, I want to list the version for Fedora Stentz which is "4".

I have written below query:

my $query = "SELECT VERSION from test.csv WHERE OS="Fedora" AND RELEAS
+E="Stentz";
my $sth = $dbh->prepare ($query);
$sth->execute;

However, I get below error:

DBD::CSV::st execute failed: No such column '"Fedora" AND RELEASE="Ste
+ntz"' at /usr/local/lib/perl/5.10.1/DBI/DBD/SqlEngine.pm line 796

So, This means the use of "AND" clause is causing the problem. I have worked a little bit on Oracle DB and know that the above Query would work fine on Oracle DB.

however, I am not able to put "AND" clause to work in above query.

I hve referred the documnetion @ SQL::Statement::Syntax (which is used by DBD::CSV), however it doensn't talk much about the "AND" clause usage.

Could you help in correcting the query please.

-Thnaks.

[marto]

Replace:

my $query = "SELECT VERSION from test.csv WHERE OS="Fedora" AND RELEASE="Stentz"

with:

my $query = "SELECT VERSION from test.csv WHERE OS='Fedora' AND RELEASE='Stentz'";

You can't be posting the code you're actually running. You were missing a semicolon at the end of the line, also note how I quote the values. You'd have the same problem with Oracle, contrary to your claim.

Update: Basic working example:

#!/usr/bin/perl

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect ("dbi:CSV:") or die "Cannot connect: $DBI::errs
+tr";

$dbh->{csv_tables}->{testtable} = {
    'file' => 'test.csv',
};

my $query = "SELECT VERSION from test.csv WHERE OS='Fedora' AND RELEAS
+E='Stentz'";
my $sth = $dbh->prepare($query);
$sth->execute;
$sth->dump_results();
$dbh->disconnect();

[slayedbylucifer]

The missing ";" was a typo. My actual query looks like below:

my $query = "SELECT COLUMN3 FROM $table WHERE COLUMN1=\'$var1\' AND CO
+LUMN2=\'$var2\'";

I already tried the "Single quotes" as above and it fails as well. I had to escape the single quotes else it won't interpret the Value that is stored in the variables.

Thanks for your time.

[marto]

When posting please provide an example which you are actually running, not doing this potentially wastes peoples time, posting unrelated code and a made up problem. I've provided a working example based on your csv file and the query you ran. Stop using variables like this, read and understand 'Placeholders and bind values' from the DBI documentation and never ever forget the ballad of Little Bobby Tables. SQL_injection.

[Anonymous Monk]

Note: Quoting "Strings" using double quotes are recognized as quoted identifiers (column or table names).

#!/usr/bin/perl --
use strict; use warnings;
use Data::Dump;
use DBI;
use DBD::CSV;
my $dbh = DBI->connect(
    "dbi:CSV:",
    undef, undef,
    { RaiseError => 1 },
);
my $sth = $dbh->prepare_cached('
select *
from test.csv
where OS=?
and RELEASE=?
LIMIT 8
');
$sth->execute( 'Fedora', 'Stentz' );
dd $sth->fetchall_arrayref;
__END__
[["Fedora", "Stentz", 4]]

[slayedbylucifer]

All, Thank you very much for your time and suggestion. the ultimate issue was a screwed up SQL::Statement module installation. I got it re-installed and my script is working fine. Please find more details here: http://stackoverflow.com/questions/12164087/perl-dbdcsv-sql-syntax-and-clause-is-not-working-properly

[marto]

Again, vastly different from the code you posted here, which didn't even compile. The code you posted on SO looks like a combination of the replies you got here.

[Anonymous Monk]

That code does not compile , esp the sql string