Re: suppress quoting in prepared sql

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  /  [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,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?
>
>