0

In the code below, I have @some_array which is an array of arrays which contains text like name. So 

@some_array= ([sam, jon, july],[Mike, Han,Tommy],[angie, sita, lanny]);

Now when I query the list like sam jon july first and mike han tommy. Only the execute return the result from the first list others is undef. I don't know why any help will be appreciated.

If I switch the second to first place and run it it will show the first one only. So the problem is it is returning for the first set but for subsequent sets its returning undef. I did enable strict and warnings but it it didn't show any error.

my $pointer;
my $db = $db->prepare_cached("
    begin
            :pointer := myFun(:A1);
    end;
            ") or die "Couldn't prepare stat: " . $db->errstr;
$db->bind_param_inout(":pointer", \$pointer, 0, { ora_type => ORA_RSET });

for (my $i = 0; $i < @some_array; $i++) {
    my @firstarray = @{$some_array[$i]};
    my $sql = lc(join(" ", @firstarray));
    print "<pre>$sql</pre>\n";
    $db->bind_param(":A1", $sql);
    $db->execute();
    print "<pre>".Dumper($db->execute())."</pre>\n";
 }
Borodin
  • 126,100
  • 9
  • 70
  • 144
mysteriousboy
  • 159
  • 1
  • 7
  • 20

1 Answers1

1

I don't see how this can work at all, as execute returns the “number of rows affected” and not the result of the prepared statement. You also appear to have overwritten your database handle with the return from prepare_cached, which will destroy the handle and close the connection.

You should be looking at the $pointer variable that you have bound to the SQL variable :pointer that receives the result of the stored procedure call.

Borodin
  • 126,100
  • 9
  • 70
  • 144
  • Yes I am looking at the pointer only. The thing is I was checking if execute affects any row. And it returns undef after the first pass. The query is right and I am passing the second argument but I am just curious why second pass returns the undef. – mysteriousboy Mar 19 '13 at 12:48
  • Well your code doesn't show anything that displays `$pointer`, and for some reason you are calling `execute` twice. Is `$sql` displaying correctly? – Borodin Mar 19 '13 at 14:19
  • I solved the issue, the problem was I was closing the db connection for second loop. I fixed it thanks for the help – mysteriousboy Mar 19 '13 at 14:48
  • You're welcome. But the code you show doesn't do that? – Borodin Mar 19 '13 at 17:12