last outter loop does not finish 2

[jcarrott]

The code I have used the DBI module to read my tables.

use DBI;

$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;

    	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 = $data[0];
		$vCompany    =~ s/\s*$//;
		my $vPO      = $data[1];
		$vPO         =~ s/\s*$//;
		$sPO         = $vPO;
		$sPO         =~ s/^\s+//;
		my $vVendor  = $data[2];
		my $vBuyCode = $data[3];
		$vBuyCode    =~ s/\s*$//;
		print "\t$id: $vCompany $vPO $vVendor $vBuyCode\n";

		$st2 = $dbh->prepare("SELECT * 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;
		@dat2 = $st2->fetchrow_array();
		my $vBuyer   = $dat2[4];
		$vBuyer      =~ s/\s*$//;
		print "\t$id2: $vBuyer\n";
		$st2->finish;

		$st5 = $dbh->prepare("SELECT * 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;
		@dat5 = $st5->fetchrow_array();
		my $vVenNm   = $dat5[3];
		$vVenNm      =~ s/\s*$//;
		print "\t$id5: $vVenNm\n";
		$st5->finish;

		$st3 = $dbh->prepare("SELECT * 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 $vReqsr   = $dat3[11];
			$vReqsr      =~ s/\s*$//;
			my $vSource  = $dat3[8];
			$vSource     =~ s/\s*$//;
			$vSource     =~ s/^\s+//;
			print "\t$id3: $vReqsr $vSource\n";

			$st4 = $dbh->prepare("SELECT * 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 = $dat4[2];
			$vReqName =~ s/\s*$//;
			my $vSendTo  = $dat4[39];
			$vSendTo =~ s/\s*$//;
			print "\t$id4: $vReqName $vSendTo\n";

			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 . ", if";
   				$message_body .= " there are any issues with this order.\n";
   				$message_body .= "Thank you, \n";

			print $message_body;
			print "\n";

		}
		$st3->finish;
		$st4->finish;
	}
$sth->finish;
$dbh->disconnect();

The result I get does not process the final inner 'while' loop.

        Source record: 120        1608496    241350 LT
        Buyer  record: LEROY THOMAS
        Vendor record: OWENS & MINOR
        POline record: WILLIS 915030
        Reqest record: WAYNE WILLIS wawillis@ochsner.org
WAYNE WILLIS -

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

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

Thank you,

        Source record: 120        1608495    391514 DT
        Buyer  record: DIANA THERIOT
        Vendor record: EV3 INC

I am sure that I am doing something wrong that is very basic. Can somebody help me?

 

[Annihilannic]

Have you checked whether @dat3 = $st3->fetchrow_array() is actually returning anything for the last record being processed?

Incidentally, it would tidy up and shorten your code significantly if you wrote a generic trim() function rather than having so many s/\s*$// and s/^\s*// throughout.

Annihilannic.

 

[ChrisHunt]

Can you confirm which bit isn't working? The sample output you give includes text generated in the final loop.

I'd suggest printing out the exact SQL statement used in the loop in question, and checking that it actually returns the data you think it will.

Also, your method of firing many single-table queries at the database seems like the long way round of doing it. Why not build a single query with the tables joined like a relational database should be?

Also it's a bad idea to SELECT * from each table and pick the data out purely by their column position. Partly because you're digging out and passing more data than you need, but mainly because column position can change - which would break your script. Better to select just those columns you need, which'll mean you can be sure that $data[x] is column such-and-such, because that's where it is in your SQL.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[jcarrott]

There is data being returned on the second outter loop 'Source record' is the data that is used to drill for the rest of the data.

The data from POLINESRC is the start of getting the rest of the data. Buyer and Vendor are at the PO level, but the problem comes from the requisition. There can be a seperate resuest for each line of the PO. So a combined SQL would not work.

I will work on the targeted SELECT and the TRIM() function.

I was focused on the high level functionality first.

I just can't see why the last loop does not run. If it ran and failed I would get errors.