Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

DBI:CSV SQL parsing problem

by rlewisuk (Initiate)
on Nov 05, 2007 at 11:16 UTC ( #648991=perlquestion: print w/ replies, xml ) Need Help??
rlewisuk has asked for the wisdom of the Perl Monks concerning the following question:

Hello all.

I am having problems with DBI:CSV. I keep getting the following error:

"SQL ERROR: Bad table or column name 'col2 + col3' has chars not alphanumeric or underscore!
DBD::CSV::db prepare failed: SQL ERROR: Bad table or column name 'col2 + col3' has chars not alphanumeric or underscore!
for Statement "SELECT AVG(col2 + col3) FROM mydata.csv" at test_script.pl line 11."

I have the following code to query a csv file.

#!/usr/bin/perl use DBI; use strict; use warnings; ## Set up connection to csv data file as a database my $dbh = DBI->connect("dbi:CSV:f_dir=./", "", "", {AutoCommit => 1, RaiseError => 1}); my $sth = $dbh->prepare("SELECT AVG(col2 + col3) FROM mydata.csv"); $sth->execute(); my $value = $sth->fetchrow_array; foreach (@value){ print $_."\n"; } $sth->finish(); $dbh->disconnect();
This works fine:
my $sth = $dbh->prepare("SELECT AVG(col3) FROM mydata.csv");
So does this:
my $sth = $dbh->prepare("SELECT (col2 + col3) FROM mydata.csv");
Just not this:
my $sth = $dbh->prepare("SELECT AVG(col2 + col3) FROM mydata.csv");
The mydata.csv file has the following in it:
col1,col2,col3,col4 575,480,192,5 388,485,194,5 379,447,210,5 504,465,215,0.8 356,439,219,0.7 481,435,225,0.6
Any idea what is going wrong? Any help would be appreciated.

Comment on DBI:CSV SQL parsing problem
Select or Download Code
Re: DBI:CSV SQL parsing problem
by erroneousBollock (Curate) on Nov 05, 2007 at 12:23 UTC
    Any idea what is going wrong?
    I'd say it's apparent that the SQL expression parser employed by DBD::CSV is unable to deal with such a complicated expression (either at all, or with the amount of type information at its disposal). Specifically, the parser expects a variable or a numeric literal as the argument to an aggregate function.

    Assuming that's not the case, have you tried more parentheses ? some form of value coercion ?

    -David

          > Assuming that's not the case, have you tried more parentheses ? 
      
      Or perhaps sprinkling some chicken blood would help :-). The expected behavior is documented in this case so reading the docs is probably quicker (though perhaps not as much fun, depending on your definition of fun) than experimentation.
Re: DBI:CSV SQL parsing problem
by jZed (Prior) on Nov 05, 2007 at 16:35 UTC
    The syntax that DBD::CSV supports is documented in SQL::Statement::Syntax. It clearly states that the supported argument for set functions is a column name, not a numeric expression:
    set function ::= COUNT ( [DISTINCT|ALL] * ) | COUNT | MIN | MAX | AVG | SUM ( [DISTINCT|ALL] col_name )
    So the short answer is, no, sorry, you can't currently do that like that in DBD::CSV. Depending on what you want to do, you may be able to build your own UDF (User-Defined-Function) to use here. See SQL::Statement::Function for details of building UDFs.
Re: DBI:CSV SQL parsing problem
by ikegami (Pope) on Nov 05, 2007 at 16:45 UTC
    Workaround:
    my $sth = $dbh->prepare("SELECT AVG(col2), AVG(col3) FROM mydata.csv") +; $sth->execute(); my @avgs = $sth->fetchrow_array(); my $avg = $avgs[0] + $avgs[1];
      Something like that might work. OTOH, I'm not totally sure what AVG(col1 +col2) should produce or whether it would be same as what you suggest. For example, since NULLs are eliminated, the two columns may have a different Count.

      Note that this gives different results if ever only one of col2 and col3 are NULL.

      - tye        

        No worries there, all columns have the same number of (non NULL) values.
      Thanks ikegami
      This might be an option. I have tried
      SELECT AVG(col2) + AVG(col3)...
      Without success. If I use
      my $avg = $avgs[0] + $avgs[1];
      I'll first have to parse the SELECT statement for +,-,* and / and replace with commas and do the operation afterwards, but it certainly seems doable.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://648991]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (17)
As of 2014-09-22 14:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (196 votes), past polls