I have a relatively simple DB query that is giving very strange results. Have you seen anything like this before or do you have any ideas about how I might debug the problem?

print "Content-type: text/plain\n\n";

my $point  = "POINT( $data{'lng'} $data{'lat'} )";
my ($usrn) = $dbh->selectrow_array("SELECT idUSRN FROM USRN WHERE idUS
+RN > 0 ORDER BY ST_Distance_Sphere( ST_GeomFromText( ?, 4326 ), cente
+r ) LIMIT 1", undef, $point);
#my ($usrn) = $dbh->selectrow_array("SELECT idUSRN FROM USRN LIMIT 1")
+;

print $dbh->errstr;

print "\n$point $usrn\n";
print "SELECT idUSRN FROM USRN ORDER BY ST_Distance_Sphere( ST_GeomFro
+mText( '$point', 4326 ), center ) LIMIT 1";
exit;

[download]

I am getting a value of $usrn of zero. Despite the query excluding zero in the WHERE clause and there being no zero for idUSRN in the database table!

This placeholder syntax works fine elsewhere for me. However, just in case this was the problem I have tried removing the placeholder and writing ST_Distance_Sphere( ST_GeomFromText( '$point', 4326 ), center ) and this also gives zero.

But...when I copy the select statement which is printed and paste it into MySQL Query Browser connected to the same database schema, I get an eight-digit integer as expected.

DBI is not displaying an error so no clues there!

Swapping commenting out of the queries gives a sensible, non-zero, result.

Any ideas on what I can try to get to the bottom of this would be very much appreciated. It seems weird that the same query works differently from Perl than it does when run directly against the database.

In reply to DBI returns zero! by Bod

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.