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
+;
}

[download]
$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 (Monsignor) 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.

[reply]
[d/l]
[select]
      Thank you for your advice, I will look into this
[reply]
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;
}

    [download]
    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.
[reply]
[d/l]
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=?");

    [download]

    -Paul

[reply]
[d/l]
      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....

      [download]
      is this because it is a sql string and not a number. The sqlstring, when executed alone returns a number. TIA
[reply]
[d/l]
        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.

        -Paul

[reply]
        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 = 
+?");

        [download]
        From the error message, it seems like you are assigning RESULTS with something like SELECT.
[reply]
[d/l]

Back to Seekers of Perl Wisdom