perl failed because of DBI delete with 3 where values 1

[jcarrott]

I am trying to do a delete, I have done the select and I am finished working with this record. I need to get rid of it.

This is my code. I have tried ';', 'and', ','

## delete the line from procure.RECPO2

        $dbh->do("DELETE FROM procure.RECPO2 WHERE
              po_number = '" . $vPO . "';
              requester = '" . $vReqName . "';
              req_num   = '" . $vSourDoc . "'")
              or die "Couldn't delete line: " . $dbh->errstr;

The error is trying to read the second where value.
Is there a way to get the record I want and delete it?

 

[feherke]

Hi

I have tried ';', 'and', ','

Wrong, correct, wrong.

Now show us how you added the [tt]and[/tt] operator to the [tt]where[/tt] clause.

Feherke.

http://free.rootshell.be/~feherke/

 

[jcarrott]

I just had an and at the end.

## delete the line from procure.RECPO2

        $dbh->do("DELETE FROM procure.RECPO2 WHERE
              po_number = '" . $vPO . "' and
              requester = '" . $vReqName . "' and
              req_num   = '" . $vSourDoc . "'")
              or die "Couldn't delete line: " . $dbh->errstr;

        }

I thought because I had concatienated the single quote I could just say 'and' then have the next value.

 

[jcarrott]

I forgot to include the error for the 'and'

DBD::Oracle::db do failed: ORA-00904: "REQUESTER": invalid identifier (DBD ERROR
: error possibly near <*> indicator at char 77 in 'DELETE FROM procure.RECPO2 WH
ERE
                          po_number = '       1608501' and
                          <*>requester = 'FERRARO' and
                          req_num   = '915053'') [for Statement "DELETE FROM pro
cure.RECPO2 WHERE
                          po_number = '       1608501' and
                          requester = 'FERRARO' and
                          req_num   = '915053'"] at reqNote2_sh line 116.
Couldn't delete line: ORA-00904: "REQUESTER": invalid identifier (DBD ERROR: err
or possibly near <*> indicator at char 77 in 'DELETE FROM procure.RECPO2 WHERE
                          po_number = '       1608501' and
                          <*>requester = 'FERRARO' and
                          req_num   = '915053'') at reqNote2_sh line 116.

 

[jcarrott]

I droped the requester and it worked,

$dbh->do("DELETE FROM procure.RECPO2 WHERE
po_number = '" . $vPO . "' and
req_num = '" . $vSourDoc . "'")
or die "Couldn't delete line: " . $dbh->errstr;

 

[stevexff]

Rather than trying to build up the statement by catenating the pieces together, you may find it easier (and more secure) to use a prepared statement and then execute it

my $sql_delete = 'DELETE FROM procure.RECPO2 WHERE po_number = ?
   AND requester = ? AND req_num = ?;';
my $sth = $dbh->prepare($sql_delete);
$sth->execute($vPO, $vReqName, $vSourDoc);

Note: not tested...

And although it is just a matter of personal taste and/or site coding standards, the Hungarian notation on the variable names is a bit clunky and smacks of VB...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]