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 <bferr...@baywinds.org
<mailto:bferr...@baywinds.org>> 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?
