Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Perl MySql

by rzayas (Initiate)
on Dec 20, 2004 at 08:06 UTC ( #416127=perlquestion: print w/replies, xml ) Need Help??

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;
#html code replies that everything works (not shown)

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

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$
    You can eliminate the inner loop entirely with,
    while (@report = $sth->fetchrow_array()) { print SAVE join("\t", @report), $/; }
    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

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;
    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!

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;


    Thanks
    Sasi Kumar
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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://416127]
Approved by davido
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (2)
As of 2021-07-24 10:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?