Ick!
ok, I have to dynamically build the IN clause of the prepare as a static
sql statement
On 4/5/16 9:32 AM, Vaughan, Mark wrote:
>From the DBI documentation
(https://metacpan.org/pod/DBI#Placeholders-and-Bind-Values):
Also, placeholders can only represent single scalar values. For example, the
following statement won't work as expected for more than one value:
"SELECT name, age FROM people WHERE name IN (?)" # wrong
"SELECT name, age FROM people WHERE name IN (?,?)" # two names
You may have to prepare the query each time unless you have a fixed number of
elements in the IN clause.
HTH,
Mark Vaughan
Neustar, Inc. / Lead Consulting Services Consultant, Professional Services
8532 Concord Center Drive, Englewood, CO 80112, USA
Office: +1.303.802.1308 Fax: +1.303.802.1350 / mark.vaug...@neustar.biz
-----Original Message-----
From: Bruce Ferrell [mailto:bferr...@baywinds.org]
Sent: Tuesday, April 05, 2016 10:24 AM
To: dbi-users@perl.org
Subject: suppress quoting in prepared sql
I'm generating a sql statement like this:
sth = $mysql_dbh->prepare(
"select sum(column) as columnSum from table where value in ( ? ) and row_date
between cast( ? as date) and cast( ? as date) ");
sth->execute( $ValueIDs ,$week_start_date,$week_end_date);
$ValueIDs is a series of unquoted values:
01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01164
When observed at the mysql server, the sql appears as follows:
select sum(column) as columnSum where value in (
'01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01164' ) and
row_date between cast( '2016-03-29' as date) and cast( '2016-04-05' as date)
resulting in no data being returned.
When the sql is manually entered as follows:
select sum(column) as columnSum where value in (
01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01164 ) and
row_date between cast( '2016-03-29' as date) and cast( '2016-04-05' as date)
The correct values are returned.
How can I suppress the quoting for the IN clause?
