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

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
  • Comment on Re^3: Writing Multiple Recordsets into a CSV file

Replies are listed 'Best First'.
Re^4: Writing Multiple Recordsets into a CSV file
by sudip_dg77 (Novice) on Apr 11, 2008 at 08:10 UTC
    Here is my perl script to print results that are returned from a stored procedure. The Database is MS-SQL. 
    #!/cluster/uapp/perl/bin/perl -w
use KAP; #internally developed library.
use strict;
use Date::Manip; #internally developed library.
use DBAccess;
use EQ_Misc; #internally developed library.
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 = "lv..check_tax_lot"; #The stored procedure that I am runn
+ing, which returns me 7 resultset.

    KAP::write_log("Preparing $sp");#To write log
    
    $_read_dbi = KAP::opendbi($_db_lv_conn_file) || handle_error_and_e
+xit ("Failed to get a connection to MSLV db server"); # To connect to
+ the database
    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");#To write log

    $_read_sth->execute();
    if($DBI::err) {
        handle_error_and_exit( "**ERROR executing $sp: " . $DBI::errst
+r);
    }
    KAP::write_log("Execute of $sp done");#To write the log

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

    [download]
    But when I run the above perl script I only get the following output: 
    nbsda1@vcstest3$ perl sudip.pl
$VAR1 = [
          '15140EQJP_55121',
          'DD UN',
          '2018175',
          'DU PONT (E.I.) DE NEMOURS',
          'BO data mismatch',
          '275000.0',
          '98027EQJP_54981'
        ];
nbsda1@vcstest3$

    [download]
    Which is only the first line of data as returned from the stored procedure. However if I run the stored procedure alone from a tool connecting to database(like for example DB Artisan or Rapid SQL) I get the following output: 
    ########## RESULTSET 1 ##############
                                                        
Account    Symbol    Sedol    Security Name    Encumbered    Enc. qty 
+   Borrow Acct    
                                
15140EQJP_55121    DD UN    2018175    DU PONT (E.I.) DE NEMOURS    BO
+ data mismatch    275000    98027EQJP_54981                          
+          
15140EQJP_55121    AFL UN    2026361    AFLAC INC    BO data mismatch 
+   100000    29075EQJP_54301                                    
15140EQJP_55121    SLM UN    2101967    SLM CORP    BO data mismatch  
+  180000    98002EQJP_54161                                    
                        
########## RESULTSET 2 ##############                                 
+                           
Position integrity:                                                   
+         
                                                            
lender_account    borrow_account    sedol    symbol    security_name  
+  encumbered    loaned    diff                                
15140EQJP_55121    98003EQJP_54181    6497071    000700 KP    HANJIN S
+HIPPING    25000    0    25000                                
15140EQJP_55121    98003EQJP_54181    6494997    005880 KP    KOREA LI
+NE CORP    8160    0    8160    
                                                            
########## RESULTSET 3 ##############                                 
+                           
91 - LOAN-IP - Loan in progress inconsistencies:                      
+                                      
                                                            
tax_lot_id    lender_account    borrow_account    sedol    symbol    s
+ecurity_name    order_id    block_id    encumbered    quantity_ordere
+d    quantity_confirmed    quantity_closed    quantity_executed    un
+sent_executions    quantity_left    diff
No loan in progress inconsistencies found                             
+                               

########## RESULTSET 4 ##############                                 
+                            
Loan IP tax_lot without open order?                                   
+                         
                                                            
account    symbol    sedol    security_name    tax_lot_id    security_
+id    position_type_code    quantity    encumbered_quantity          
+                  
No missing open orders found.                                         
+                   

########## RESULTSET 5 ##############                                 
+                            
Loan tax_lot without loan account (user_field_6) or "LV Transaction ID
+" (user_field_4):                                                    
+        
                                                            
account    symbol    sedol    security_name    Enc. qty    Loan Accoun
+t    LV Transaction ID                                    
15140EQJP_55121    FIC UN    2330299    FAIR ISAAC CORP    1105       
+ [NULL]                                    

########## RESULTSET 6 ##############                                 
+                            
Negative tax_lots on index account (Over-encumbered??):               
+                                             
                                                            
account    symbol    sedol    security_name    quantity               
+                             
28963EQJP_55441    2379 TT    6051422    REALTEK SEMICONDUCTOR CORP   
+ -37000                                            
15140EQJP_55121    2379 TT    6051422    REALTEK SEMICONDUCTOR CORP   
+ -83379                                            
                            
########## RESULTSET 7 ##############                                 
+                            
Encmbered quantity differs from quantity on taxlot:                   
+                                         
                                                            
account    symbol    sedol    security_name    quantity    encumbered_
+quantity    encumbered_type    description                           
+     
No faulty encumbrances found.

    [download]
    How do I modify my perl script so that all of the output as returned by the stored procedure as shown above can be printed? I would appreciate your help........
[reply]
[d/l]
[select]

      Most likely, your use of {syb_more_results} is wrong. Most likely, this is because you're not using DBD::Sybase but something else. Reduce your code. Write a real small and short script that only connects to the database and fetches the results, instead of pulling in all your other in-house libraries. Throw out the following modules from your sample program:

      use KAP; #internally developed library.
use Date::Manip; #internally developed library.
use DBAccess;
use EQ_Misc; #internally developed library.
use DBD::CSV;
use MIME::Lite;
use Net::SMTP;

      [download]

      Date::Manip likely is not an "internally developed library" anyway. After you've thrown out these, your script should basically look like:

      use strict;
use DBI;
use Data::Dumper;
my $dbh = DBI->connect(...);
my $sp = 'lv..check_tax_lot';
warn "Preparing [$sp]";
my $sth = $dbh->prepare($sp);
warn "Running [$sp]";
my $res = $sth->execute();
warn "Got results ($res):";
if (! $res) {
    die $sth->errstr;
};
my $results = $sth->fetchall_arrayref;
print Dumper $results;

      [download]

      Also, your post should have been a reply below Re: Writing Multiple Recordsets into a CSV file, where you already got a good answer and I consider it good courtesy to at least mention that others have helped you already and where they have.

[reply]
[d/l]
[select]

      [ 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.

[reply]
[d/l]
[select]
[reply]
        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]
      Are you sure that $_read_sth->{syb_more_results} is defined (or even exists)? Try 
      while( my $row = $_read_sth->fetchrow_arrayref() ) {
  print Dumper $row;
}

      [download]
      --
      Andreas
[reply]
[d/l]
[select]

      You still haven't told us about KAP? Does it return a DBD object? If so, which one - DBD::Sybase, DBD::Oracle or something else? Or is it a wrapper around a DBD object? If so, does KAP expose the necessary methods/members to traverse multiple result sets. Until you can tell us more about KAP, every answer here is just MJD 11900 or (my favorite) 11901.

      -derby
[reply]
        Searching our home grown Module Documentation this is what I get:
        DBI FUNCTIONS

        dbidata

        Returns connection data for initializing a DBI connection.

        opendbi

        Returns a DBI object. Se the DBI documentation for what to to with it.

        Usage: my $dbi = KAP::opendbi( 'SYBASE.eq_script_user' ); $dbi->do(``SELECT * FROM people WHERE nice = 'very''');

        For this example to work, a configuration file called dbi.SYBASE would have to be created, and it needs to contain values for 'data_source', 'username' and 'password'

        data_source=SYBASE
        username=eq_script_user
        password=secret

        or full data_source:

        data_source=dbi:ODBC:ms_servosl12
        username=eq_script_user
        password=secret

        or

        data_source=dbi:Sybase:server=SYBASE
        username=eq_script_user
        password=secret
[reply]

In Section Seekers of Perl Wisdom