DBI indexable placeholder

grump- has asked for the wisdom of the Perl Monks concerning the following question:

I have some queries that require inputting the same data in multiple places. Previously I have just been using question marks and forcing the client to send the same data as many times as the question marks appear. If two question marks requiring the same input, then send the input twice. But I have started using the custom or named placeholders and I have noticed that I can bind in a pseudo index able way. This code works against an Oracle database (with real query, this one hacked for simplicity sakes):

#!/usr/bin/perl
use DBI;
use Data::Dumper;

my $dbh = DBI->connect( dsn, user, pass, { RaiseError => 1 } );

my $query = qq{
select * from blah_table where col1 = :p0
  union select * from blah_table where col1 = :p0
    union select * from blah_table where col1 = :p0
  };

my $dbhq = $dbh->prepare( $query );

my @args = ('foo_param');

while( $query =~ /(:p(\d+))/g ) {   #$1 will be :p0, $2 will be 0
  $dbhq->bind_param($1, @args[$2]); #':p0 gets @args[0]
}

$dbhq->execute();

print Dumper($dbhq->fetchall_arrayref());
[download]

I hate to just accept that 'if it works, use it' because I might end up down the road in trouble having used poor practice. If someone has done this successfully for some time I would like to know. Even if it does work, is it good practice? I had the thought that if I could test multiple overlapping placeholders successfully (e.g. change query and args to:

...
my $query = qq{
select * from blah_table where col1 = :p0
  union select * from blah_table where col1 = :p0
    union select * from blah_table where col1 = :p1
      union select * from blah_table where col1 = :p0
        union select * from blah_table where col1 = :p1
  };

...
my @args = ('foo_param0', 'foo_param1');
...
[download]

) that might prove to me that it is o.k. to use but even so, some input from experts would be nice. Any input would be welcome. Thanks!

Comment on DBI indexable placeholder Select or Download Code

Replies are listed 'Best First'.
Re: DBI indexable placeholder by runrig (Abbot) on Jan 04, 2013 at 17:52 UTC
You only need to bind each name once. Your while loop would bind multiple occurances of ':p0' multiple times. Shouldn't hurt, but is a waste. Would be better to keep track of what you've bound with a hash, or better yet, know what parameters need to be bound for each sql statement, and just bind those rather than scanning through the statement to find them. And internally, Oracle seems to translate '?' placeholders into ':p0', ':p1', ':p2', etc., though you can not mix named with unnamed placeholders.	[reply]
Re: DBI indexable placeholder by mje (Curate) on Jan 08, 2013 at 10:19 UTC
In addition to the other comments you've received and to answer your question, yes, named placeholders are supported by DBD::Oracle and I'd say they are good practise so long as you are only using DBD::Oracle. You can use a string preceded by a colon to name each parameter in the SQL. DBD::Oracle even supports (as you have found) binding a named placeholder once but referring to it multiple times in the SQL. However, you should note that although most DBDs support named placeholders not all of them support using the named placeholder more than once in the SQL (e.g., DBD::ODBC - patches welcome)	[reply]
Re: DBI indexable placeholder by locked_user sundialsvc4 (Abbot) on Jan 07, 2013 at 22:49 UTC
I had to do something vaguely-similar to this once, and what I decided to do was to define each SQL-statement definition (there were hundreds of them) as consisting of SQL text, with the appropriate placeholders, and a separate list of parameter-names, which were taken from a list of `(use) constant`s. The number of entries in the parameter-name list had to exactly match the number of placeholders in the query definition (which was basically hard-coded into the app). Anyhow, to run a query you specified the name of the query and a hash of named parameters. When building the DBI request, the (positional...) parameters were resolved by name ... by my Perl code, before running the query. It was easy to write and worked very well in practice. I freely admit that I find the notion of a `:name` syntax to be quite intriguing, and instantly see how such a thing could be generally implemented (even to the point of idly wondering if it could be a Perl fee-chur, maybe through some mix-in package?). It would have been an improvement over my original idea to be sure.