Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

DBD::CSV::st execute failed: Can't locate object method "do_err" via package "SQL::Statement::Function::NumericEval"

by Sandy_Bio_Perl (Beadle)
on Jul 07, 2016 at 14:53 UTC ( #1167381=perlquestion: print w/replies, xml ) Need Help??

Sandy_Bio_Perl has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks!

I am running sql queries on a spreadsheet. Some of the cells have no value in them. For example, some patients have no record of their viral DNA level at treatment week 12 (column dnatw12). When I try to skip these blank fields using 'IS NOT NULL' I get an error message {DBD::CSV::st execute failed: Can't locate object method "do_err" via package "SQL::Statement::Function::NumericEval"}

My Perl code for the sql search is below

#!/usr/bin/perl use strict; use warnings; use Text::CSV; use DBI; sub RunSqlSearch; my $spreadsheet = 'newCsv.csv'; my $query = qq(SELECT sid,genotype, dnabl, dnatw12 FROM $spreadsheet W +HERE genotype = 'a' AND (dnatw12/dnabl)<0.5 AND dnatw12 IS NOT NULL ) +; my ($queryResult) = RunSqlSearch($query); print "Your query $query returned the following result:\n$queryResult\ +n"; sub RunSqlSearch($){ my $query = $_[0]; # error check # if ($query eq ""){die "From runSqlQuery2 - No value entered for $ +query $!\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); if($sth){ $sth ->finish(); } $sth->execute; my @row; my $queryResult=""; my @queryResult; while (@row = $sth->fetchrow_array) { push @queryResult, @row; $queryResult .= join("\t",@row) . "\n"; } # output arguments # if ($queryResult eq ""){$queryResult = "No result found";} return ($queryResult); $sth->finish(); $dbh->disconnect(); }

My full error message is below

DBD::CSV::st execute failed: Can't locate object method "do_err" via p +ackage "SQL::Statement::Function::NumericEval" at C:/Strawberry/perl/ +vendor/lib/SQL/Statement/Function.pm line 216, <GEN1> line 37. [for Statement "SELECT sid,genotype, dnabl, dnatw8 FROM newCsv.csv WH +ERE genotype = 'a' AND (dnatw12/dnabl)<0.5 AND dnatw12 IS NOT NULL "] + at C:/Users/Sandy/Documents/PC_Bioinformatics_MSc/Project/Perl/runSQ +LQuery2.pl line 38, <GEN1> line 37. DBD::CSV::st execute failed: Can't locate object method "do_err" via p +ackage "SQL::Statement::Function::NumericEval" at C:/Strawberry/perl/ +vendor/lib/SQL/Statement/Function.pm line 216, <GEN1> line 37. [for Statement "SELECT sid,genotype, dnabl, dnatw8 FROM newCsv.csv WH +ERE genotype = 'a' AND (dnatw12/dnabl)<0.5 AND dnatw12 IS NOT NULL "] + at C:/Users/Sandy/Documents/PC_Bioinformatics_MSc/Project/Perl/runSQ +LQuery2.pl line 38, <GEN1> line 37.

When I run a query using columns that do not contain blanks - details for treatment week 8 are complete for all patients e.g. qq(SELECT sid,genotype, dnabl, dnatw8 FROM $spreadsheet WHERE genotype = 'a' AND (dnatw8/dnabl)<0.5 AND dnatw8 IS NOT NULL ); The query works perfectly.

I would be very grateful for any advice from the Monastery

  • Comment on DBD::CSV::st execute failed: Can't locate object method "do_err" via package "SQL::Statement::Function::NumericEval"
  • Select or Download Code

Replies are listed 'Best First'.
Re: DBD::CSV::st execute failed: Can't locate object method "do_err" via package "SQL::Statement::Function::NumericEval"
by Tux (Canon) on Jul 07, 2016 at 19:32 UTC

    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

      Thank you, brother Tux. Apologies for my sloppy coding. It had developed it over a period of time and had forgotten to take out redundant lines. I appreciate the time and effort you put into to reading and fixing it.

      However, as you noted, the sloppiness of my coding was not cause of the error. Top marks to brother BaldManTom (below) for providing a solution.

Re: DBD::CSV::st execute failed: Can't locate object method "do_err" via package "SQL::Statement::Function::NumericEval"
by BaldManTom (Friar) on Jul 08, 2016 at 00:16 UTC

    Hi Sandy_Bio_Perl,

    I think that the problem isn't with the IS NOT NULL, but rather with the division on the column with the null value. Here's something I scratched together that works. Note the IFNULL in your SQL statement, as well as csv_null => 1 in the connection parameters:

    #!/usr/bin/perl use strict; use warnings; use DBI; sub RunSqlSearch; my $spreadsheet = 'newCsv.csv'; my $query = "SELECT sid,genotype, dnabl, dnatw12 " . "FROM $spreadsheet " . "WHERE genotype = 'a' " . "AND (IFNULL(dnatw12,0)/dnabl)<0.5 " . "AND dnatw12 IS NOT NULL " ; my ($queryResult) = RunSqlSearch($query); print "Your query $query returned the following result:\n$queryResult\ +n"; sub RunSqlSearch($){ my $query = $_[0]; # error check # if ($query eq ""){die "From runSqlQuery2 - No value entered for $ +query $!\n";} # Connect to the database, (the directory containing our csv file( +s)) my $dbh = DBI->connect ("dbi:CSV:", undef, undef, { f_dir => ".", f_encoding => "utf-8", csv_eol => "\n", csv_null => 1, RaiseError => 1, }) or die $DBI::errstr; # Output using sql query # my $sth = $dbh->prepare($query); $sth->execute; my @row; my $queryResult=""; while (@row = $sth->fetchrow_array) { $queryResult .= join("\t",@row) . "\n"; } # output arguments # if ($queryResult eq ""){$queryResult = "No result found";} return ($queryResult); }

    Hope that points you in the right direction.

    -bmt

    UPDATE: Note that this will still blow up if dnabl is NULL. I played around a bit, but the IFNULL that worked in the numerator of the division failed to work in the denominator. Not sure why...

      Thanks for reporting and confirming the issue. Please try whether https://github.com/perl5-dbi/SQL-Statement/commit/af13adb1f489f88a45c2c1c8b822346e2cefe686 fixes the issue for you.

      Thanks, Jens

      Thank you brother BaldManTom

      Your suggestions work perfectly. I very much appreciate your help

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1167381]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (None)
    As of 2021-10-16 06:02 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?
      My first memorable Perl project was:







      Results (69 votes). Check out past polls.

      Notices?