On Thu, Nov 21, 2002 at 03:12:47PM -0800, Daniel Olson wrote:
> I have a Perl script acting as a CGI processor with many values being passed,
> and the values can possibly be empty. This seems to cause problems. I have a
> prepare statement with 19 columns with 17 columns of variable data. I'm trying
> to set up the execute statement properly so it handles arguments that are
> defined as well as those that aren't. If all the arguments have values, it all
> works swimmingly. The code looks like this:
>
> $sth = $dbh->prepare("insert into ul_info
>(ul_id,ul_len,ul_wid,ul_dep,ul_tun,ul_sin,ul_und,ul_und_fr,ul_und_to,ul_edge,ul_stat,ul_skin,ul_drain_typ,ul_drain_amt,ul_drain_col,ul_drain_od,ul_stage,timestamp,clinician)
> values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,'')");
>
>$sth->execute($UlID,$formdata->param('Len'),$formdata->param('Wid'),$formdata->param('Dep'),$formdata->param('Tun'),$formdata->param('Sin'),$formdata->param('Und'),$formdata->param('UStart'),$formdata->param('UEnd'),$formdata->param('DEdge'),$formdata->param('DEval'),$formdata->param('DSkin'),$formdata->param('DType'),$formdata->param('DAmt'),$formdata->param('DColor'),$formdata->param('DOdor'),$newstage)
> || die "Can't perform execute: $DBI::errstr";
>
> If I run this from the command line and only define three variables, it fails
> with the error:
>
> DBD::Oracle::st execute failed: called with 5 bind variables when 17 are needed at
>script.cgi line 201.
>
When you call $cgi->param('param') in a list context and there is no value
for 'param', it returns an empty list. (And if there are multiple values,
it returns a list of all the values.) Thus, all the parameters without
values effectively disappear from the argument list to execute().
> So I thought I'd put all the variables in quote to try to get blank values to
> be quoted and understood. I replaced the last line of code above with:
>
>
>$sth->execute($UlID,$dbh->quote($formdata->param('Len')),$dbh->quote($formdata->param('Wid')),$dbh->quote($formdata->param('Dep')),$dbh->quote($formdata->param('Tun')),$dbh->quote($formdata->param('Sin')),$dbh->quote($formdata->param('Und')),$dbh->quote($formdata->param('UStart')),$dbh->quote($formdata->param('UEnd')),$dbh->quote($formdata->param('DEdge')),$dbh->quote($formdata->param('DEval')),$dbh->quote($formdata->param('DSkin')),$dbh->quote($formdata->param('DType')),$dbh->quote($formdata->param('DAmt')),$dbh->quote($formdata->param('DColor')),$dbh->quote($formdata->param('DOdor')),$dbh->quote($newstage))
> || die "<H4>Can't perform execute: $DBI::errstr</H4>";
>
You must not use quote() and placeholders on the same values. A
placeholder already does an implicit quote() of the value; if you call
quote() explicitly as well, then your value gets two levels of quoting.
> With only three variables defined, that fails with the error:
>
> DBI quote: invalid number of parameters: handle + 0
> Usage: $h->quote($string [, $data_type ]) at script.cgi line 200.
>
> quote apparently doesn't like not having a string to work on. Plus, after
> further investigation, I discovered that quote returns a NULL value when
> reading NULL as input, so it wouldn't do what I wanted anyway.
>
> I'm pretty sure I can make this work by preprocessing each argument
> individually, setting a variable to either the argument if it's defined or
> the empty string '' if not. It just seems awkward. Can anyone offer any
> suggestions?
Basically, you need use $cgi->param() in a way that ensures you get exactly
one value for each parameter, either the actual value or undef if there is
no value. This may be putting scalar() around each call, or assigning each
value to a scalar variable ahead of time.
Sorry I haven't provided more detail, I've got to run. That should be
enough to explain the issue at least.
Hope that helps!
Ronald
