Using binding in order to pass values to a sql statement.

Hello all. I am trying to use bind_param to create a list of values in the f
orm of an array. Then I want to query the data base using a basic sql statem
ent where each value in my list (array) will be sent to the DBI in order to 
return a value. I am gettin
g and error that says can't call method "bind_param" on defined value at lin
e *&&. Here in the subroutine that will attempt to do this.



sub kill_porq{
my @banlist=qw(
222497190
291529832
285471249
280768305
276573798
278628710
297888281
297002394
271831939
127465
245710698
314086723
347803890
253926979
226200204
250702348
246597179
301791980
332270292
344909948
283580543
325838931
349835609
248436566
240499101
245185000
353432496
246334241
279430907
222800122
);
my $ban='';
foreach $ban (@banlist){


$sth->bind_param (1, "$ban");


$sth=$dbh->prepare("select request_no, ban, request_sts, status_act, NPAC_Pr
ocess_ind, external_req_no
from vstappo.port_request
where ban = ?
");


$sth->execute();

return;
}

>
> Hello all. I am trying to use bind_param to create a list of values in
the form of an array. Then I want to query the data base using a basic
sql statement where each value in my list (array) will be sent to the
DBI in order to return a value. I am getting and error that says can't
call method "bind_param" on defined value at line *&&. Here in the
subroutine that will attempt to do this.
>
>
>
> sub kill_porq{
> my @banlist=qw(
> 222497190
<snip list>
> 222800122
> );
>  my $ban='';
>  foreach $ban (@banlist){
>
>
>  $sth->bind_param (1, "$ban");

Normally you would prepare the statement once, since it doesn't change.
If you prepare it each time through the loop you lose the efficiency
gain. So move the prepare before the foreach.

That will also solve the scoping problem you have, are you using
'strict'?  $sth in the above line should not yet be defined. You should
have,

my $sth;

Inside the loop, this would help you see the issue.

>
>
>  $sth=$dbh->prepare("select request_no, ban, request_sts, status_act,
NPAC_Process_ind, external_req_no
>   from vstappo.port_request
>    where ban = ?
>  ");
>
>
>  $sth->execute();
>

You can also call execute with your bind params in the call, so that you
only need to make one method call, so,

$sth->execute($ban);

Should be sufficient without the 'bind_param' call at all.

Of course you aren't doing anything with $sth here?  And you will need
to store your result set as each execute on the $sth will clear the
previous one (I believe).

Alternatively you could build a "better" statement using the $ban list
and pass all of it to the database at once to get all of the data in a
single execute.

> return;
> }
>
>

HTH,

http://danconia.org