Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
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
Replies are listed 'Best First'.
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 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 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 lurking in the Monastery: (10)
As of 2015-07-08 07:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (96 votes), past polls