Re: suppress quoting in prepared sql

Excellent suggestion and exactly what I did moments before it came in.

Thanks all

On 4/5/16 9:50 AM, Geoffrey Rommel wrote
> The general rule for using parameter markers ('?'s) is that a 
> parameter marker can appear wherever a literal can appear. (As far as 
> I know, this is true in any ANSI-compliant database; it goes back to 
> the early years of DB2 and SQL/DS.) The substituted parameters 
> are equivalent to literals. Hence, it makes sense that MySQL would 
> interpret the list of values as a single string.
>
> One way of getting around this would be to substitute the value in the 
> perl script before passing it to the database:
>
> "select sum(column) as columnSum from table where value in ( $ValueIDs 
> ) and row_date between cast( ? as date) and cast( ? as date) "); ... 
> but this might not be appropriate in your application.
>
>
>
> On Tue, Apr 5, 2016 at 12:24 PM, Bruce Ferrell <[email protected] 
> <mailto:[email protected]>> wrote:
>
>     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?
>
>
>