Re: sql, dbi, placeholders and 'in'

[Abigail <abigail@abigail.nl>]

On Mon, Sep 20, 2004 at 04:05:56PM -0400, Gay, Jerry wrote:
> i have a bunch of sql queries that have the form:
> 
> 	select fields
> 	from table
> 	where
> 	 product in ('a', 'b', 'c', ...)
> 	 and
> 	 document in ('1', '2', '3', ...)
> 	;
> 
> i don't know up front how many values will be in the in() clause, and i
> can't seem to find a way to easily build these queries and run them through
> dbi with placeholders.
> 
> is there a module that will help me create these queries on the fly, or am i
> stuck with concatenating the bits together like
> 
> my $sql= 'select fields from table where '
> 	. 'product in (' . join( ', ' => ( ('?')x@products) ) . ')'
> 	. 'document in (' . join( ', ' => ( ('?')x@documents) ) . ')'
> ;


sub __ {join ", " => '?' x @_}

my $sql = sprintf <<'--', __ (@products), __ (@documents);
    SELECT  fields
      FROM  table
     WHERE  product  IN (%s)
       AND  document IN (%s)
--


Abigail
-
**Majordomo list services provided by PANIX <URL:http://www.panix.com>**
**To Unsubscribe, send "unsubscribe phl" to majordomo@lists.pm.org**