http://www.perlmonks.org?node_id=314848

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

Fellow monks, I have an odd database problem. I'm implementing user registration on my website; in my user validation, I have code like this:
$SQL = "SELECT COUNT(*) FROM Members WHERE Nickname = '?'"; $Statement = $Database->prepare($SQL); $Statement->execute($NickName); my @numrows = $Statement->fetchrow_array; rint "NumRows: @numrows"; if (($Statement->rows) > 0) { LogError("NICK_TAKEN"); return("-1"); }
The problem is this: I can never get the $Statement to return anything when selecting a Nickname I know exists. If I try this in the mysql monitor (using the same user login and machine as this script runs on, and the exact same query), I get back one row; but inside this script, nothing. I've also tried my $NumRows = $Statement->rows but that always returns zero rows. I'm using CGI.PM with fatalstobrowser. I tried modifying the username, and it expectedly spits out a database login error with the modified username - but with the "correct" username, I get nothing. I've done things like this before without a problem. Where can I look that would give me an indication of what is breaking down?

UPDATE: Thanks for the hints, everyone. I should have clarified earlier that I actually tried to select the rows and then count them, then I read the CPAN warning and decided to use SELECT COUNT(*) instead. In the end, removing the single quotes worked.

Replies are listed 'Best First'.
Re: Odd Database Behavior
by bart (Canon) on Dec 15, 2003 at 17:06 UTC
    Drop the quotes around the placeholder question mark. The placeholder mechanism will provide them when considered necessary.
    $SQL = "SELECT COUNT(*) FROM Members WHERE Nickname = ?";
Re: Odd Database Behavior
by hardburn (Abbot) on Dec 15, 2003 at 17:19 UTC

    Your question should be answered by one of the nodes above, but just a note for the future: when debugging DBI, use the DBI->trace() method to turn on extra debugging information printed to STDERR. Usually a trace level of 2 is sufficient, which should show you (among other things) the statement after placeholders are filled in.

    ----
    I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
    -- Schemer

    : () { :|:& };:

    Note: All code is untested, unless otherwise stated

Re: Odd Database Behavior
by tcf22 (Priest) on Dec 15, 2003 at 17:06 UTC
    If you are doing a SELECT COUNT(*), why are you using the rows() method. Just look at the query results.

    UPDATE: Added code
    my @row = $Statement->fetchrow_array; if ($row[0] > 0) { LogError("NICK_TAKEN"); return("-1"); }


    Also, rows() doesn't always work when used with select statements.
    From CPAN...
    $rv = $sth->rows; Returns the number of rows affected by the last row affecting comm +and, or -1 if the number of rows is not known or not available. Generally, you can only rely on a row count after a non-SELECT exe +cute (for some specific operations like UPDATE and DELETE), or after +fetching all the rows of a SELECT statement. For SELECT statements, it is generally not possible to know how ma +ny rows will be returned except by fetching them all. Some drivers wi +ll return the number of rows the application has fetched so far, but +others may return -1 until all rows have been fetched. So use of the +rows method or $DBI::rows with SELECT statements is not recommended. One alternative method to get a row count for a SELECT is to execu +te a "SELECT COUNT(*) FROM ..." SQL statement with the same "..." as +your query and then fetch the row count from that.

    - Tom

Re: Odd Database Behavior
by Abigail-II (Bishop) on Dec 15, 2003 at 17:11 UTC
    A few things:
    • Check the result of your prepare.
    • Check the result of your execute.
    • Don't quote placeholders.
    • Why check the number of rows returned (which might not even available until all rows have been fetched)if you are doing a count (*)?
    • Why quote the -1 you are returning?
    Abigail
Re: Odd Database Behavior
by Roy Johnson (Monsignor) on Dec 15, 2003 at 17:06 UTC
    Try it without the quotes around the ?. IIRC, parameter substitution will know that it has a string.

    The PerlMonk tr/// Advocate
Re: Odd Database Behavior
by pg (Canon) on Dec 15, 2003 at 17:15 UTC

    Two things:

    • Drop the single quotes around the ? mark to make the syntax right
    • As you use count in your SQL statement, you will always get one row back, thus checking the size of result set makes no sense, simply use the count you get.
Re: Odd Database Behavior
by grinder (Bishop) on Dec 15, 2003 at 20:15 UTC

    If you just want to see whether a value appears in a column of a table, performing a count(*) is a fearfully expensive way of going about that. At the worst, you will perform a full table scan.

    The query would be better formulated as select 1 from members where nickname='...'

    If this is just a once-off, the simplest approach would be to use the do method, with something (error-checking notwithstanding) like:

    sub nick_exists { return $db->do( "select 1 from members where nickname=" . $db->quote($_[0]) ); }

    If, on the other hand, you know you'll be pounding on this routine in an inner loop, it might then be worthwhile recasting it as a prepared statement:

    BEGIN { my $ss = $db->prepare( 'select 1 from members where nickname=?' ) or d +ie; sub nick_exists { $ss->execute($_[0]); my $res = $ss->fetchrow_arrayref; $ss->finish; $res and defined $res->[0] ? 1 : 0; } }

    The beauty of this approach is that you don't have to change your code. The access to the database is abstracted away in the nick_exists routine.

    And as if that wasn't enough, you could further add

    use Memoize; memoize('nick_exists');

    ... although you want to be careful with that. The program will continue to assume that a nick that has just been taken is still available. That would be a bug. But, used with care, this can provide a healthy speed boost.