http://qs321.pair.com?node_id=434205

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

Ok I am pretty new to perl and have been working on my first website that has a database using class::dbi. My question is what is the most efficient way to step through a database printing everything on each row. I can write to the database easily enough. My primary key in the database is an int that auto increments. If any of you could point me to some explaination or code that demonstrates how to cleanly walk through a db it would be much appreciated. Oh and I should say I have read the stuff on reading from a database on the tutorials page. All of my database access is through modules and where I am inputing and reading from the database is in a test script.

Replies are listed 'Best First'.
Re: question about printing from database
by friedo (Prior) on Feb 24, 2005 at 19:29 UTC
    With Class::DBI, you can fetch an entire table using the retrieve_all method. In scalar context, it will give you an iterator which basically does what the DBI "fetchrow_*" methods do. For example: 

    my $iterator = Class->retrieve_all;
while(my $item = $iterator->next) { 
    print $item->some_field, "\n";
}

    [download]

    Update: Added newline to print.

[reply]
[d/l]
      Thanks a ton man that works so now all I need to do is in the while exectution set it up to print all of the fields in the columns in the table. once again Thanks
[reply]
Re: question about printing from database
by hok_si_la (Curate) on Feb 24, 2005 at 20:55 UTC
    Greetings Thargor,

    I am also a newbie, and have needed to to do something very similar many times. Here is the format I usually use. 
    ############################################################
sub getInfo
############################################################
{
my ($dbh, $sth, %data);

$dbh=DBI->connect('DBI:ODBC:my_table', { RaiseError => 1, AutoCommit =
+> 0 });
       
$sth = $dbh->prepare( "Select * FROM my_table" );
$sth->execute;
$sth->bind_columns( \( @data{ @{$sth->{NAME_lc} } } ));  #----Saves sp
+eed by binding columns to their values. 
$sth->{'ChopBlanks'} =1;    #----Removes extra spaces from fixed char 
+fields. 
$data{$_} = (defined $data{$_}) ? $data{$_} : '' for keys %data;  #---
+-Changes null values to "" as per jZed to avoid warnings

while ($sth->fetch) {
      
print qq(<TR>\n);
print qq(<TH>$data{col1name}</TH>\n); #use real column names
print qq(<TH>$data{col2name}</TH>\n); 
print qq(<TH>$data{col3name}</TH>\n);
print qq(<TH>$data{col4name}</TH>\n);  
print qq(</TR>\n);
}


$sth->finish();
$dbh->disconnect();  ----Needed to free up system resources.
}

    [download]
    Hope this also helps.

    hok_si_la
[reply]
[d/l]

Back to Seekers of Perl Wisdom