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,

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

Re: suppress quoting in prepared sql

I'd happily take a patch to the DBI docs to mention the common use case described by this thread. Tim. On Tue, Apr 05, 2016 at 05:29:34PM +, Vaughan, Mark wrote: > This works if the number of elements remains static. You'd have to run the > prepare again if the number of elements changes. >

RE: suppress quoting in prepared sql

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

Re: suppress quoting in prepared sql

> On Apr 5, 2016, at 12:29 PM, Vaughan, Mark 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. >

RE: suppress quoting in prepared sql

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:

Re: suppress quoting in prepared sql

> On Apr 5, 2016, at 11:55 AM, Bruce Ferrell 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 ?

Re: suppress quoting in prepared sql

Excellent suggestion and exactly what I did moments before it came in. Thanks all On 4/5/16 9:50 AM, Geoffrey Rommel wrote The general rule for using parameter markers ('?'s) is that a parameter marker can appear wherever a literal can appear. (As far as I know, this is true in any

Re: suppress quoting in prepared sql

The general rule for using parameter markers ('?'s) is that a parameter marker can appear wherever a literal can appear. (As far as I know, this is true in any ANSI-compliant database; it goes back to the early years of DB2 and SQL/DS.) The substituted parameters are equivalent to literals. Hence,

RE: suppress quoting in prepared sql

From the DBI documentation (https://metacpan.org/pod/DBI#Placeholders-and-Bind-Values): 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

Re: suppress quoting in prepared sql

On Tue, April 5, 2016 09:24, Bruce Ferrell wrote: > 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

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