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

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.

Replies are listed 'Best First'.
Re: DBI returns zero!
by kcott (Archbishop) on Mar 12, 2022 at 21:18 UTC

    G'day Bod,

    This sounded familiar. I dug up "Recalcitrant placeholders" which uses selectrow_array() and includes:

    "Yet my code returns 0."

    I started to look through this but it's very long: I'll leave you to revisit and review.

    I did note that you showed a workaround (which, perhaps, might also be applicable here): 

    my $query = $crm->db->prepare("SELECT idPerson FROM Person WHERE email
+ = ? OR altEmail = ?");
$query->execute($data{'email'}, $data{'email'});
my $crid  = $query->fetchrow_array;
print "ERROR: " . $crm->db->errstr if $crm->db->err;

    [download]

    Your post contains unknown functions. You haven't shown the output of your print statements. The code is not self-contained: it's impossible to run this to troubleshoot.

    The only thing that leaps out at me as being a potential problem is: 

    my $point  = "POINT( $data{'lng'} $data{'lat'} )";

    [download]

    $point will end up as something like: 'POINT( arg1 arg2 )'. Should there be comma between the two arguments? Did you really want a string here? Have you quoted it correctly?

    As requested for that similar post from nine months ago, please provide an SSCCE. My reply to that contained an SSCCE which might be a useful example (or, at least, a starting point for one): "Re: Recalcitrant placeholders".

    Update: I started composing my reply before "Solved! (was: Re: DBI returns zero!)" was posted; but clearly posted my reply a short time afterwards. Glad to see I was on the right track with reference to "Recalcitrant placeholders". :-)

    — Ken

[reply]
[d/l]
[select]
Solved! (was: Re: DBI returns zero!)
by Bod (Parson) on Mar 12, 2022 at 20:20 UTC

    I've solved it...

    It was because Perl was in taint mode. Turning off taint mode remedied the problem. I've now re-written the query like this: 

    my $point = "POINT( $data{'lng'} $data{'lat'} )";
my $query = $dbh->prepare("SELECT idUSRN FROM USRN WHERE idUSRN > 0 OR
+DER BY ST_Distance_Sphere( ST_GeomFromText( ?, 4326 ), center ) LIMIT
+ 10");
$query->execute($point);
my $usrn = $query->fetchrow_array;

    [download]
    This works under taint mode...

    Previously we discussed a similar issue in Recalcitrant placeholders
    haukex managed to diagnose the problem to being specific to Perl 5.16 as explained in Re^11: Recalcitrant placeholders (updated x2)

[reply]
[d/l]

Back to Seekers of Perl Wisdom