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...

In reply to Re: DBI not returning value that MySQL client does by graff
in thread DBI not returning value that MySQL client does by cormanaz

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.