How do I skip a fatal error 1

[jcarrott]

I am doing a INSERT using this code;

            ## save the variables to procure.RECPO2 using INSERT

            $insert = "INSERT INTO procure.RECPO2 (po_number, requestor,
                      req_num, send_to, vend_name, buyer_name) VALUES
                      ('" . $sPO . "', '" . $vReqName . "', '" .
                            $vSource . "', '" . $vSendTo . "', '" .
                            $vVenNm . "', '" . $vBuyer . "')";

## if requisition # is zero and requester blank - don't write record

            if (length($vSource) gt 1 && length($vReqName) gt 1)
                {
                $st6 = $dbh->prepare_cached($insert)
                or die "Couldn't prepare insert: " . $dbh->errstr;

                $st6->execute()
                or die "Couldn't execute insert: " . $st6->errstr;
                }

The error I get is

DBD::Oracle::st execute failed: ORA-00001: unique constraint (PROCURE.PCR_RECPO2
_PK) violated (DBD ERROR: OCIStmtExecute) [for Statement "INSERT INTO procure.RE
CPO2 (po_number, requestor,
                                      req_num, send_to, vend_name, buyer_name) V
ALUES
                                      ('       1625162', 'ADRIAN SAMPSON', '9371
46', 'asampson@ochsner.org', 'NEOTECH PRODUCTS INC', 'LAURA SPURGEON')"] at reqN
ote_sh line 148.
Couldn't execute insert: ORA-00001: unique constraint (PROCURE.PCR_RECPO2_PK) vi
olated (DBD ERROR: OCIStmtExecute) at reqNote_sh line 148.

That is good. I do not want to insert the record if it is already in the table, but I don't want the program to stop.

I added the 'or die' so I could see what was happening. Without it the program was dying with the message "prepare_cached failed: ORA-01756: quoted string not properly terminated". I knew that message was not an acturacte error message.

The bottom line, I do not want duplicates and I don't want the program to fail. I am writing all console output to a log for review using 'perl reqNote_sh >temp.txt'.

Can somebody help me?

 

[jcarrott]

I tried to do a select and see if one of the fields matched before trying to do the insert.

        $sRequest = "stuff";
        $st7 = $dbh->prepare("SELECT REQUESTOR FROM procure.RECPO2
            WHERE po_number = '" . $sPO . "' and req_num = '" . $vSource
            . "'") or die "Couldn't prepare statment 7: " . $dbh->errstr;
        my @dat7;
        my $r7 = $st7->execute
            or die "Couldn't execute statement 7: " . $st7->errstr;
            my $sRequest  = $dat7[0];

        if ($sRequest ne $vReqName)
            {
## if requisition # is zero and requester blank - don't write record
            if (length($vSource) gt 1 && length($vReqName) gt 1)
                {
                $st6 = $dbh->prepare_cached($insert)
                or die "Couldn't prepare insert: " . $dbh->errstr;

                $st6->execute()
                or die "Couldn't execute insert: " . $st6->errstr;
                }
            }

That did not work, I still get

DBD::Oracle::st execute failed: ORA-00001: unique constraint (PROCURE.PCR_RECPO2
_PK) violated (DBD ERROR: OCIStmtExecute) [for Statement "INSERT INTO procure.RE
CPO2 (po_number, requestor,
                                      req_num, send_to, vend_name, buyer_name) V
ALUES
                                      ('1626125', 'ELAINE ORDOYNE', '938474', 'e
ordoyne@ochsner.org', 'OWENS & MINOR', 'ERSKINE BARBARIN, JR')"] at reqNote_sh l
ine 157.
Couldn't execute insert: ORA-00001: unique constraint (PROCURE.PCR_RECPO2_PK) vi
olated (DBD ERROR: OCIStmtExecute) at reqNote_sh line 157.

I was missing something, in the above case I should have had a match and the select did not fail.

Is there an easier way to do this?

 

[TrojanWarBlade]

2 things:

1) The DBI has a "RaiseError" option you can alter to (hopefully) control whether errors are fatal or just cause error codes to be returned.

2) Even without altering the "RaiseError" flag, you should be able to wrap your code with an "eval" block to stop it killing your process.



Trojan.

 

[stevexff]

It's complaining about the primary key constraint, by the looks of it. Assuming this is the PO number, where are you getting it from? Are you trying to do an INSERT instead of an UPDATE?

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]

 

[jcarrott]

Trojan - thank you for the idea, I will check into how to use the "RaiseError".

Steve - yes I am doing a INSERT. I do not want to do anything with the duplicate records. If I find the record is in the table I want to skip the INSERT.

I was hoping that since I did get a match with the SELECT that I could somehow check the status and use that condition to skip the INSERT.

 

[jcarrott]

I figured it out

        $sRequest = "";
        $st7 = $dbh->prepare("SELECT REQUESTOR FROM procure.RECPO2
            WHERE po_number = '" . $sPO . "' and req_num = '" . $vSource
            . "'") or die "Couldn't prepare statment 7: " . $dbh->errstr;
        my @dat7;
        my $r7 = $st7->execute
            or die "Couldn't execute statement 7: " . $st7->errstr;
            my $sRequest  = rtrim($st7->fetchrow_array());

        $st7->finish;

        if (length($sRequest) lt 1)

Since I set the variable to "", the test finds something more than 1 character long if the select statement worked.