Perl MySql

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

I have a simple routine where I am retrieving records from a MySQL database. The query should return anywhere from 25-100 records. I am then writing the results to a text file. If I limit the search results to 2 or less, it works fine. Anything more than that times out. The problem is in the writing of the file; if I eliminate the file output section, the script runs without incidence.

Here is the code:

#select the fields from the database
$dbh = gsm::connect ();
$sth=$dbh->prepare ("select * from meetings order by meetings.id");
$sth->execute ();

#process the columns received, one at a time
while ((@report)=$sth ->fetchrow_array()) {
  foreach(@report) {
    print SAVE "$_\t";
    shift (@report);
  }
  print SAVE "\n";
}
#cleanup
$sth->finish; 
$dbh->disconnect();
close SAVE;
[download]

#html code replies that everything works (not shown)

2004-12-20 Janitored by Arunbear - added code tags, as per Monastery guidelines

Comment on Perl MySql Download Code

Replies are listed 'Best First'.
Re: Perl MySql by Zaxo (Archbishop) on Dec 20, 2004 at 08:26 UTC
You shouldn't shift elements out of the array in your foreach loop. That will cause you to miss fields. $ perl -e'@f=qw/foo bar baz/;for (@f) {print;shift @f}' foobaz$ [download] You can eliminate the inner loop entirely with, `while (@report = $sth->fetchrow_array()) { print SAVE join("\t", @report), $/; }` [download] I don't see anything in your code that would limit the number of records you can fetch or print. I don't see where *SAVE is opened. Are you checking that open succeeds? After Compline, Zaxo	[reply] [d/l] [select]
Re: Perl MySql by nite_man (Deacon) on Dec 20, 2004 at 08:20 UTC
Updated: Try to fetch all results and then store them into file: `#select the fields from the database $dbh = gsm::connect (); $sth=$dbh->prepare ("select * from meetings order by meetings.id"); $sth->execute (); my $results = $sth->fetchall_arrayref(); #cleanup $sth->finish; $dbh->disconnect(); open SAVE, '/some/file' or die "Cannot open file:$!"; #process the columns received, one at a time for my $row (@$results) { print SAVE join("\t", @$row)."\n"; } close SAVE;` [download] Also, you can use module "Time::HiRes" to see when do you have a bottle-neck. --- Michael Stepanov aka nite_man It's only my opinion and it doesn't have pretensions of absoluteness!	[reply] [d/l]
Re: Perl MySql by sasikumar (Monk) on Dec 20, 2004 at 08:34 UTC
Hi , Use the below mentioned (untested) code. This would solve the problem. `#select the fields from the database $dbh = gsm::connect (); $sth=$dbh->prepare ("select * from meetings order by meetings.id"); $sth->execute (); #process the columns received, one at a time while ((@report)=$sth ->fetchrow_array()) { $temp=join('\t',@report); print SAVE "$temp"; print SAVE "\n"; } #cleanup $dbh->disconnect(); close SAVE;` [download] Thanks Sasi Kumar	[reply] [d/l]
Re: Perl MySql by steves (Curate) on Dec 20, 2004 at 14:20 UTC
When you say "times out", what do you mean? The comment at the end indicates HTML output. Is this part of a CGI that's building a web page and the request is timing out in the browser? If so, eliminating the output may only be making things work coincidentally by making things work just faster enough to avoid a timeout that exists due to the time the query is taking.	[reply]