use Text::CSV_XS;
my $csv = Text::CSV_XS->new ({ binary => 1 });
while (my $r = $sth->fetch) {
    $csv->print (*STDOUT, $r);
    }
[download]

Thanks for you lines of code.

I have modified my lines of code as follows:



 
    while (my @row = $_read_sth->fetchall_arrayref)
   {
       @ret_values = Dumper @row, @{$_read_sth->{NAME}};
        my $csv = Text::CSV_XS->new ({ binary => 1 });
        while (my $r =  $_read_sth->fetch) 
        {
            $csv->print (*STDOUT, $r);
         }
}
[download]

But it is returning me the following:

 15140EQJP_55121,"DD UN",2018175,"DU PONT (E.I.) DE NEMOURS","BO data mismatch",275000.0,98027EQJP_5498115140EQJP_55121,"AFL UN",2026361,"AFLAC INC","BO data mismatch",100000.0,29075EQJP_5430115140EQJP_55121,"SLM UN",2101967,"SLM CORP","BO data mismatch",180000.0,98002EQJP_5416115140EQJP_55121,"CMI UN",2240202,"CUMMINS INC","BO data mismatch",100000.0,98059EQJP_5342115140EQJP_55121,"DOW UN",2278719,"DOW CHEMICAL","BO data mismatch",175000.0,98027EQJP_5498115140EQJP_55121,"GCI UN",2360304,"GANNETT CO","BO data mismatch",137300.0,32847EQJP_5560115140EQJP_55121,"GCI UN",2360304,"GANNETT CO","BO data mismatch",2900.0,32847EQJP_5560115140EQJP_55121,"GCI UN",2360304,"GANNETT CO","BO data mismatch",39800.0,32847EQJP_5560115140EQJP_55121,"MFC CT",2492519,"MANULIFE FINANCIAL CORP","BO data mismatch",150000.0,29075EQJP_5430115140EQJP_55121,"UPS UN",2517382,"UNITED PARCEL SERVICE-CL B","BO data mismatch",130000.0,98059EQJP_5342115140EQJP_55121,"G IM",4056719,"ASSICURAZIONI GENERALI","BO data mismatch",200000.0,29075EQJP_5430115140EQJP_55121,"G IM",4056719,"ASSICURAZIONI GENERALI","BO data mismatch",200000.0,29075EQJP_5430115140EQJP_55121,"DPW GY",4617859,"DEUTSCHE POST AG-REG","BO data mismatch",153.0,25168EQJP_5518115140EQJP_55121,"OUT1V FH",4665148,"OUTOKUMPU OYJ","BO data mismatch",215000.0,98027EQJP_5498115140EQJP_55121,"RI FP",4682329,"PERNOD-RICARD SA","BO data mismatch",89000.0,37828EQJP_5410115140EQJP_55121,"INB BB",4755317,"INBEV NV","BO data mismatch",109000.0,37828EQJP_5410115140EQJP_55121,"SU FP",4834108,"SCHNEIDER ELECTRIC SA","BO data mismatch",25000.0,98059EQJP_5342115140EQJP_55121,"SU FP",4834108,"SCHNEIDER ELECTRIC SA","BO data mismatch",35000.0,98059EQJP_5342115140EQJP_55121,"SU FP",4834108,"SCHNEIDER ELECTRIC SA","BO data mismatch",15000.0,98059EQJP_5342115140EQJP_55121,"STERV FH",5072673,"STORA ENSO OYJ-R SHS","BO data mismatch",50000.0,98027EQJP_5498115140EQJP_55121,"BAS GY",5086577,"BASF SE","BO data mismatch",31150.0,98027EQJP_5498115140EQJP_55121,"BAS GY",5086577,"BASF SE","BO data mismatch",57850.0,98027EQJP_5498115140EQJP_55121,"MUV2 GY",5294121,"MUENCHENER RUECKVER AG-REG","BO data mismatch",25000.0,29075EQJP_5430115140EQJP_55121,"NDA SS",5380031,"NORDEA BANK AB","BO data mismatch",550000.0,29076EQJP_5444115140EQJP_55121,"NDA SS",5380031,"NORDEA BANK AB","BO data mismatch",450000.0,29076EQJP_5444115140EQJP_55121,"BMW GY",5756029,"BAYERISCHE MOTOREN WERKE AG","BO data mismatch",50000.0,98013EQJP_5472115140EQJP_55121,"TLSN SS",5978384,"TELIASONERA AB","BO data mismatch",5730000.0,98054EQJP_5336115140EQJP_55121,"TLSN SS",5978384,"TELIASONERA AB","BO data mismatch",1270000.0,98054EQJP_5336115140EQJP_55121,"TLSN SS",5978384,"TELIASONERA AB","BO data mismatch",500000.0,98054EQJP_5336115140EQJP_55121,"3 HK",6436557,"HONG KONG & CHINA GAS","BO data mismatch",349000.0,98003EQJP_5418115140EQJP_55121,"3 HK",6436557,"HONG KONG & CHINA GAS","BO data mismatch",800.0,98003EQJP_5418115140EQJP_55121,"9503 JT",6483489,"KANSAI ELECTRIC POWER CO INC","BO data mismatch",400.0,34274EQJP_5456115140EQJP_55121,"9503 JT",6483489,"KANSAI ELECTRIC POWER CO INC","BO data mismatch",409600.0,34274EQJP_5456115140EQJP_55121,"9503 JT",6483489,"KANSAI ELECTRIC POWER CO INC","BO data mismatch",170000.0,38772EQJP_5436115140EQJP_55121,"MAP AU",6543628,"MACQUARIE AIRPORTS","BO data mismatch",22697.0,38772EQJP_5436115140EQJP_55121,"19 HK",6867748,"SWIRE PACIFIC LTD A","BO data mismatch",210000.0,98061EQJP_5344115140EQJP_55121,"9506 JT",6895266,"TOHOKU ELECTRIC POWER CO INC","BO data mismatch",137600.0,38772EQJP_5436115140EQJP_55121,"322 HK",6903556,"TINGYI (CAYMAN ISLN) HLDG CO","BO data mismatch",2390000.0,37828EQJP_5410115140EQJP_55121,"OPAP GA",7107250,"OPAP SA","BO data mismatch",43000.0,98055EQJP_5334115140EQJP_55121,"OPAP GA",7107250,"OPAP SA","BO data mismatch",56000.0,98055EQJP_5334115140EQJP_55121,"FRA GY",7107551,"FRAPORT AG","BO data mismatch",2606.0,34274EQJP_5456115140EQJP_55121,"PPC GA",7268298,"PUBLIC POWER CORP","BO data mismatch",8500.0,98055EQJP_5334115140EQJP_55121,"TRN IM",B01BN57,"TERNA SPA","BO data mismatch",1000000.0,38772EQJP_5436115140EQJP_55121,"ST SP",B02PY22,"SINGAPORE TELECOMMUNICATIONS","BO data mismatch",1750000.0,98003EQJP_5418115140EQJP_55121,"LXS GY",B05M8B7,LANXESS,"BO data mismatch",75000.0,98027EQJP_5498115140EQJP_55121,"GAZ FP",B0C2CQ3,"GAZ DE FRANCE","BO data mismatch",60024.0,38772EQJP_5436115140EQJP_55121,"ATCOA SS",B1QGR41,"ATLAS COPCO AB-A SHS","BO data mismatch",90000.0,98002EQJP_5416115140EQJP_55121,"2007 HK",B1VKYN6,"COUNTRY GARDEN HOLDINGS CO","BO data mismatch",1583000.0,98061EQJP_5344115140EQJP_55121,"2007 HK",B1VKYN6,"COUNTRY GARDEN HOLDINGS CO","BO data mismatch",817000.0,98061EQJP_5344115140EQJP_55121,"RHA FP",B1Y9JH9,"RHODIA SA - REGR","BO data mismatch",60000.0,98027EQJP_5498115140EQJP_55121,"RHA FP",B1Y9JH9,"RHODIA SA - REGR","BO data mismatch",50000.0,98027EQJP_5498115140EQJP_55121,"1 HK",6190273,"CHEUNG KONG HOLDINGS LTD","Encumbered by load/system",4178382.0,15140EQJP_55121,"1038 HK",6212553,"CHEUNG KONG INFRASTRUCTURE","Encumbered by load/system",181718.0,15140EQJP_55121,"ISP IM",4076836,"INTESA SANPAOLO","Encumbered by load/system",65855503.0,15140EQJP_55121,"F IM",5748521,"FIAT SPA","Encumbered by load/system",5943889.0,15140EQJP_55121,"BRK/A UN",2093666,"BERKSHIRE HATHAWAY INC-CL A","Encumbered by load/system",784.0,15140EQJP_55121,"CEG UN",2073408,"CONSTELLATION ENERGY GROUP","Encumbered by load/system",87381.0,15140EQJP_55121,"CVX UN",2838555,"CHEVRON CORP","Encumbered by load/system",6149218.0,15140EQJP_55121,"OXY UN",2655408,"OCCIDENTAL PETROLEUM CORP","Encumbered by load/system",2328311.0,15140EQJP_55121,"COP UN",2685717,CONOCOPHILLIPS,"Encumbered by load/system",4959839.0,15140EQJP_55121,"XOM UN",2326618,"EXXON MOBIL CORP","Encumbered by load/system",17039779.0,15140EQJP_55121,"HSE CT",2623836,"HUSKY ENERGY INC","Encumbered by load/system",361176.0,15140EQJP_55121,"2332 HK",B032D70,"HUTCHISON TELECOMMUNICATIONS","Encumbered by load/system",748989.0,15140EQJP_55121,"DSM NA",B0HZL93,"KONINKLIJKE DSM NV","Encumbered by load/system",839856.0,15140EQJP_55121,"ELUXB SS",B1KKBX6,"ELECTROLUX AB-SER B","Encumbered by load/system",323173.0,"Position integrity:""91 - LOAN-IP - Loan in progress inconsistencies:""No loan in progress inconsistencies found""Loan IP tax_lot without open order?""No missing open orders found.""Loan tax_lot without loan account (user_field_6) or ""LV Transaction ID"" (user_field_4):"28963EQJP_55441,"2379 TT",6051422,"REALTEK SEMICONDUCTOR CORP",-37000.015140EQJP_55121,"2379 TT",6051422,"REALTEK SEMICONDUCTOR CORP",-83379.015140EQJP_55121,"1301 TT",6348544,"FORMOSA PLASTICS CORP",-1246101.015140EQJP_55121,"1326 TT",6348715,"FORMOSA CHEMICALS & FIBRE",-1223781.028963EQJP_55441,"BHP AU",6144690,"BHP BILLITON LTD",-873876.028963EQJP_55441,"RIO AU",6220103,"RIO TINTO LTD",-102022.015140EQJP_55121,"4904 TT",6421854,"FAR EASTONE TELECOMM CO LTD",-3701092.028963EQJP_55441,"2382 TT",6141011,"QUANTA COMPUTER INC",-147000.028963EQJP_55441,"4904 TT",6421854,"FAR EASTONE TELECOMM CO LTD",-146917.015140EQJP_55121,"440 HK",6249799,"DAH SING FINANCIAL",-116120.015140EQJP_55121,"1303 TT",6621580,"NAN YA PLASTICS CORP",-2279721.028963EQJP_55441,"2332 TT",6254878,"D-LINK CORP",-299360.028963EQJP_55441,"2912 TT",6704986,"PRESIDENT CHAIN STORE CORP",-120000.015140EQJP_55121,"2332 TT",6254878,"D-LINK CORP",-2949619.015140EQJP_55121,"101 HK",6030506,"HANG LUNG PROPERTIES LTD",-696872.028963EQJP_55441,"3045 TT",6290496,"TAIWAN MOBILE CO LTD",-856064.015140EQJP_55121,"3045 TT",6290496,"TAIWAN MOBILE CO LTD",-5523980.015140EQJP_55121,"STRTR SP",6853521,"STRAITS TRADING CO LTD",-156200.015140EQJP_55121,"2912 TT",6704986,"PRESIDENT CHAIN STORE CORP",-47000.015140EQJP_55121,"MVC SQ",5442685,"METROVACESA SA",-83278.99998999999715140EQJP_55121,"2207 TT",6417165,"HOTAI MOTOR COMPANY LTD",-2070999.999989999915140EQJP_55121,"USIM5 BS",2386009,"USINAS SIDER MINAS GER-PF A",-337820.015140EQJP_55121,"GETI3 BS",2440693,"AES TIETE SA",-3973613.015140EQJP_55121,"GETI4 BS",2441038,"AES TIETE SA-PREF",-6870657.015140EQJP_55121,"MFC CT",2492519,"MANULIFE FINANCIAL CORP",-1671137.015140EQJP_55121,"LR FP",B11ZRK9,"LEGRAND SA",-220656.015140EQJP_55121,"REE SQ",5723777,"RED ELECTRICA DE ESPANA",-6529.015140EQJP_55121,"BMW GY",5756029,"BAYERISCHE MOTOREN WERKE AG",-178390.015140EQJP_55121,"ELPL6 BS",B17R2H1,"ELETROPAULO METROPOLI-PREF B",-75586611.015140EQJP_55121,"WHR UN",2960384,"WHIRLPOOL CORP",-63030.015140EQJP_55121,"EQ UN",B142VX8,"EMBARQ CORP",-630.015140EQJP_55121,"JPM UN",2190385,"JPMORGAN CHASE & CO",-1190650.015140EQJP_55121,"WFC UN",2649100,"WELLS FARGO & COMPANY",-379895.0"Encmbered quantity differs from quantity on taxlot:""No faulty encumbrances found."

As you can see above the Column headings are missing. How do I get them?

The above output data has 7 resultsets each with different headings(column names). Each of the resultset has different column heading, so I can not hardcode the header rows.

The 7 resultsets are returned from a stored procedure.

As you can see above the Column headings are also missing. How do I get them?

Also there is no return at the end of each row of data. How to get that also?

Thanks for taking the trouble to help me.

still the same errors. Fetch returns a scalar reference, not a list, and don't fetch within the fetch on the same handle

my $csv = Text::CSV_XS->new ({ binary => 1, eol => "\r\n" });
# First print the Header row
$csv->print (*STDOUT, $_read_sth->{NAME});
# Then show all records
#         v---!
while (my $row = $_read_sth->fetchall_arrayref) {
    $csv->print (*STDOUT, $row);
    }
[download]


Enjoy, Have FUN! H.Merijn

my @row = $_read_sth->fetchall_arrayref

BTW if you need to process the data row by row, why don't you just read the data row by row from the db? That's far more efficient (especially in terms of memory)

while (my @row = $sth->fetchrow_array){
   print join(", ", @row), "\n";
}
[download]

And it is fetchall_arrayref. Why is it in while loop? Doesn't it return all rows at once?

in your code, you put it into @row, which is wrong, a ref is a scalar:

my @column_names = @{$_read_sth->{NAME_lc});
local $\ = "\n";
#         v---- not a @
while (my $row = $_read_sth->fetchall_arrayref) {
    print join ",", @$row; # Extremely oversimplified
    }
[download]

returning fetch (or fetchrow_arrayref) into @row will cause a structure like this:

$VAR1 = [
          undef
        ];
[download]

which is a true value, causing the endless loop

I'm going to take a guess that what you want is to get a hash returned. So why not do

my %fields = $_read_sth->{NAME};
print "$fields{Account},$fields{Symbol},....\n";
[download]

Perhaps if you could expand on what your difficulty is, I could do a better job of trying to help.

> Any formating suggestions how this can be done?

Using Data::Dumper and then trying to reformat its output shows a bad case of cargo-cult coding. Don't do that.

My suggestion is that you learn how to use references. Right now, you are using Data::Dumper to do the extraction for you - this is the part of it that you want - but you don't want the output formatted the way that Data::Dumper does it. The answer is to step away from the whole mess and do it right: extract the references, dereference them to the actual underlying data structure (an array, in the case of 'fetchall_arrayref'), and print out the elements of that array in the way you want them.

You also need to read the DBI documentation and figure out which fetch method you want to use in order to get the information you want. If you want both the field names and the field values - as you imply - then perhaps 'fetchrow_hashref' would serve you better.

You should also learn how to use the 'printf' command to format your output. Very basic example of code - something you can expand and rework to suit your purposes, not blindly copy - follows:

#!/usr/bin/perl -w
# use strict;
use DBI;
$|++;

# Create the variables that will be loaded from '.dbinfo'
our ($db, $table, $user, $pass, $query);
# Load them up
do ".dbinfo" or die "Database info not found!\n";

my $dbh = DBI->connect("DBI:mysql:$db", $user, $pass);
my $sth = $dbh->prepare("select $query from $table");
$sth->execute;

while (my $hash_ref = $sth->fetchrow_hashref){
    for my $key (keys %$hash_ref){
        printf "%-10s: %s\n", $key, $hash_ref->{$key};  
    }
}
[download]

-- 
Human history becomes more and more a race between education and catastrophe. -- HG Wells