Using placeholders in OFFSET clause of SELECT statement

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

When I attempt to use the next query:

SELECT news.id FROM news LIMIT 1 OFFSET ?

And in a code I write

$offset = int(rand(100));
$sth->execute($offset);
[download]

I get the following error back from DBD::mysql

Software error:

DBD::mysql::st execute failed: You have an error in your SQL syntax;

check the manual that corresponds to your MySQL server version for the right syntax to use near ''55'' at line 1.

I understand that placeholder is not allowed in LIMIT or OFFSET clause of SELECT statement.

How can I overcome this problem?

Comment on Using placeholders in OFFSET clause of SELECT statement Select or Download Code

Replies are listed 'Best First'.
Re: Using placeholders in OFFSET clause of SELECT statement by tinita (Parson) on Apr 29, 2010 at 11:18 UTC
Placeholders are actually allowed (at least in newer versions) but by default DBD::mysql puts quotes around the parameter, and the quotes are not allowed for the OFFSET. To prevent that, use bind_param() instead: `use DBI qw(:sql_types); $sth->bind_param(1, $offset, SQL_INTEGER);` [download]	[reply] [d/l]
Re: Using placeholders in OFFSET clause of SELECT statement by ikegami (Patriarch) on Apr 29, 2010 at 01:26 UTC
`my $offset = int(rand(100)); my $sth = $dbh->prepare("$stmt LIMIT 1 OFFSET $offset"); $sth->execute();` [download]	[reply] [d/l]
Re^2: Using placeholders in OFFSET clause of SELECT statement by yakoval (Novice) on Apr 29, 2010 at 19:23 UTC
Thank you! This way I got rid of the error!	[reply]
Re: Using placeholders in OFFSET clause of SELECT statement by balakrishnan (Monk) on Apr 29, 2010 at 08:03 UTC
I suggested you to enable the tracing on DBI handle during the misbehaviors happens, `$dbh->trace( 2 );` [download] That will give you the clear idea about where to lay your hands.	[reply] [d/l]