dbi delete does not seem to work 1

[jcarrott]

I have a select and then a delete function. The select works great, but the delete does not work. It also does not give an error. I checked and the user ID that I log into the DB with does have ownership and delete permission.

## read table procure.po_lease and process each entry
use DBI;
use Locale::Currency::Format;
use MIME::Lite;

### Attributes to pass to DBI->connect(  )
%attr = (
    PrintError => 1,
    RaiseError => 1,
	AutoCommit => 1
	);

$dbh = DBI->connect($dsn, $user, $password, \%attr )
		or die "Couldn't connect to DB: " . DBI->errstr;
$sth = $dbh->prepare("SELECT COMPANY, PO_NUMBER, TOT_ORDER_AMT, NBR_LINES,
		CLOSED_LINES, BUYER_CODE, CLOSED_FL, VENDOR FROM procure.PO_LEASE") 
		or die "Couldn't prepare statment: " .
		"Couldn't prepare select: " . $dbh->errstr;
    my @data;
	my $rv = $sth->execute
		or die "Couldn't execute statement: " . $sth->errstr;

	open (PO_Close, ">>$vPath1$vName3");
	open (PO_Release, ">>$vPath1$vName4");

	while (@data = $sth->fetchrow_array())
	{

	$vComp     = $data[0];
	$vCompany  = ltrim($data[0]);

	if (substr($vCompany, 0, 1) eq "1") 
		{
		$vCompstr = "120";
	    }
	if (substr($vCompany, 0, 1) eq "7") 
		{
		$vCompstr = "710";
		}
	if (substr($vCompany, 0, 1) eq "8") 
		{
		$vCompstr = "800";
		}

	$vPo       = $data[1];
	$vPoNum    = ltrim($data[1]);
	$vNumLines = $data[3];
	$vClLines  = $data[4];
	$vBuyer    = $data[5];
	$vVendor   = ltrim($data[7]);

	$sLine  = $vCompstr . ", " . $vPoNum . ", LEA, " . $vBuyer . ", ";
	$sLine .= $vVendor . "\n";

##	print $sLine . "\n";

	if ($data[6] eq "Y") 
		{
		print PO_Close $sLine;
		}
	else
		{
		print PO_Release $sLine;
		}
	$sth2 = $dbh->prepare("DELETE FROM procure.PO_LEASE WHERE company = '" . $vComp . "'
			AND po_number = '" . $vPO . "'")
			or die "Couldn't prepare statment2: " .
			"Couldn't prepare delete: " . $dbh->errstr;
	my $rv2 = $sth2->execute
		or die "Couldn't execute delete: " . $sth2->errstr;
    }

Does anybody see why the delete is not working?

 

[1DMF]

What is the value of $vComp & $vPO , have you run debug statements to confirm the values held are correct and exist in the DB?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[jcarrott]

This is fixed, I had the Purchase order set to $vPO and re-typed it as $vPo.

 

[1DMF]

to avoid these simple typos and bugs, you should put..

use strict;

At the top of every script you write just after the shebang.

Then you will see errors relating to the use undeclared variables ;-)


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts