My DBI suddenly can't handle placeholders in a LIMIT clause.

Cap'n Steve has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: My DBI suddenly can't handle placeholders in a LIMIT clause. by erroneousBollock (Curate) on Aug 10, 2007 at 06:58 UTC
Have you recently updated the DBD::mysql module on your system? (Either via CPAN or via package updates on your OS) If so, you may wish to read the changelog for DBD::mysql. -David	[reply]
Re^2: My DBI suddenly can't handle placeholders in a LIMIT clause. by Cap'n Steve (Friar) on Aug 10, 2007 at 08:06 UTC
I didn't, but my host might have and I don't know any way to be sure. A quick check of the DBD::mysql changelog doesn't seem to show anything related.	[reply]
Re: My DBI suddenly can't handle placeholders in a LIMIT clause. by clinton (Priest) on Aug 10, 2007 at 08:37 UTC
I remember a change in DBD::mysql a couple of years ago, where it suddenly became stricter about the type of data being used to fill in the placeholders - a number of scripts which previously worked, suddenly started to fail. It was as though the DBD was interpreting `"0"` as a string rather than as a number. As you have found, binding specific column/data types to each placeholder fixes the problem, and, as a bonus, adds a safety check to the data going to your DB. Clint	[reply] [d/l]
Re^2: My DBI suddenly can't handle placeholders in a LIMIT clause. by erroneousBollock (Curate) on Aug 10, 2007 at 08:45 UTC
I believe someone wrote DBD::mysql::AutoTypes do deal with those kinds of issues. Not sure if it's necessary any longer. -David.	[reply]
Re: My DBI suddenly can't handle placeholders in a LIMIT clause. by DrHyde (Prior) on Aug 10, 2007 at 11:43 UTC
You often need to do things like this with DBD::SQLite too. SQLite only does strings, but if you pass a number to the `$sth->execute()` method it gets terribly confused. An easier work-around though than explicitly passing the type is to say `$sth->execute('' . $number)`. Maybe `$sth->execute(0 + $number)` will convince DBD::Mysql in your case.	[reply] [d/l] [select]
Re^2: My DBI suddenly can't handle placeholders in a LIMIT clause. by Cap'n Steve (Friar) on Aug 11, 2007 at 04:42 UTC
Actually, doing math with the variable doesn't help. Perl must know it's a number but something in the database driver causes it to forget.	[reply]
Re: My DBI suddenly can't handle placeholders in a LIMIT clause. by Cody Pendant (Prior) on Aug 13, 2007 at 01:16 UTC
Cap'n Steve, are you by any chance using DreamHost? I just had the exact same symptoms on a DreamHost website. I've put in a service call to them about it. This seems to suggest that DBD::mysql version 2.9 causes this problem, and DreamHost do seem to have made changes to their mysql very recently. Nobody says perl looks like line-noise any more kids today don't know what line-noise IS ...	[reply]
Re^2: My DBI suddenly can't handle placeholders in a LIMIT clause. by Cap'n Steve (Friar) on Aug 13, 2007 at 01:52 UTC
Yes I am, and that's an interesting thread you linked to. I wonder why it was changed, though. Automatically using the appropriate type worked fine, and I don't see why this specifically affects LIMITs.	[reply]
Re^3: My DBI suddenly can't handle placeholders in a LIMIT clause. by Cody Pendant (Prior) on Aug 13, 2007 at 01:59 UTC
I assumed it was just a bug, or unintended feature. By the way, here's the rather cryptic DreamHost blog post on mysql 5 days ago: "while upgrading a package..." Nobody says perl looks like line-noise any more kids today don't know what line-noise IS ...	[reply]
Re: My DBI suddenly can't handle placeholders in a LIMIT clause. by Cody Pendant (Prior) on Aug 27, 2007 at 03:19 UTC
Just to get "closure" in the non-programming sense: Dreamhost eventually wrote back to me to say, yes, they had upgraded the version of the mysql driver. Not only that but they'd upgraded, found that lots of stuff fell over, so they downgraded again, but to a different version than the original one. I don't know if I dare put my placeholders back or not! Nobody says perl looks like line-noise any more kids today don't know what line-noise IS ...	[reply]
Re^2: My DBI suddenly can't handle placeholders in a LIMIT clause. by Cap'n Steve (Friar) on Aug 27, 2007 at 07:16 UTC
That reminds me, I actually did find (but can't find again) a mailing list discussion where they mentioned that using placeholders as part of the LIMIT was bad practice (although I have no idea why, or how that applies to DBI interpreting them as strings). For now, I'm just using the bind_param() workaround mentioned earlier because it's just prettier than building the query without placeholders.	[reply]