Strange code. Really weird. Why finish anddisconnect after the return? These are never executed. Why finish immediately after creation of the handle, even before the execute. Weird again. If I clean up the code minimally and run that with recent versions of DBI, Text::CSV_XS, and DBD::CSV, I do not see that error. Maybe we also should get some data.
FWIW the use Text::CSV; is completely useless, as DBD::CSV will use Text::CSV_XS automatically
$ cat newCsv.csv
sid,genotype,dnabl,dnatw12
1,a,3,4
$ cat test2.pl
use 5.20.0;
use warnings;
use DBI;
sub RunSqlSearch;
my $spreadsheet = "newCsv";
my $query =
qq(SELECT sid, genotype, dnabl, dnatw12 FROM $spreadsheet WHERE ge
+notype = 'a' AND (dnatw12/dnabl)<0.5 AND dnatw12 IS NOT NULL);
my ($queryResult) = RunSqlSearch ($query);
say "Your query $query returned the following result:\n$queryResult";
sub RunSqlSearch {
my $query = shift;
# error check #
$query eq "" and die "From runSqlQuery2 - No value entered for $q
+uery $!\n";
# Connect to the database, (the directory containing our csv file(
+s))
my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
f_dir => ".",
f_ext => ".csv/r",
f_encoding => "utf-8",
csv_eol => "\n",
RaiseError => 1,
}) or die $DBI::errstr;
# Output using sql query #
my $sth = $dbh->prepare ($query);
$sth->execute;
my $queryResult = "";
my @queryResult;
while (my @row = $sth->fetchrow_array) {
push @queryResult, @row;
$queryResult .= join ("\t", @row) . "\n";
}
# output arguments #
$queryResult eq "" and $queryResult = "No result found";
return $queryResult;
$sth->finish (); # Never executed
$dbh->disconnect (); # Never executed
} # RunSqlSearch
$ perl test2.pl
perl test2.pl
Your query SELECT sid, genotype, dnabl, dnatw12 FROM newCsv WHERE geno
+type = 'a' AND (dnatw12/dnabl)<0.5 AND dnatw12 IS NOT NULL returned t
+he following result:
No result found
$ perl -MV=DBI,Text::CSV_XS,DBD::CSV
DBI
/pro/lib/perl5/site_perl/5.22.0/x86_64-linux-thread-multi-ld/D
+BI.pm: 1.636
Text::CSV_XS
/pro/lib/perl5/site_perl/5.22.0/x86_64-linux-thread-multi-ld/T
+ext/CSV_XS.pm: 1.23
DBD::CSV
/pro/lib/perl5/site_perl/5.22.0/DBD/CSV.pm: 0.49
$
After re-reading the original post, I tried with empty fields, and can reproduce the error:
$ cat newCsv.csv
sid,genotype,dnabl,dnatw12
1,a,,
$ perl test2.pl
DBD::CSV::st execute failed: Can't locate object method "do_err" via p
+ackage "SQL::Statement::Function::NumericEval" at /pro/lib/perl5/site
+_perl/5.22.0/SQL/Statement/Function.pm line 226, <GEN1> line 2.
[for Statement "SELECT sid, genotype, dnabl, dnatw12 FROM newCsv WHER
+E genotype = 'a' AND (dnatw12/dnabl)<0.5 AND dnatw12 IS NOT NULL"] at
+ test2.pl line 32, <GEN1> line 2.
DBD::CSV::st execute failed: Can't locate object method "do_err" via p
+ackage "SQL::Statement::Function::NumericEval" at /pro/lib/perl5/site
+_perl/5.22.0/SQL/Statement/Function.pm line 226, <GEN1> line 2.
[for Statement "SELECT sid, genotype, dnabl, dnatw12 FROM newCsv WHER
+E genotype = 'a' AND (dnatw12/dnabl)<0.5 AND dnatw12 IS NOT NULL"] at
+ test2.pl line 32, <GEN1> line 2.
$
Enjoy, Have FUN! H.Merijn
|