Missing a bind variable, but where?

DamnDirtyApe has asked for the wisdom of the Perl Monks concerning the following question:

I'm working on some code to store reports in a database, and I'm running into a wall on a simple DBI update. Here's the relevant code:

if ( $q->param( 'submit') ) {
    $db_sth = $db_dbh->prepare( <<'    END_OF_SQL' ) ;
        update
          report
        set
          dbms_name   = ?,
          report_name = ?,
          report_desc = ?,
          db_name     = ?,
          db_username = ?,
          db_password = ?,
          sql_query   = ?
        where
          report_id   = ?
    END_OF_SQL
    $db_sth->execute(
        $q->param( 'dbms_name' ),
        $q->param( 'report_name' ),
        $q->param( 'report_desc' ),
        $q->param( 'db_name' ),
        $q->param( 'db_username' ),
        $q->param( 'db_password' ),
        $q->param( 'sql_query' ),
        $q->param( 'report_id' )
    ) or die $db_sth->errstr ;
}
[download]

When I try and submit the page with the updates (meaning this block gets executed) I get the following error:

called with 7 bind variables when 8 are needed at /home/httpd/htdocs/d
+ora/edit_report.cgi line 60.
[download]

(line 60 is where the "execute" begins)

Now, no matter how many times I count it, I see 8 parameters in the execute call. Any ideas as to what I'm doing wrong?

_______________
DamnDirtyApe

Those who know that they are profound strive for clarity. Those who
would like to seem profound to the crowd strive for obscurity.
            --Friedrich Nietzsche

Comment on Missing a bind variable, but where? Select or Download Code

Replies are listed 'Best First'.
Re: Missing a bind variable, but where? by blokhead (Monsignor) on Feb 27, 2003 at 17:32 UTC
One of your form fields is undefined. CGI's param function returns an empty list when it doesn't have a param by that name, if it's called in list context as it is here. It's like writing `@a = (1,2,(),(),3);` -- it creates an array with 3 elements. Very frustrating when writing CGI/DBI code, isn't it? `;)` You'll need to do something like this to get those param calls out of list context.: `$db_sth->execute( $q->param( 'dbms_name' ) \|\| '', ## or \|\| undef $q->param( 'report_name' ) \|\| '', $q->param( 'report_desc' ) \|\| '', ...` [download] blokhead	[reply] [d/l] [select]
Re: Re: Missing a bind variable, but where? by DamnDirtyApe (Curate) on Feb 27, 2003 at 17:44 UTC
Yup, you had it right. I'd forgotten to put a name attribute on one of the form elements. Everything's working perfectly now, thanks to all for your suggestions! _______________ DamnDirtyApe Those who know that they are profound strive for clarity. Those who would like to seem profound to the crowd strive for obscurity. --Friedrich Nietzsche	[reply]
Re: Missing a bind variable, but where? by tachyon (Chancellor) on Feb 27, 2003 at 17:27 UTC
One of the param values is undef. You need to ensure that they are all defined. ie: `do { $q->param($_,'') unless defined $q->param($_) } for qw( dbms_name + ... etc )` [download] cheers tachyon s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print	[reply] [d/l]
Re: Missing a bind variable, but where? by Abigail-II (Bishop) on Feb 27, 2003 at 17:31 UTC
Passing in undefined values as bind values is perfectly OK. That's how you pass in NULL. Abigail	[reply]
Re: Re: Missing a bind variable, but where? by tachyon (Chancellor) on Feb 27, 2003 at 17:39 UTC
Not in this context it is not. In list context if one of the param values is undef CGI.pm will return an empty list, and thus we get a 7 not 8 member list passed and so the bind error: `@ary = ( (), ('val'),('val'),('val'),('val'),('val'),('val'),('val') ) +; print scalar @ary; __DATA__ 7` [download] Thus as I suggested defining the values or setting undef ones to 'NULL' will work fine. cheers tachyon s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print	[reply] [d/l]
Re: Missing a bind variable, but where? by Abigail-II (Bishop) on Feb 27, 2003 at 17:29 UTC
Sure, but the calls to `param` are done in list context. Are you sure non of the calls is returning an empty list? We don't know what `$q` is, so it's just a guess. Abigail	[reply] [d/l] [select]