Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

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 chilling in the Monastery: (6)
As of 2017-12-16 19:08 GMT
Find Nodes?
    Voting Booth?
    What programming language do you hate the most?

    Results (458 votes). Check out past polls.