Re: suppress quoting in prepared sql

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]>
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?
>
>
>