in reply to Writing Multiple Recordsets into a CSV file

Taking the final part of your code I think you need to fix a few things before getting on to some debugging. There is a bit of confusion between a string holding the filename and an actual filehandle. I would open the output file outside the loop and close it outside the loop. Perhaps a die within the loop so we can stop and see if what we get from the db is what we expect. 
open my $fh, q{>}, $datafile 
  or die qq{cant open $datafile to write: $!\n};

 my $header_rec = join(",", @{$read_sth->{NAME}}); 
 
# print the header to the filehandle
print $fh $header_rec . "\n"; 

while (@data = $read_sth->fetchrow_array) { 

    # lets see what we have
    die @data;

    my @data = EQ_Misc::arr_replace_undef("", @data); 
    my $csv_record = join(",", @data); 
        
    # you're writing the array _and_ the record (and a dot)?
    #print $fh "@data $csv_record .\n";
    
    # did you mean?
    print $fh qq{$csv_record\n};

}
#close $datafile;
close $fh;

[download]

Replies are listed 'Best First'.
Re^2: Writing Multiple Recordsets into a CSV file
by sudip_dg77 (Novice) on Apr 10, 2008 at 10:02 UTC
    I am using the following code as per your suggesstion now: 
    #!/cluster/uapp/perl/bin/perl -w
use KAP;
use strict;
use Date::Manip;
use DBAccess;
use EQ_Misc;
use DBD::CSV;
use MIME::Lite;
use Net::SMTP;

  my $_db_lv_conn_file = "MSLV.eq_script_user";
  my $_read_dbi = undef;

            # Run stored proc
    my $sp = "lv..check_tax_lot";

    KAP::write_log("Preparing $sp");
    $_read_dbi = KAP::opendbi($_db_lv_conn_file) || handle_error_and_e
+xit ("Failed to get a connection to MSLV db server");
    my $_read_sth = $_read_dbi->prepare($sp);
    if($DBI::err) {
        handle_error_and_exit("**ERROR preparing $sp: " . $DBI::errstr
+);
    }
    KAP::write_log("Prepare done, executing $sp");

    $_read_sth->execute();
    if($DBI::err) {
        handle_error_and_exit( "**ERROR executing $sp: " . $DBI::errst
+r);
    }
    KAP::write_log("Execute of $sp done");
    
    
    my $_datafile = KAP::datafile();   
    my $numrecords = 0;
    my @data = undef;
    
    open my $fh, q{>}, $_datafile or die qq{cant open $_datafile to wr
+ite: $!\n};

    my $header_rec = join(",", @{$_read_sth->{NAME}}); 
 
    # print the header to the filehandle
    print $fh $header_rec . "\n"; 
    while (@data = $_read_sth->fetchrow_array) { 

    # lets see what we have
    die @data;

    my @data = EQ_Misc::arr_replace_undef("", @data); 
    my $csv_record = join(",", @data); 
        
    # you're writing the array _and_ the record (and a dot)?
    #print $fh "@data $csv_record .\n";
    
    # did you mean?
    print $fh qq{$csv_record\n};

    }
    #close $datafile;
    close $fh;

    [download]
    The output is as follows:
    nbsda1@vcstest3 $ perl test.pl
    Name Age
    John 29
    Ram 30
    at /cluster/uapp/app/bin/sudip.pl line 45.

    #It still returns me only only the 1st Results set and discrads the last 6.

    nbsda1@vcstest3 $
    Please Help...
[reply]
[d/l]
      Are you certain that $_read_sth->fetchrow_array returns more than one record? A print STDOUT "@csv_record\n"; will show that.

      If it doesn't, then there's probably a problem with your stored procedure.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom