Writing Multiple Recordsets into a CSV file

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

I have stored procedure returning me 7 resultsets, which I want to write to a .csv file using Perl. But for some reason(I do not know why) the .csv file only has the 1st set of resultset and all the other 6 sets are discarded. Is there a function in perl through which I can write all the 7 resultsets into the .csv file? Here is the part of the code that is doing it:

#!/cluster/uapp/perl/bin/perl -w
use KAP ## A internally developed module with custom code to access th
+e Database.
use strict;
use DBAccess;
use DBD::CSV;



  my $db_lv_conn_file = "MSLV.eq_script_user";
  my $read_dbi = undef;

      # Run stored proc
    my $sp = "lv..check_tax_lot";
##This is the Stored Procedure that gives me back 7 Resultsets ###
    
    $read_dbi = KAP::opendbi($_db_lv_conn_file) || handle_error_and_ex
+it ("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 = "output.csv";   
    my $numrecords = 0;
    my @data = undef;
  

    # Loop through the data and write to file
##This is where I start to write into the .csv file##    

##But it only writes the one resultset and discards the rest 6.##
    my $header_rec = join(",", @{$read_sth->{NAME}});
   
    print $datafile $header_rec . "\n";

    while (@data = $read_sth->fetchrow_array) 
    {
        @data = EQ_Misc::arr_replace_undef("", @data);
        my $csv_record = join(",", @data);
        open (FH, ">>$datafile") or die "$!";
        print FH "@data $csv_record .\n";;
        close $datafile;   
    }
[download]

Any help will be appreciated.....

Comment on Writing Multiple Recordsets into a CSV file Download Code

Replies are listed 'Best First'.
Re: Writing Multiple Recordsets into a CSV file by wfsp (Abbot) on Apr 10, 2008 at 09:41 UTC
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]	[reply] [d/l] [select]
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]
Re^3: Writing Multiple Recordsets into a CSV file by apl (Monsignor) on Apr 10, 2008 at 11:35 UTC
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]
Re: Writing Multiple Recordsets into a CSV file by derby (Abbot) on Apr 10, 2008 at 12:12 UTC
What database are you using? (my bet, Sybase or MS SQLServer). The idiom for Sybase is `do { while( my $row = $sth->fetch ) { ... } } while($sth->{syb_more_results});` [download] because (from DBD::Sybase): I've decided to handle this by returning an empty row at the end of each result set, and by setting a special Sybase attribute in $sth which you can check to see if there is more data to be fetched. The attribute is syb_more_results which you should check to see if you need to re-start the "fetch()" loop. So I guess it all depends on how/what your KAP module handles multiple result sets. As a side rant, I find multiple recordsets to be a premature optimization that should be avoided - but hey, that's just me. -derby	[reply] [d/l]
Re^2: Writing Multiple Recordsets into a CSV file by sudip_dg77 (Novice) on Apr 10, 2008 at 14:45 UTC
I am using MS-SQL...whats the alternative for syb_more_results in MS-SQL? And what libraries are needed to use this function?	[reply]
Re^3: Writing Multiple Recordsets into a CSV file by derby (Abbot) on Apr 10, 2008 at 14:47 UTC
That all depends on what module(s) your underlying KAP module use. Does it use DBD::Sybase or DBD::ODBC or something else entirely? -derby	[reply]
Re^4: Writing Multiple Recordsets into a CSV file by sudip_dg77 (Novice) on Apr 11, 2008 at 08:10 UTC
Re^5: Writing Multiple Recordsets into a CSV file by Corion (Patriarch) on Apr 11, 2008 at 08:20 UTC
Re^5: Writing Multiple Recordsets into a CSV file by ikegami (Patriarch) on Apr 11, 2008 at 08:27 UTC
Some notes below your chosen depth have not been shown here
Re^5: Writing Multiple Recordsets into a CSV file by andreas1234567 (Vicar) on Apr 11, 2008 at 08:22 UTC
Re^5: Writing Multiple Recordsets into a CSV file by derby (Abbot) on Apr 11, 2008 at 10:14 UTC
Some notes below your chosen depth have not been shown here