> On Apr 5, 2016, at 12:29 PM, Vaughan, Mark <mark.vaug...@neustar.biz> wrote: > > This works if the number of elements remains static. You'd have to run the > prepare again if the number of elements changes.
Sure. But that's true no matter how you construct your statement to be prepared. > > 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? >>> >>> >> >
