Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Unanswered: Perl DBI mismatch placeholder and variables problem

    I can't my update to recognize both placeholders... strange... I have this string:

    DBD::Oracle::st execute failed: called with 3 bind variables when 1 are needed [for Statement "("update menus_completed set SUB1 = ? where rsvp_code = ? and menu_number = ?") || die $odbrmh::dbh->errstr;"

    code synopsis: read a table of old values, use the form input to replace updated fields...


    # here I'm reading in 8 vars, named $sub1, $sub2...$sub8... I use the eval # to let me process without having to hard code the var names... I have
    # tested the variable names with print statements, they are being
    # populated..

    # fields are laid out like this: 00/00/"180 bytes of data". I split the field into 3
    # different fields.. first and second fields are counts, 2 byte number fields...


    while ($i < 9) {

    $temp = eval('$sub'.$i);
    if ($temp) {
    @str1 = split("/", $temp);

    # did the split, so now arrray @str1 has the 3 fields, 00, 00, and the rest.

    # here I have input values from the form, these'll overlay the 00, 00 fields.
    # I have printed these to verify they have valid data...

    $temp1 = ("$FORM{subcnt.$i.h}");
    $temp2 = ("$FORM{subcnt.$i.w}");

    # string 'em all together... now I've got my replacement field to be uploaded
    # to the table...

    my $temp3 = ("$temp1"."/"."$temp2"."/"."$str1[2]");

    # my db fieldnames are SUB1, SUB2, SUB3...SUB8... So I need to set up a
    # string including the loop value ($i) to interate each time...

    $temp4 = ("SUB$i");

    # now build the 2 halves of the prepare statement....

    $sth01 = '("update menus_completed set ';
    $sth02= ' = ? where rsvp_code = ?") || die $odbrmh::dbh->errstr;';

    # build entire prepare statement, using the 2 halves, and the lit created in
    # $temp4
    $sth03 = ("$sth01"."$temp4"."$sth02");

    # do the prepare

    my $sth = $odbrmh::dbh->prepare("$sth03");

    # execute...

    $sth->execute($temp3, "$FORM{pcode}") || die $sth->errstr;

    } # subs

    $i++; # iterate loo[ index...
    } # back to while loop...

    }

    as you can see from the error log excerpt at the top, the statement build is working perfectly... I can't understand why he can't SEE and PROCESS the other placeholder... any ideas?

  2. #2
    Join Date
    Aug 2011
    Posts
    4

    update, loaded wrong version of code...

    Correct message for this version of the code:

    [Thu Aug 18 16:04:27 2011] [error] [client 69.97.209.119] DBD::Oracle::st execute failed: called with 2 bind variables when 1 are needed [for Statement "("update menus_completed set SUB1 = ? where rsvp_code = ?") || die $odbrmh::dbh->errstr;"

    I tried to increase the ?'s and the bind variables, to see if I could see what might be causing the problem that's why the previous post didn't match the error message... sorry.. Here's the latest version I ran with, and verified the input for:


    # here I'm reading in 8 vars, named $sub1, $sub2...$sub8... I use the eval # to let me process without having to hard code the var names... I have
    # tested the variable names with print statements, they are being
    # populated..

    # fields are laid out like this: 00/00/"180 bytes of data". I split the field into 3
    # different fields.. first and second fields are counts, 2 byte number fields...


    while ($i < 9) {

    $temp = eval('$sub'.$i);
    if ($temp) {
    @str1 = split("/", $temp);

    # did the split, so now arrray @str1 has the 3 fields, 00, 00, and the rest.

    # here I have input values from the form, these'll overlay the 00, 00 fields.
    # I have printed these to verify they have valid data...

    $temp1 = ("$FORM{subcnt.$i.h}");
    $temp2 = ("$FORM{subcnt.$i.w}");

    # string 'em all together... now I've got my replacement field to be uploaded
    # to the table...

    my $temp3 = ("$temp1"."/"."$temp2"."/"."$str1[2]");

    # my db fieldnames are SUB1, SUB2, SUB3...SUB8... So I need to set up a
    # string including the loop value ($i) to interate each time...

    $temp4 = ("SUB$i");

    # now build the 2 halves of the prepare statement....

    $sth01 = '("update menus_completed set ';
    $sth02= ' = ? where rsvp_code = ?") || die $odbrmh::dbh->errstr;';

    # build entire prepare statement, using the 2 halves, and the lit created in
    # $temp4
    $sth03 = ("$sth01"."$temp4"."$sth02");

    # do the prepare

    my $sth = $odbrmh::dbh->prepare("$sth03");

    # execute...

    $sth->execute($temp3, "$FORM{pcode}") || die $sth->errstr;

    } # subs

    $i++; # iterate loo[ index...
    } # back to while loop...

    }

    as you can see from the error log excerpt at the top, the statement build is working perfectly... I can't understand why he can't SEE and PROCESS the other placeholder... any ideas?

  3. #3
    Join Date
    Aug 2011
    Posts
    4

    attempt with explicit binds...

    tried with explicit binds.. got this message:

    [Thu Aug 18 19:27:15 2011] [error] [client 75.197.246.254] Can't bind unknown placeholder '1' (1) at ..................../update.pl line 73

    seems like there's a construction error, but the statement looks clean... here's the code snip with the explicit binds...

    while ($i < 9) {
    print 'loop';
    $temp = eval('$sub'.$i);
    if ($temp) {
    @str1 = split("/", $temp);
    print 'exists';
    $temp1 = ("$FORM{subcnt.$i.h}");
    $temp2 = ("$FORM{subcnt.$i.w}");
    $temp3 = ("$temp1"."/"."$temp2"."/"."$str1[2]");
    $temp4 = ("SUB$i");
    $sth01 = '("update menus_completed set ';
    $sth02= ' = ? where rsvp_code = ?") || die $odbrmh::dbh->errstr;';
    $sth03 = ("$sth01"."$temp4"."$sth02");
    my $sth = $odbrmh::dbh->prepare("$sth03");
    $sth->bind_param(1, $temp3 );
    $sth->bind_param(2, $FORM{pcode} );

    $sth->execute() || die $sth->errstr;
    print 'upd';
    } # subs

    $i++;
    }

  4. #4
    Join Date
    Aug 2011
    Posts
    2
    It looks like your prepare statement is not a SQL statement. In your code snippet, you have '|| die ...' in the prepare statement ?? I would also suggest you remove the parentheses in your $temp(1|2|3) variables. They're just strings right?

    the syntax is: dbh->prepare(SQL_STATEMENT)

    In your case,you can try something like:
    my $sth = $odbrmh::dbh->prepare("update menus_completed set SUB".$i." = ? where rsvp_code = ?") || die $odbrmh::dbh->errstr;

    $sth->bind_param(1, $temp3 );
    $sth->bind_param(2, $FORM{pcode} );

    $sth->execute() || die $sth->errstr;

  5. #5
    Join Date
    Aug 2011
    Posts
    4

    next problem

    OK, that seemed to get me past that hurdle, but now I cannot get an update to actually write data... Weird... I even hard-coded an update to just write text to the fields, and nothing... No log messages...
    Do you know how to trace in DBI? I have used this stuff (DBI) for years, and never had this much trouble... But, there's something wrong and I'm not sure what... I figured Trace was my next best thing...

    btw, this is on a a hosted account, and I only have access to my site's error log... it's a shared instance of Oracle... I'm going to ask in the interim for any other messages they might be able to get me but help with trace would rock if you have an example or link to some doc...

    thanks...

    Roy Holliday

  6. #6
    Join Date
    Aug 2011
    Posts
    2
    to enable tracing:

    Level of tracing 1 thru 4
    ### Set trace output to a file at level 2 and prepare()
    DBI->trace( 2, 'dbitrace.log' );

    More info on tracing: Programming the Perl DBI: Chapter 4: Programming with DBI

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •