Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Re^2: Perl Mysql DBI, using placeholders with unknown variable count

by Haioken (Novice)
on Aug 18, 2009 at 10:20 UTC ( #789411=note: print w/ replies, xml ) Need Help??


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');
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) . ");";
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?


Comment on Re^2: Perl Mysql DBI, using placeholders with unknown variable count
Select or Download Code
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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://789411]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (5)
As of 2014-08-21 02:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (127 votes), past polls