> On Apr 5, 2016, at 11:55 AM, Bruce Ferrell <bferr...@baywinds.org> wrote:
>
> Ick!
>
> ok, I have to dynamically build the IN clause of the prepare as a static sql
> statement
Yep. This is how I do it for a given array of values:
# Create a string of placeholder characters, with one ? character
# per element in an array of values.
my @values = (1, 2, 3, 4, 5);
my $str = join (",", ("?") x @values);
Then interpolate $str into your query string.
>
> 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?
>>
>>
>
