...

View Full Version : Prevent duplicate entry



florida
12-21-2007, 02:43 AM
I have a unique constraint in my Oracle table for 3 fields. If a duplicate entry is entered from something like Oracle client it would give me a ORA-00001 unique constraint message and not add the duplicate info.

How do I capture that if I am using PHP?

If I have the PHP insert statement like this:



php $s = OCIParse($c, "insert into tableOne values ('$varOne', '$varTwo', '$varThree')");
OCIExecute($s, OCI_DEFAULT);

How would I capture the ORA-00001 unique constraint message to prevent a duplicate entry into the Oracle table?

Fou-Lu
12-21-2007, 05:13 AM
Although I am familiar with oracle, I am not familiar with its php connectivity. I'm betting that it has a errno and error message associated with the implicit cursors the query generated that can be returned with a simple call.

Ah here we go: http://ca3.php.net/manual/en/function.oci-error.php
This is for a procedural oci_error, and returns an array (betting on the num and the msg). Still trying to track down an object oriented version, but its probably there somewhere. Hope that helps!
Oh, and you can stop any warnings associated with it by using a muter in front of your object call: @

arnyinc
12-21-2007, 04:22 PM
Just to clarify your final question "How would I capture the ORA-00001 unique constraint message to prevent a duplicate entry into the Oracle table?"

Oracle has already prevented the data from being entered into the table when it returns the ORA-00001 error to you. As far as trapping the error, Fou-Lu has the right idea with using oci_error.

If all else fails, you can always try to SELECT the data before entering it. Test to see if it already exists before you INSERT it. Obviously if you can trap the error, that will require one less call than using the SELECT so that would be more efficient.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum