Hi,
This is a problem that I have encountered with the substitution of bind
values into an SQL statement. This is a generalized statement of the
SQL that I am trying to get to work:
$statement = q{
SELECT *
FROM table
WHERE field1 = ?
AND field2 = ?
ORDER BY fieldname
LIMIT ?
, ?
};
Here is how the statement is executed:
@queryresults = $dbh->selectall_arrayref( $statement, {
dbi_fetchall_arrayref_attr = {} }, @bind_values, $offset, $rows );
Here is one set of bind values that works on the substitution:
@bind_values = [ 1, '%data%' ];
$offset = 0;
$rows = 30;
The first field is a numeric field in the database. The second field is
a string field in the database. The last two bind values are
substituted into the limit part of the SQL query. With those bind
values passed in, the query executes properly with no errors.
When the '$offset' is changed to 30, I get the error:
DBD::mysql::db selectall_arrayref failed: You have an error in your SQL
syntax near ''30'
, 30' at line xx
If I set the $offset to any value other than zero (0), I get the error
but the first number is whatever I change the $offset to. Here are some
other things that I have tried to get it to work:
@queryresults = $dbh->selectall_arrayref( $statement, {
dbi_fetchall_arrayref_attr = {} }, @bind_values, 0 + $offset, $rows );
@queryresults = $dbh->selectall_arrayref( $statement, {
dbi_fetchall_arrayref_attr = {} }, @bind_values, $offset + 0, $rows );
@queryresults = $dbh->selectall_arrayref( $statement, {
dbi_fetchall_arrayref_attr = {} }, @bind_values, int($offset), $rows );
None of these worked. It still seems to quote the number coming into
the statement which causes the SQL engine to complain. In the program
that I am using, the $offset value is passed through HTML::FormValidator
and is run through a field_filter which is 'sub { return (shift =~
/^([0-9]+)$/)[0] }'. This should return a number only.
If I hardcode a numeric value of 30 into selectall_arrayref where
$offset is, it works. But if the $offset is set to 30 and used in the
selectall_arrayref, it fails.
I am using DBI version 1.18
Is there a way to tell it to NOT quote that number? The number of
values in the @bind_values changes depending on the data that is
submitted to the cgi script.
Thanks for any help that you might be able to provide,
Trevor Schellhorn
Internet Marketing Center
