inner while loop fails to run after first pass 1

[jcarrott]

My code is:

# Declare the subroutines
sub trim($);
sub ltrim($);
sub rtrim($);

## read table procure.recpo and process each entry

use DBI;
## use MIME::Lite;

$dbh = DBI->connect($dsn, $user, $password)
		or die "Couldn't connect to DB: " . DBI->errstr;
$sth = $dbh->prepare("SELECT * FROM procure.RECPO")
		or die "Couldn't prepare statment: " . $dbh->errstr;

  ## Get R-NAME from BUYER

    my @data;
	my $rv = $sth->execute
		or die "Couldn't execute statement: " . $sth->errstr;

  ## Loop through each line of the PO

	while (@data = $sth->fetchrow_array()) {
		my $vCompany = rtrim($data[0]);
		my $vPO      = $data[1];
        $sPO      	 = ltrim($data[1]);
		my $vVendor  = $data[2];
		my $vBuyCode = rtrim($data[3]);
		print "\t$id: $vCompany $vPO $vVendor $vBuyCode\n";

		$st2 = $dbh->prepare("SELECT R_NAME FROM lawson.BUYER WHERE
			procure_group = 'OCF' and buyer_code = '" . $vBuyCode . "'")
			or die "Couldn't prepare statment 2: " . $db2->errstr;
		my @dat2;
		my $r2 = $st2->execute
     		or die "Couldn't execute statement 2: " . $st2->errstr;
		my $vBuyer = rtrim($st2->fetchrow_array());
		print "\t$id2: $vBuyer\n";
		$st2->finish;

		$st5 = $dbh->prepare("SELECT VENDOR_VNAME FROM lawson.APVENMAST WHERE
			vendor = '" . $vVendor . "'")
			or die "Couldn't prepare statment 5: " . $db5->errstr;
		my @dat5;
		my $r5 = $st5->execute
     		or die "Couldn't execute statement 5: " . $st5->errstr;
		my $vVenNm   = rtrim($st5->fetchrow_array());
		print "\t$id5: $vVenNm\n";
		$st5->finish;

	## POLINESRC will return one record for each line of the PO
	## now loop through each line of the PO and get each lawson.POLINESRC &
	## lawson.REQUESTER record

		$st3 = $dbh->prepare("SELECT SOURCE_DOC_N, REQUESTER FROM lawson.POLINESRC WHERE
			company = '" . $vCompany . "' and po_number = '" . $vPO . "'")
			or die "Couldn't prepare statment 3: " . $db2->errstr;
		my @dat3;
		my $r3 = $st3->execute
     		or die "Couldn't execute statement 3: " . $st2->errstr;
		while (@dat3 = $st3->fetchrow_array()) {
			my $vSource  = trim($dat3[0]);
			my $vReqsr   = rtrim($dat3[1]);
   			print "\t$id3: $vReqsr $vSource\n";

			$st4 = $dbh->prepare("SELECT R_NAME, EMAIL_ADDRESS FROM lawson.
				REQUESTER WHERE requester = '" . $vReqsr . "'")
				or die "Couldn't prepare statment 4: " . $db2->errstr;
			my @dat4;
    		my $r4 = $st4->execute
      			or die "Couldn't execute statement 4: " . $st2->errstr;
			@dat4 = $st4->fetchrow_array();
			my $vReqName = rtrim($dat4[0]);
			my $vSendTo  = rtrim($dat4[1]);
			print "\t$id4: $vReqName $vSendTo\n";
			$st4->finish;
		## create the email message
			my 	$message_body  = $vReqName . " -";
   				$message_body .= "\n\n";
   				$message_body .= "Purchase Order number " . $sPO;
   				$message_body .= " has been placed with Vendor - " . $vVenNm;
   				$message_body .= ", as a result of your requisition number ";
   				$message_body .= $vSource . ".\n\n";
   				$message_body .= "Please notify your buyer, " . $vBuyer . ", ";
   				$message_body .= "if there are any issues with this order.\n";
   				$message_body .= "Thank you, \n";

			print $message_body;
			print "\n";
    	## send email

## 			$msg = MIME::Lite->new(
## 				From        => 'purchasing@ochsner.org,
## 				To          => $vSendTo,
##				Subject     => 'RE: PO issued',
##				'X-Priority => $vPriority,
##				Type        => 'text/html');

## 			$msg->attach(
##				Type		=> 'TEXT',
##				Data		=> $message_body);

## 			$msg->send('smtp', 'smtp.ochsner.org');

		}
		$st3->finish;
	}

$sth->finish;
$dbh->disconnect();

# Perl trim function to remove whitespace from the start and end of the string
sub trim($)
{
	my $string = shift;
	$string =~ s/^\s+//;
	$string =~ s/\s+$//;
	return $string;
}
# Left trim function to remove leading whitespace
sub ltrim($)
{
	my $string = shift;
	$string =~ s/^\s+//;
	return $string;
}
# Right trim function to remove trailing whitespace
sub rtrim($)
{
	my $string = shift;
	$string =~ s/\s+$//;
	return $string;
}

When I run the code against a table with 4 entries, I get:

$ perl reqNote_sh
        Source record: 120        1608496    241350 LT
        Buyer  record: LEROY THXXXXX
        Vendor record: OWENS & MINOR
        POline record: WIXXXX 915030
        Reqest record: WAYNE WIXXXX wxxxxx@ochxxxx.org
WAYNE WIXXXX -

Purchase Order number 1608496 has been placed with Vendor - OWENS & MINOR, as a
result of your requisition number 915030.

Please notify your buyer, LEROY THXXXX, if there are any issues with this order.

Thank you,

        Source record: 120        1608495    391514 DT
        Buyer  record: DIANA THEXXXX
        Vendor record: EV3 INC
        Source record: 120        5000177    538507 JS
        Buyer  record: JOYCE SXXXX
        Vendor record: COMPASS FURNITURE
        Source record: 120        5000179    538507 JS
        Buyer  record: JOYCE SXXXX
        Vendor record: COMPASS FURNITURE
$

For the second, third, and fourth passes of the outer while loop - no data is returned from the inner while loop.

Does anybody know why the inner loop is not executing?

 

[PinkeyNBrain]

This is strictly from a debugging point of view but prior to your $st3 = $dbh->prepare(...) code, assign your SELECT statement to a string and print it out, verify it is what you think it should be. Then if you have a regular console interface, run the command by hand and see if anything dumps to the screen (more specifically for the 2nd through 4th loops). If you are getting data, then go back and examine why your @dat3 isn't taking on any values after the first loop.

 

[jcarrott]

Thank you, the answer was hard to believe - no data.