Perl DBI Mistaking a Number For a String

{NULE} has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I'm befuddled because I Perl DBI program that's been running for over a year has suddenly started complaining about a prepared statement. I have a feeling that my system admin must have updated something, but this really shouldn't be something that breaks. I was hoping someone here has seen it and might be able to shed some light into why it happened suddenly. Here's the code (just the important parts):

my $sh = $dbh->prepare("select * from records where date <= ? order by
+ date asc, time asc limit ?,10000")
    || die "Could not prepare select: " . $dbh->errstr;
my $date = "2005-02-26";
my $n = 0;
$sh->execute($date, $n)
    || die "Could not execute select: " . $dbh->errstr;
[download]

This code dies because the $n value seems to be interpreted as a string, so the prepared statement ends up quoting it in the limit portion like so " limit '0',10000", which MySQL doesn't like.

Again this was working fine a few days ago and broke abruptly, I'm assuming, because something was updated. Has anybody seen this and better yet, knows of a work around? Thanks much,

{NULE}
--
http://www.nule.org

Comment on Perl DBI Mistaking a Number For a String Download Code

Replies are listed 'Best First'.
Re: Perl DBI Mistaking a Number For a String by Transient (Hermit) on Apr 21, 2005 at 13:45 UTC
If any arguments are given, then execute will effectively call "bind_param" for each value before executing the statement. Values bound in this way are usually treated as SQL_VARCHAR types unless the driver can determine the correct type (which is rare), or unless bind_param (or bind_param_inout) has already been used to specify the type. I suggest calling bind_param for each parameter and passing the correct bind type. Did you perhaps upgrade your MySQL or DBD::MySQL?	[reply]
Re^2: Perl DBI Mistaking a Number For a String by {NULE} (Hermit) on Apr 21, 2005 at 14:03 UTC
Hi again, I changed the code to use a bind_param and it seems to be working. This is what it looks like now: `my $sh = $dbh->prepare("select * from records where date <= ? order by + date asc, time asc limit ?,10000") \|\| die "Could not prepare select: " . $dbh->errstr; my $date = "2005-02-26"; my $n = 0; $sh->bind_param(1, $date, SQL_DATE); $sh->bind_param(2, $n, SQL_INTEGER); $sh->execute() \|\| die "Could not execute select: " . $dbh->errstr;` [download] It still seems a little odd that the code broke in this manner. Never the less, I appreciate the quick reply as this was a production problem and I was stuck. :) Thanks, {NULE} -- http://www.nule.org	[reply] [d/l]
Re^2: Perl DBI Mistaking a Number For a String by {NULE} (Hermit) on Apr 21, 2005 at 13:51 UTC
Thanks for the reply. MySQL certainly has not been updated, but DBD::MySQL may have been. I'm trying to reach my admin to find out if that's the case. I'll play with bind_param and see what I come up with. Thanks, {NULE} -- http://www.nule.org	[reply]
Re: Perl DBI Mistaking a Number For a String by cees (Curate) on Apr 21, 2005 at 14:35 UTC
Check out This thread for lots of info on placeholders in LIMIT clauses. http://www.issociate.de/board/post/9336/LIMIT_clause_and_placeholders.html Ideally you should not use placeholders in LIMIT clauses, because most databases that actually prepare statements will not support them. Of course if you are married to MySQL, that won't make too much of a difference, barring MySQL changing things. - Cees	[reply]
Re^2: Perl DBI Mistaking a Number For a String by {NULE} (Hermit) on Apr 21, 2005 at 19:36 UTC
Good information. You'll be happy to know that this is an old script and going away. I actually only needed it for one last run to archive off an old table. :) I've made a note to avoid placeholders in limit clauses in the future. Thanks, {NULE} -- http://www.nule.org	[reply]
Re^2: Perl DBI Mistaking a Number For a String by dragonchild (Archbishop) on Apr 21, 2005 at 19:43 UTC
Of course if you are married to MySQL, that won't make too much of a difference, barring MySQL changing things. Do you mean like this? My wife's blog	[reply]
Re: Perl DBI Mistaking a Number For a String by tantarbobus (Hermit) on Apr 21, 2005 at 15:23 UTC
In DBD::mysql 2.9006 you can set $dbh->{mysql_unsafe_bind_type_guessing}=2, and it will try and guess whether to bind as a string or as an int.	[reply]