in reply to Re: Perl Mysql DBI, using placeholders with unknown variable count
in thread Perl Mysql DBI, using placeholders with unknown variable count

I've also been painfully made aware that my search terms (@q_names) are being quoted, irrespective of SQL's datatype for this field, or of the data in my field. As an example: 
SELECT `status`, `rma_num`, `auth_id`, `vendor`, `part_num` FROM rma_l
+ist WHERE ('status') = ('0');

[download]
is now being generated, which returns 0 rows, as status is an integer. My code for $query has been extrapolated from your elegant example as: 
my $query = "SELECT "
    . join(", ", map { $dbh->quote_identifier($_) } @q_return)
    . " FROM rma_list WHERE ("
    . join(', ', ('?') x (keys %q_search))
    . ") = ("
    . join(', ', ('?') x @q_names)
    . ");";

[download]
It seems obvious to me that my code isn't placing these quotes on the search items, so I guess DBI's substitution is. Is there a way to force this off for integer only variables? Or a different substitution character?

Replies are listed 'Best First'.
Re^3: Perl Mysql DBI, using placeholders with unknown variable count
by moritz (Cardinal) on Aug 18, 2009 at 10:27 UTC
    If status is a column name, you may not quote it with $dbh->quote() or by using it as a placeholder - you must use $dbh->quote_identifier(). That is the problem, not the data type of the int.

    Sorry if I got that wrong in my first example.

    The rule is simply $dbh->quote_identifier() for column names and placeholders (?) for data.

    Perl 6 projects - links to (nearly) everything that is Perl 6.
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom