Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Posts
    362
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prevent duplicate entry

    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:

    Code:
    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?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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: @
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •