Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

DBI place holder for sub query problem

by Anonymous Monk
on Jan 02, 2008 at 16:29 UTC ( #659996=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am using DBI and Oracle 10. I want to use placeholders as I have read this is the best way to do things. my code looks like this
$sth=$dbh->prepare("Update Customers set RESULTS =('?') where rec_no=? +"); foreach my $row ( @rows ) { $sth->execute($row->{'SQL'},$row->{'REC_NO'}) or die $dbh->errstr +; }
$row->{'SQL'} contains the sql I want to execute for the sub query, $row->{'REC_NO'} contains a unique integer. I get error DBD::Oracle::st execute failed: called with 2 bind variables when 1 are needed . Please advize how to use place holders properly with a sub query. TIA

Replies are listed 'Best First'.
Re: DBI place holder for sub query problem
by dsheroh (Prior) on Jan 02, 2008 at 16:55 UTC
    I don't think it's possible to get that to work the way you want.

    The way that the prepare/execute combo works is that the prepare parses the query and figures out the most efficient way to run it, then execute just plugs in a few constants to define the details. A subquery, however, isn't a constant, it's something else which has to be parsed and planned along with the main query, so I really, really doubt that it's possible to use one as a parameter.

    Probably the best you're going to be able to do is move the prepare into the loop, change it to prepare_cached, and put the actual subquery into the statement you're preparing. You're going to have to do separate preparation for each subquery, but prepare_cached should at least prevent you from repeating your work when/if multiple rows have identical subqueries.

      Thank you for your advice, I will look into this
Re: DBI place holder for sub query problem
by jZed (Prior) on Jan 02, 2008 at 19:08 UTC
    Generally, placeholders can only hold the place of *values*, not structural elements like table and column names and subselect queries. You can (for most DBDs, I believe) use placeholders for values in a subquery so something like this might work:
    $sth=$dbh->prepare(" UPDATE tableA SET colA =( SELECT colB FROM tableB WHERE colC=? ) WHERE tableA.rec_no=? "); foreach my $row ( @rows ) { $sth->execute( $row->{'tableB.value'}, $row->{'tableA.value'} ) or die $dbh->errstr; }
    Note that the order of the values in the execute is the order of the placeholders in the SQL, not the order the tables are opened.
Re: DBI place holder for sub query problem
by jettero (Monsignor) on Jan 02, 2008 at 16:36 UTC

    The DBI handles the quoting on it's own (the reason you use the place holders)... Perhaps this'll work better?

    $sth=$dbh->prepare("Update Customers set RESULTS =? where rec_no=?");


      Thank you for your input, but when I tried it I get
      DBD::Oracle::st execute failed: ORA-01722: invalid number (DBD ERROR: +error possibly near <*> indicator at char 30 in 'Update Customers set + RESULTS =:<*>p1....
      is this because it is a sql string and not a number. The sqlstring, when executed alone returns a number. TIA
        If it's not a bind variable then it's not a bind variable. The bind vars get meta-quoted automagically, sql probably should not be, so I don't think you can use a bind variable to do that job.


        If you are using a sub-query then the parenthesis are needed. Just omit the single quotes.
        $sth=$dbh->prepare("Update Customers set RESULTS = (?) where rec_no = +?");
        From the error message, it seems like you are assigning RESULTS with something like SELECT.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://659996]
Approved by jettero
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (7)
As of 2017-09-25 22:35 GMT
Find Nodes?
    Voting Booth?
    During the recent solar eclipse, I:

    Results (291 votes). Check out past polls.