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