Re: suppress quoting in prepared sql

The most elegant solution is to pass a single array-typed value from Perl to SQL 
somehow AS a single array-typed value, so that a single placeholder is appropriate.

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:[email protected]]
> Sent: Tuesday, April 05, 2016 11:37 AM
> To: Vaughan, Mark
> Cc: Bruce Ferrell; [email protected]
> Subject: Re: suppress quoting in prepared sql
>
>
>> On Apr 5, 2016, at 12:29 PM, Vaughan, Mark <[email protected]> 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  /  [email protected]
>>
>>
>> -----Original Message-----
>> From: Paul DuBois [mailto:[email protected]]
>> Sent: Tuesday, April 05, 2016 11:25 AM
>> To: Bruce Ferrell <[email protected]>
>> Cc: [email protected]
>> Subject: Re: suppress quoting in prepared sql
>>
>>
>>> On Apr 5, 2016, at 11:55 AM, Bruce Ferrell <[email protected]> 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  /
>>>> [email protected]
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: Bruce Ferrell [mailto:[email protected]]
>>>> Sent: Tuesday, April 05, 2016 10:24 AM
>>>> To: [email protected]
>>>> 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?
>>>>
>>>>
>>>
>>
>
>