in reply to Re^4: Writing Multiple Recordsets into a CSV file
in thread Writing Multiple Recordsets into a CSV file

[ This post assumes you are using DBD::Sybase. ]

You use 

do {
   my $row = $_read_sth->fetchrow_arrayref();
   print Dumper $row;
} while ($_read_sth->{syb_more_results});

[download]

Normal use would be 

while (my $row = $_read_sth->fetchrow_arrayref()) {
   print Dumper $row;
}

[download]

The docs suggest 

do {
   while (my $row = $sth->$_read_sth->fetchrow_arrayref()) {
      print Dumper $row;
   }
} while ($_read_sth->{syb_more_results});

[download]

Not sure what this "syb_more_results" business is about, so I don't know if "Normal" is sufficient or if you need the outer loop, but what you are using now definitely looks wrong.

Replies are listed 'Best First'.
Re^6: Writing Multiple Recordsets into a CSV file
by andreas1234567 (Vicar) on Apr 11, 2008 at 08:37 UTC
[reply]
Re^6: Writing Multiple Recordsets into a CSV file
by sudip_dg77 (Novice) on Apr 11, 2008 at 09:22 UTC
    Following is my code now as per your suggestion: 
    #!/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;
use Data::Dumper;

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

            # Run stored proc
    my $sp = "longview..norges_integrity_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;
    
   do{
    while (my $row = $_read_sth->fetchrow_arrayref())
   {
     print Dumper $row;
   }
}  while ($_read_sth->{syb_more_results});

    [download]
    But the output doesn't contain any column headers and only returns the first resultset as follows: 
    nbsda1@vcstest3$ perl sudip.pl

$VAR1 = [
          '15140EQJP_55121',
          'DD UN',
          '2018175',
          'DU PONT (E.I.) DE NEMOURS',
          'BO data mismatch',
          '275000.0',
          '98027EQJP_54981'
        ];

$VAR1 = [
          '15140EQJP_55121',
          'CMI UN',
          '2240202',
          'CUMMINS INC',
          'BO data mismatch',
          '100000.0',
          '98059EQJP_53421'
        ];
$VAR1 = [
          '15140EQJP_55121',
          'DOW UN',
          '2278719',
          'DOW CHEMICAL',
          'BO data mismatch',
          '175000.0',
          '98027EQJP_54981'
        ];

nbsda1@vcstest3$

    [download]
    But basically what I want is that whatever the stored procedure returns(all the 7 resultsets), just to dump that on the screen with all the header columns etc.

    I also do not need this $VAR1 = [ ];

    Basically I want to write the output as dumped by stored procedure into a .csv file which I would need for some other purpose. But before I try to write the output into the .csv file I just wanted to print the output on the screen and see how it looks.

    Any help?
[reply]
[d/l]
[select]
      Corion already suggested you replace the home-grown KAP module with the more robust DBI CPAN module. You haven't done that.
[reply]
      Try fetchrow_hashref() instead of fetchrow_arrayref(). You are using Data::Dumper. It dumps. It does not format. You have to do the formatting yourself. Read up of Perl hashes for a start.
      --
      Andreas
[reply]
[d/l]
[select]

      Great! It works!

      I also do not need this $VAR1 = [ ];

      Data::Dumper is a debugging tool, meant to show data in an umambiguous format. It's now up to you to replace it with the output code you desire.

[reply]

In Section Seekers of Perl Wisdom