I'd happily take a patch to the DBI docs to mention the common use case described by this thread.
Tim. On Tue, Apr 05, 2016 at 05:29:34PM +0000, Vaughan, Mark wrote: > This works if the number of elements remains static. You'd have to run the > prepare again if the number of elements changes. > > Mark Vaughan > Neustar, Inc. / Lead Consulting Services Consultant, Professional Services > 8532 Concord Center Drive, Englewood, CO 80112, USA > Office: +1.303.802.1308 Fax: +1.303.802.1350 / mark.vaug...@neustar.biz > > > -----Original Message----- > From: Paul DuBois [mailto:p...@snake.net] > Sent: Tuesday, April 05, 2016 11:25 AM > To: Bruce Ferrell <bferr...@baywinds.org> > Cc: dbi-users@perl.org > Subject: Re: suppress quoting in prepared sql > > > > On Apr 5, 2016, at 11:55 AM, Bruce Ferrell <bferr...@baywinds.org> wrote: > > > > Ick! > > > > ok, I have to dynamically build the IN clause of the prepare as a > > static sql statement > > Yep. This is how I do it for a given array of values: > > # Create a string of placeholder characters, with one ? character # per > element in an array of values. > > my @values = (1, 2, 3, 4, 5); > > my $str = join (",", ("?") x @values); > > Then interpolate $str into your query string. > > > > > On 4/5/16 9:32 AM, Vaughan, Mark wrote: > >> >From the DBI documentation > >> >(https://urldefense.proofpoint.com/v2/url?u=https-3A__metacpan.org_p > >> >od_DBI-23Placeholders-2Dand-2DBind-2DValues-29-3A&d=CwIF-g&c=MOptNlV > >> >tIETeDALC_lULrw&r=rwT9R07bCzfhX6apOj8NoPX-TbEkSSLuFkjri49xQ-0&m=QpMl > >> >4dk0ZSYHx2vhZSJDCeS1tdTQ9Z8GWCyZqgIjc28&s=2uZZNLLOkgh5xJfTn_SVli361r > >> >ZOaGOrDxGPv_yVwd8&e= > >> > >> Also, placeholders can only represent single scalar values. For example, > >> the following statement won't work as expected for more than one value: > >> > >> "SELECT name, age FROM people WHERE name IN (?)" # wrong > >> "SELECT name, age FROM people WHERE name IN (?,?)" # two names > >> > >> You may have to prepare the query each time unless you have a fixed number > >> of elements in the IN clause. > >> > >> HTH, > >> Mark Vaughan > >> Neustar, Inc. / Lead Consulting Services Consultant, Professional > >> Services > >> 8532 Concord Center Drive, Englewood, CO 80112, USA > >> Office: +1.303.802.1308 Fax: +1.303.802.1350 / > >> mark.vaug...@neustar.biz > >> > >> > >> -----Original Message----- > >> From: Bruce Ferrell [mailto:bferr...@baywinds.org] > >> Sent: Tuesday, April 05, 2016 10:24 AM > >> To: dbi-users@perl.org > >> Subject: suppress quoting in prepared sql > >> > >> 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,011 > >> 64 > >> > >> 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,01 > >> 164' ) 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,011 > >> 64 ) 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? > >> > >> > > >
