Returning a dataset with arrays

Hi!
I am a newbie using Perl and I have a big problem , for me, and it is
that I need to return a query result into a array but I cant do that.

I did this code:

sub getcodigodb()
{ local($table)= shift;
local($field)= shift;
my $code=shift;
my $dbh = open_connection();

my $sql = "SELECT * FROM $table WHERE $field=$code ";
my $sth = $dbh->prepare($sql);
$sth->execute or die "Unable to execute SQL query: $dbh->errstr
\n";

my $sth = $dbh->prepare($sql);
$sth->execute or die "Unable to execute SQL query: $dbh->errstr
\n";

$ref = $sth->fetchall_arrayref;

$sth->finish;
$dbh->disconnect;

return $ref;
}
}

$sth->fetchall_arrayref; I use this because I read that I need a perl
structure for moving into it.
This select can return 0,1 or n rows so I suppose $ref has this rows
but I dont know how to access them.

Could be this way :
@rows=&getcodigodb('mytable','myfield',3434); Is it correct?

If it is how can I access the rows. If the query doesnt find records,
what the function return to determine if the dataset exist or not?

I do this but this just print the selection I want return the
selection and manipulate whenever I want because it is in array and I
can use everything functions from array

while (@row = $sth->fetchrow_array)
{ # retrieve one row
print join(", ", @row), "\n";
}

I just want to access the selection like a cursor.
Sorry for inconveniece but I am so confused with this.

Thks in Advance!

Re: Returning a dataset with arrays

Macaruchi wrote:

> I am a newbie using Perl and I have a big problem , for me, and it is
> that I need to return a query result into a array but I cant do that.
>
> I did this code:
>
> sub getcodigodb()
> { local($table)= shift;
> local($field)= shift;


You should always prefer lexical (my) variables over package (local)
variables, except when you can't.

You can here, so those should be my() rather than local() declarations.


> my $code=shift;


Get all 3 arguments in one go:

my($table, $field, $code) = @_;


> my $dbh = open_connection();
>
> my $sql = "SELECT * FROM $table WHERE $field=$code ";
> my $sth = $dbh->prepare($sql);
> $sth->execute or die "Unable to execute SQL query: $dbh->errstr
> \n";
>
> my $sth = $dbh->prepare($sql);
> $sth->execute or die "Unable to execute SQL query: $dbh->errstr
> \n";
>
> $ref = $sth->fetchall_arrayref;
>
> $sth->finish;
> $dbh->disconnect;
>
> return $ref;
> }
> }
>
> $sth->fetchall_arrayref; I use this because I read that I need a perl
> structure for moving into it.
> This select can return 0,1 or n rows so I suppose $ref has this rows
> but I dont know how to access them.


Read:

perldoc perlreftut


> Could be this way :
> @rows=&getcodigodb('mytable','myfield',3434); Is it correct?


No, that won't work.

Apply "Use Rule 1" from the above documentation.

I like to do it in 3 steps:

1) pretend it is an ordinary array:

@rows = @query_rows;

2) replace the name with a block:

@rows = @{ };

3) put something in the block that returns the right kind of reference:

@rows = @{ getcodigodb('mytable', 'myfield', 3434) };


(do not use an ampersand when calling functions unless you know what
it does, and what it does is what you want to do (it seldom is).
)


> If it is how can I access the rows. If the query doesnt find records,
> what the function return to determine if the dataset exist or not?


warn "no results\n" unless @rows;


--
Tad McClellan
email: perl -le "print scalar reverse qq/moc.noitatibaher\100cmdat/"

Re: Returning a dataset with arrays

On 1 Apr, 03:55, Macaruchi wrote:

> I am a newbie using Perl and I have a big problem , for me, and it is
> that I need to return a query result into a array but I cant do that.

Did you start your program with "use strict;use warnings;"?

Good version of your code:

sub getcodigodb
{ my ($table,$field,$code)= @_;
my $dbh = open_connection();

my $sql = "SELECT * FROM $table WHERE $field=$code";
my $sth = $dbh->prepare($sql);
$sth->execute() or die "Unable to execute SQL query: $dbh-
>errstr
\n";
$ref = $sth->fetchall_arrayref;

$sth->finish;
$dbh->disconnect;

return $ref;
}

}

> Could be this way :
> @rows=&getcodigodb('mytable','myfield',3434); Is it correct?
No
my $ref=getcodigodb('mytable','myfield',3434);

or my @recs=@{getcodigodb('mytable','myfield',3434)};

Don't use '&' to call subs.


There is a good book about Perl: "Learning Perl". 4th edition is best.
Good Perl tutorials are available here:
http://www.perlfoundation.org/perl5/index.cgi?recommended_on line_tutorials

---
Alexandr Ciornii, http://chorny.net