Re: suppress quoting in prepared sql

Replying to myself...

Postgres can do what I speak of directly, see 
http://search.cpan.org/~turnstep/DBD-Pg/Pg.pm#Array_support but if MySQL doesn't 
support that or something similar, there's another of many reasons for you to 
use Postgres instead.

-- Darren Duncan

On 2016-04-05 6:28 PM, Darren Duncan wrote:
> 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?
>>>>>
>>>>>
>>>>
>>>
>>
>>
>
>