RE: suppress quoting in prepared sql

This works if the number of elements remains static. You'd have to run the prepare again if the number of elements changes.

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  /  [email protected]


-----Original Message-----
From: Paul DuBois [mailto:[email protected]] 
Sent: Tuesday, April 05, 2016 11:25 AM
To: Bruce Ferrell <[email protected]>
Cc: [email protected]
Subject: Re: suppress quoting in prepared sql


> On Apr 5, 2016, at 11:55 AM, Bruce Ferrell <[email protected]> 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://urldefense.proofpoint.com/v2/url?u=https-3A__metacpan.org_p
>> >od_DBI-23Placeholders-2Dand-2DBind-2DValues-29-3A&d=CwIF-g&c=MOptNlV
>> >tIETeDALC_lULrw&r=rwT9R07bCzfhX6apOj8NoPX-TbEkSSLuFkjri49xQ-0&m=QpMl
>> >4dk0ZSYHx2vhZSJDCeS1tdTQ9Z8GWCyZqgIjc28&s=2uZZNLLOkgh5xJfTn_SVli361r
>> >ZOaGOrDxGPv_yVwd8&e=
>> 
>> 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  /  
>> [email protected]
>> 
>> 
>> -----Original Message-----
>> From: Bruce Ferrell [mailto:[email protected]]
>> Sent: Tuesday, April 05, 2016 10:24 AM
>> To: [email protected]
>> 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,011
>> 64
>> 
>> 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,01
>> 164' ) 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,011
>> 64 ) 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?
>> 
>>  
>