Re: suppress quoting in prepared sql

On Tue, April 5, 2016 09:24, Bruce Ferrell 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?
First, you don't say that you are using (@results) = $sth->fetchrow_array.
Second, I'm not sure you can use a ? to replace multiple entries.  I'd
assemble the result using a perl join function and then put that in.  But
not using a ?
------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154