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

Good day bros. I am having an odd DBI problem. I have a subroutine that I am using to fetch a value from MySQL: 
sub getsqlvalue {
    my @results = ();
    my ($dbh,$sqlstatement)= @_;
    my $sth = $dbh->prepare($sqlstatement);
    my @row;
    $sth->execute || die "Could not execute MySQL statement: $sqlstate
+ment";
    while (@row=$sth->fetchrow_array) { push(@results, [ @row ]); }
    $sth->finish();
    return $results[0][0];
}

[download]
I run this to get a value and it works fine. I then run it again to get another value (from the same field only with a different where condition) and it returns nothing.

So I set a breakpoiint in the debugger inside this sub and copied and pasted the contents of $sqlstatement into the MySQL command line client, and it gave the proper value. I then continued execution, and the value came back null. DBI returns no errors.

What could explain this?!? How can I debug?

Replies are listed 'Best First'.
Re: DBI not returning value that MySQL client does
by graff (Chancellor) on May 19, 2010 at 01:23 UTC
    If your mysql command line client gives a "null" result for the specific query, this is actually equivalent to what your perl code is doing. In both cases, the return from the query is an empty set (with no errors). If you want to trap that, your perl code needs to test for an empty return.

    Personally, in terms of "raw DBI" calls, I tend to prefer "fetchall_arrayref", which would look like this in your case (at least, this is one way to do it): 

    sub getsqlvalue {
    my ($dbh,$sqlstatement)= @_;
    my $sth = $dbh->prepare($sqlstatement)
    $sth->execute || die "MySQL execute failed";
    my $rows = $sth->fetchall_arrayref;
    $sth->finish();

    return ( ref( $rows ) eq 'ARRAY' and @$rows ) ? $$rows[0][0] : und
+ef;
}

    [download]
    (or maybe, in the case of an empty-set result from the query, you want to do something more explicit that just returning undef -- it's your call).

    BTW, I think the default behavior for the "execute()" call in DBI/DBD::mysql is to include the SQL statement text in the error message it writes to STDERR if the statement fails to execute for whatever reason (so you probably don't need to print it again in the "die" message.

    UPDATE: I think I misunderstood... You are calling this routine twice in a given run of your script, the "where" clause of the sql statement is different on each call, only the first calls gives a return value, whereas with the command-line mysql client, both queries give return values -- is that what you meant to say?

    If so, the question becomes: what is different about the where clause? Is it the sort of difference that you should be handling by putting placeholders in the query, preparing the statement only once, and then executing it twice with different values passed in the execute call for the placeholder? If the difference involves anything in the where clause that ought to be "escaped", then using placeholders will fix that. In any case, you may need to show us the text of your two sql statements...

[reply]
[d/l]

      Only a very small point but it is unnecessary to call finish after fetchall_arrayref and the DBI documentation for finish suggests you should use it with care.

[reply]
      Thanks for the comeback. Turns out it was a MSO (machine smarter than operator) problem. I was connected to two different dbs with the same table. :-/ One had the record, the other didn't.
[reply]

Back to Seekers of Perl Wisdom