PERL Beginners - retrieving multiple data from the database

Author

retrieving multiple data from the database

Alma

2007-06-27, 9:59 pm

Hi All,

I have written a display subroutine in my package .

abc.pm

sub display
my $self = shift;
$id =@_;
my $sth=$dbh->prepare("select title,name,status from table where
id=emp_id and status ='P'");
$sth->execute() or die $databasehandle->errstr;
while( my @row=$sth->fetchrow_array()) {
foreach($i=0;$i<3;$i++) {
return @row;
}
}

My calling program is
my @row = abc->display();

print $row[0];
print $row[1];

------
I am getting only the one record. It should display me the no. of
matching records.In my database there are 3 records.

Is there any way that we can return the multiple row values from the
subroutine.

Thanks in advance.

Chas Owens

2007-06-27, 9:59 pm

On 6/27/07, Alma <almatirkey@gmail.com> wrote:
snip
> return @row;
snip
> Is there any way that we can return the multiple row values from the
> subroutine.
snip

Return does not work like you think it does. It return immediately.
If you wish to return a list then you need to return a list (not
individual values). Either build an array using push, or better yet
use the fetchall method. Also, learn to indent blocks and use
whitespace. Putting everything on left hand side and all scrunched up
is unreadable.

sub display {
my $self = shift;
#$id = @_; # I don't think this is what you mean try this instead
my ($id) = @_;
my $sth = $dbh->prepare("
select title, name, status
from table
where id = emp_id
and status ='P'
");
#$sth->execute() or die $databasehandle->errstr; #boy are you
going to be surprised on an error, try this instead
$sth->execute() or die $dbh->errstr; #or better yet, just add
RaiseError => 1 during the connect
return @{$sth->selectall_arrayref};
}

My calling program is
my @row = abc->display();

Paul Lalli

2007-06-27, 9:59 pm

On Jun 27, 8:14 am, almatir...@gmail.com (Alma) wrote:

> I have written a display subroutine in my package .

And you have re-written it here. Please don't do that. Copy and
paste your code. Do not retype it. For one, you're going to get
comments about the typos you've made rather than your actual problem.
For two, we have no way of knowing what other mistakes you've made
here that you didn't make in your original.

> abc.pm
>
> sub display

No opening curly-brace. This code does not compile.

> my $self = shift;
> $id =@_;

This does not do what you think it does. This assigns $id to be the
number of arguments passed into this method (not counting $self, which
you shifted off already). You meant one of these:
my $id = shift;
my ($id) = @_;
my $id = $_[0];

I also notice that you're not declaring $id here. That means that
every object of your abc.pm class has the same $id value, and when you
change it for one, you change it for every one.

> my $sth=$dbh->prepare("select title,name,status from table where
> id=emp_id and status ='P'");
> $sth->execute() or die $databasehandle->errstr;

The fact that you're using `or die...` on the execute() implies that
$dbh->{RaiseError} is not turned on, yet you're not checking the
return value of prepare(). If the prepare() fails, Perl will silently
continue and you'll get no useful error message from the execute() (it
will tell you you can't call execute() on an undefined value)

> while( my @row=$sth->fetchrow_array()) {
> foreach($i=0;$i<3;$i++) {
> return @row;

This is non sensical. You're returning out of the function
immediately after the first iteration of foreach within the first
iteration of while.

If you want to return all three rows, create an array before your
while loop, and each time through the while statement, push a
reference to @row onto your array, and return that array at the very
end.

Paul Lalli

Sponsored Links