Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
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());
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'); ...
) 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!

In reply to DBI indexable placeholder by grump-

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others lurking in the Monastery: (3)
    As of 2014-09-22 23:47 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      How do you remember the number of days in each month?











      Results (208 votes), past polls