looping through sql server cursors

[j0nxuser]

I have several scripts that execute sql server select statements and some that execute sql server stored procedures. They work fine to retrieve and print the entire record sets (as desired). When I take the below sql statement through tsql, I see a record set of 42 records, but when I execute this inside the below perl script, I only get one row. On top of that, the one row that printed is the very last row...Why doesn't the entire record set reside in the array when printed? I also tried to assign the record set to scalars and still only one row (the last row) printed. Is there something else I need to do to handle cursors?

Thank you,
Mark

#!/usr/bin/perl

#Shared files
unshift (@INC, "/home/shared");
require "accounts.pl";


#Connect to server
&DB_CONNECT ("COASTAL");


#Specify database
$dbh->do("use CTGASR20");

##Initialize variable
#$dlr_ddd="";
#$ddd="";
#$pon="";
#$created_by="";
#$icsc="";
#$asr_sent_dt="";
#$version="";
#$dlr_created_dt="";
#$pbe="";

#declare stored procedure
my $query = " declare \@DLR_DD VARCHAR(8)
            declare \@DLR_PON VARCHAR(16)
            declare \@DLR_CREATED_ON DATETIME
             
            declare dlr_cursor cursor for
              select  
                    rdt.dd,
                    rdt.pon,
                    max(rdt.created_on) as created_on
              from
                    res_dlr_tbl rdt
              where
                     rdt.created_on >= '2005-02-17 15:00'
                 and rdt.created_on < '2005-02-17 17:00'
                 and rdt.pon <> ''
              group by
             rdt.dd,
                    rdt.pon
            --        and rdt.created_on >= '2004-04-21 18:00' 
             
            open dlr_cursor
 
            fetch next from dlr_cursor into \@DLR_DD, \@DLR_PON, \@DLR_CREATED_ON
                select top 1 \@DLR_DD as DLR_DDD,
                     DDD,
                     \@dlr_pon as PON,
                     created_by,
                     icsc,
                     asr_sent_on,
                     ver,
                     \@dlr_created_on as DLR_CREATED_ON,
                     pbe
                into #recd_dlrs_temp
                from asr_Admin_Tbl aat
               where pon=\@dlr_pon
            order by case when supp='1' then 1 else 0 end, 
                     (select count(*) from sys_asr_transfer_tbl where icsc=aat.icsc and active='A' and ccna='LTL') DESC,
                     case when (asc_ec>' ' and asc_ec<>icsc) then 1 else 0 end,
                     asr_sent_on desc
             
            while \@\@fetch_status=0
            begin
               insert into #recd_dlrs_temp (DLR_DDD, DDD, PON, CREATED_BY, ICSC, ASR_sENT_ON, VER, DLR_CREATED_ON, PBE)
               select top 1 \@DLR_DD as DLR_DDD,
                    DDD,
                    \@dlr_pon as PON,
                    created_by,
                    icsc,
                    asr_sent_on,
                    ver,
                    \@dlr_created_on as DLR_CREATED_ON,
                    pbe
               from asr_Admin_Tbl aat
              where pon=\@dlr_pon
            order by case when supp='1' then 1 else 0 end, 
                     (select count(*) from sys_asr_transfer_tbl where icsc=aat.icsc and active='A' and ccna='LTL') DESC,
                     case when (asc_ec>' ' and asc_ec<>icsc) then 1 else 0 end,
                     asr_sent_on desc
              fetch next from dlr_cursor into \@DLR_DD, \@DLR_PON, \@DLR_CREATED_ON
            end
             
            close dlr_cursor
            deallocate dlr_cursor
             
            select * from #recd_dlrs_temp";

#Prepare stored procedure
my $sth=$dbh->prepare($query);

print "$query\n";
#Execute stored procedure
$sth->execute();

    #assign info into array
    (@info_array) = $sth->fetchrow;
    
	    $dlr_ddd=$info_array[0];
        $ddd=$info_array[1];
        $pon=$info_array[2];
        $created_by=$info_array[3];
        $icsc=$info_array[4];
        $asr_sent_dt=$info_array[5];
        $version=$info_array[6];
        $dlr_created_dt=$info_array[7];
        $pbe=$info_array[8];

        print "$dlr_ddd,$ddd,$pon,$created_by,$icsc,$asr_sent_dt,$version,$dlr_created_dt,$pbe\n";

#Finish query and disconnect from db
$sth->finish;
$dbh->disconnect;

 

[PaulTEG]

  (@info_array) = $sth->fetchrow;

You're only pulling the one row from the database, as for it being the last record, it could be the order by statement

while (@info_array) = $sth->fetchrow) {
...
}

HTH
--paul

cigless ...

 

[j0nxuser]

Paul - I should have known. Your suggestion worked like a charm!!

Thank you,

Mark