Given the limitations of some SQL DBMSs to pass arrays (or tables of a single column), Chris Howard's solution is probably the best one in regards for maintaining data types and such.
Another alternative is to use an encoding/decoding process for transport, so you can fake passing an array value.
How this works is that on the Perl side you join the list of array elements into a single string value with some kind of separator, which could be a comma or a vertical bar or whatever you like.
On the SQL side you have a prepared statement with just the 1 placeholder for a string value, and this is an argument to a SQL function that splits the elements into a SQL ARRAY or TABLE or ROW and then that is the input to the IN.
The second solution may require a more advanced DBMS rather than a less advanced one but it is functionally closest to what you wanted to do, aside from having actual array-valued parameters. The temporary table solution would work on more SQL DBMSs but it means there's the separate loading operation. Either way though, the main query only has to be PREPAREd once.
-- Darren Duncan On 2016-04-05 10:44 AM, Howard, Chris wrote:
Insert "in" values in a table. Run the query with a sub-select or join against the table. -----Original Message----- From: Paul DuBois [mailto:p...@snake.net] Sent: Tuesday, April 05, 2016 11:37 AM To: Vaughan, Mark Cc: Bruce Ferrell; dbi-users@perl.org Subject: Re: suppress quoting in prepared sqlOn 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 sqlOn 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 statementYep. 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?
