DBI : select count

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

Hello!

Could someone please point out where I'm going wrong with the following code?

Supposed to be the abc's of sql under DBI::CSV, I want to count the number of records in a csv file (yes, properly created under DBI::CSV - the database is known to work) that contain a certain user_name.

But, the following dies at the prepare statement and produces a "parse error near (*)" error:

    $sqlCount = "SELECT COUNT(*) FROM $user_data_db_name WHERE USER_NA
+ME='$form_values{'USER_NAME'}'";
    $sth = $dbh->prepare($sqlCount) || die "Cannot prepare: " . $dbh->
+errstr();
    $sth->execute() or die "Cannot execute: " . $sth->errstr();
    my $occurences = $sth->fetchrow_arrayref->[0];
[download]

so I tried brute force:

    $sqlCount = "SELECT * FROM $user_data_db_name WHERE USER_NAME='$fo
+rm_values{'USER_NAME'}'";
    $sth = $dbh->prepare($sqlCount) || die "Cannot prepare: " . $dbh->
+errstr();
    $sth->execute() or die "Cannot execute: " . $sth->errstr();
    $sth->finish();
    $ocurrences = 0;
    while (@data = $sth->fetchrow_array()) {
        $ocurrences++;
    }
[download]

which runs, but returns null for $ocurrences.

Hours of searching for ideas has produced nothing.

Thanks.

Comment on DBI : select count Select or Download Code

Replies are listed 'Best First'.
Re: DBI : select count by lachoy (Parson) on May 09, 2002 at 16:19 UTC
You're calling `finish()` on the statement handle before you fetch anything -- of course it's not going to return anything :-) Move that after the `while` loop and you'll almost certainly get different results. As for the `SELECT COUNT(*)...` not being supported, that's understandable. DBD::CSV is just a standard way to access data in CSV files -- it doesn't implement an entire database engine. (That said, I think DBD::AnyData may implement some additional functionality for you...) Chris M-x auto-bs-mode	[reply] [d/l] [select]
Re: DBI : select count by tommyw (Hermit) on May 09, 2002 at 16:21 UTC
`SELECT * FROM $user_data_db_name WHERE USER_NAME='$form_values{'USER_NAME'}'` isn't interpolating the values the way you thing it is: you're searching for the record where the username is literally $form_values... Instead of this, try `$sqlCount = "SELECT COUNT(*) FROM $user_data_db_name WHERE USER_NAME=? +"; $sth = $dbh->prepare($sqlCount) \|\| die "Cannot prepare: " . $dbh->errs +tr(); $sth->execute($form_values{'USER_NAME'}) or die "Cannot execute: " . $ +sth->errstr(); my $occurences = $sth->fetchrow_arrayref->[0];` [download] And find out why bind variables are your friend As an aside, do you really want the table name to be a variable. Could be messy if it gets the wrong value into it. -- Tommy Too stupid to live. Too stubborn to die.	[reply] [d/l] [select]
Re (2): DBI : select count by VSarkiss (Monsignor) on May 09, 2002 at 17:10 UTC
you're searching for the record where the username is literally `$form_values...` Um, no. The outermost quotes are double, so the variable is in fact being interpolated. Nonetheless, using placeholders as you've illustrated is a much better approach.	[reply] [d/l]
Re: Re (2): DBI : select count by punchcard_bob (Initiate) on May 09, 2002 at 17:29 UTC
Chris - spot on for the placement of finish()! Exactly the sort of fault that comes from staring at code too long - didn't even see it. Thanks. Tommy - thanks, but like VSarkiss says above, the quotes make it scope the hash and generates the query properly. But you guys are right about the usefulness of placeholders.	[reply]