Small Doubt Regarding Select Staement in DB

koti688 has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I have two files like below. 1st file : mail.pl

#!/usr/bin/perl
use DBD::mysql;
use DBI;
use Utils::DBClass;
$object = new DBClass( "perltest", "localhost", 3306,"root", "sierra")
+;
$object -> select_db ("select * from samples");
[download]

2nd file DBClass.pm in a folder named Utils.

package DBClass;
use DBI;
@ISA = ('Exporter');
@EXPORT_OK = ("Connection_check", "new","select_db");
sub Connection_check {
my($self ) = @_;
$dsn = "DBI:mysql:database=$self->{_db};host=$self->{_host};port=$self
+->{_port}";
$dbh = DBI->connect($dsn,$self->{_user},$self->{_pass});
return ($dbh)
}
sub new
{
    my $class = shift;
    my $self = {
        _db => shift,
        _host => shift,
        _port => shift,
        _user => shift,
        _pass => shift
    };
    $dbh = Connection_check($self);
    $self->{_dbh} = $dbh;
    bless $self, $class;
    return $self;
}
### Retrieving the Rows ########
sub select_db{
  my ($obj,$sel) = @_;
  my $sth1 = $dbh->prepare($sel);
  $sth1->execute or die "SQL Error: $DBI::errstr\n";
  my @row;
  while (@row = $sth1->fetchrow_array)
    {
      print "@row \n";
    }
}
[download]

I am Getting the output for the quiries i mentioned above as below: <html> 1 Koti 555-5555 2 Siva 222-2222 3 Praneet 555-5555 4 nishitha 08649-257828 6 Anu 222-2222 7 Pradeep 555-5555 8 Asmit 222-2222 9 Surya 555-5555 </html> My requirement is getting the out put with out using the "print" statement in the select_db function. i want to get the ouput using "return" function and using "array of arrays". can anyone suggest me how to proceed .

Comment on Small Doubt Regarding Select Staement in DB Select or Download Code

Replies are listed 'Best First'.
Re: Small Doubt Regarding Select Staement in DB by rovf (Priest) on Nov 03, 2008 at 09:43 UTC
`my @row; while (@row = $sth1->fetchrow_array) { print "@row \n"; }` [download] i want to get the ouput using "return" function and using "array of arrays". Hmmmm.... you are nearly done. Inside the loop, you already have the data as an array. Though you can't exactly have "array of array", you can at least have either "array of arrayreference" or "reference to array of arrayreference", depending on your likings (or maybe depending on the calling context). If you define your result array initially as `my @resultarray;` [download] you can successively push references to the rows into it, like this: `push @resultarray,\@row;` [download] Then you can do one of these: `return @resultarray; # returns list when called in list context return \@resultarray; # returns reference return wantarray ? @resultarray : \@resultarray; # adapt to calling co +ntext` [download] Although I personally use the latter type of return occasionally in my own code, I know that this usage is somewhat questionable, and at least needs to be documented well. -- Ronald Fischer <ynnor@mm.st>	[reply] [d/l] [select]
Re^2: Small Doubt Regarding Select Staement in DB by koti688 (Sexton) on Nov 03, 2008 at 12:03 UTC
Thanks Ronald.. I got it.. and i really appreciate ur explanation 2. Thanks a lot ~ koti.	[reply]
Re: Small Doubt Regarding Select Staement in DB by ikegami (Patriarch) on Nov 03, 2008 at 09:50 UTC
`@EXPORT_OK = ("Connection_check", "new","select_db");` [download] All three of those are methods. None of them should be exported.	[reply] [d/l]
Re^2: Small Doubt Regarding Select Staement in DB by koti688 (Sexton) on Nov 03, 2008 at 12:02 UTC
Thanks ikegami.. Itz veryuseful.	[reply]
Re: Small Doubt Regarding Select Staement in DB by ikegami (Patriarch) on Nov 03, 2008 at 09:35 UTC
`sub select_db{ my ($self,$sel) = @_; return $self->{_dbh}->selectall_arrayref($sel); }` [download]	[reply] [d/l]
Re^2: Small Doubt Regarding Select Staement in DB by ikegami (Patriarch) on Nov 03, 2008 at 09:45 UTC
I should have mentioned it's pretty dangerous to do things this way since you can't avail yourself of replaceable parameters. Here are two alternate solutions: Let the user call `selectall_arrayref` (or whatever): `sub dbh{ my ($self) = @_; return $self->{_dbh}; }` [download] Accept the same parameters as `selectall_arrayref`: `sub select_db{ my $self = shift; return $self->{_dbh}->selectall_arrayref(@_); }` [download]	[reply] [d/l] [select]