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

[Sandy_Bio_Perl]

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

[Tux]

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.
$

[Sandy_Bio_Perl]

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.

[BaldManTom]

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...

[rehsack]

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

[Sandy_Bio_Perl]

Thank you brother BaldManTom

Your suggestions work perfectly. I very much appreciate your help