Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-563

Oracle "IDENTITY" last inserted ID is returning 0 instead of the real ID

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.5
    • Component/s: None
    • Security Level: All
    • Environment:
      Oracle, OCI8

      Description

      I am using doctrine 2 with oracle, the tables in the database has some triggers that generate the IDs, and I am trying to us Doctrine 2 cascade persist when mapping on one-to-many, and I use "IDENTITY" in the mapping, but there is a problem which is the one-side of the relation is returning 0 as last inserted ID, which is wrong, my ID mapping of my tables is like the following:

      /**

      • @orm\Id
      • @orm\Column(type="integer");
      • @orm\GeneratedValue(strategy="IDENTITY")
        */
        protected $id;

      and my entities looks like the following:

      /**

      • @ORM\Entity
      • @ORM\Table(name="clients")
        **/
        class Client {
        /**
      • @ORM\Id
      • @ORM\GeneratedValue(strategy="IDENTITY")
      • @ORM\Column(type="integer")
        */
        protected $id;

      /** @ORM\Column(name="name",type="string",length=255,unique=true) */
      protected $name;

      /**

      • @ORM\OneToMany(targetEntity="ContactInformation", mappedBy="client", cascade= {"persist"}

        )
        **/
        protected $contactInformations;

      public function __construct()

      { $this->contactInformations = new ArrayCollection(); }

      public function getId()

      { return $this->id; }

      public function getName() { return $this->name; }

      public function setName($name) { $this->name = $name; return $this; }

      public function getContactInformations() { return $this->contactInformations; }

      public function addContactInformations(Collection $contactInformations)
      {
      foreach ($contactInformations as $contactInformation) { $contactInformation->setClient($this); $this->contactInformations->add($contactInformation); }
      }

      /**
      * @param Collection $tags
      */
      public function removeContactInformations(Collection $contactInformations)
      {
      foreach ($contactInformations as $contactInformation) { $contactInformation->setClient(null); $this->contactInformations->removeElement($contactInformation); }
      }

      public function setContactInformations($contactInformations) { $this->contactInformations = $contactInformations; return $this; }
      }

      and the other entity:

      /**
      * @ORM\Entity
      * @ORM\Table(name="contact_informations")
      **/
      class ContactInformation {
      /**
      * @ORM\Id
      * @ORM\GeneratedValue(strategy="IDENTITY")
      * @ORM\Column(type="integer")
      */
      protected $id;

      /**
      * @ORM\OneToOne(targetEntity="ContactInformationType")
      * @ORM\JoinColumn(name="type_id", referencedColumnName="id")
      **/
      protected $type;

      /** @ORM\Column(type="text") */
      protected $value;

      /**
      * @ORM\ManyToOne(targetEntity="Client", inversedBy="contact_informations")
      * @ORM\JoinColumn(name="client_id", referencedColumnName="id")
      **/
      private $client;

      public function getId() { return $this->id; }

      public function getType()

      { return $this->type; }

      public function setType($type)

      { $this->type = $type; return $this; }

      public function getValue()

      { return $this->value; }

      public function setValue($value)

      { $this->value = $value; return $this; }

      public function getClient()

      { return $this->client; }

      public function setClient($client = null)

      { $this->client = $client; return $this; }

      }

      I also want to add: why don't Doctrine 2 just use the oracle "returning id into" statement, in this case regardless the identity mapping this will always return the inserted ID, and it will work with "AUTO", "SEQUENCE", "IDENTITY" and I think any other mapping word used!

      I did try to trace where the problem come from, and it seems that when using OCI8 oracle driver that the invoked method is
      Doctrine\ORM\Id\IdentityGenerator::generate
      and it invokes
      Doctrine\DBAL\Connection::lastInsertId
      and is returning 0, I don't know why it is being invoked since the sequenceName is null (there is no sequence in the definition!)

      Maybe a good solution is to check if the $statement is an 'INSERT INTO ' sql statement, then we bind an output variable to the statement which will hold the "returning ID into :output_variable" value... what do you think?

      1. LastInsertId.php
        1 kB
        Mohammad A. ZeinEddin
      2. OCI8Connection.php
        5 kB
        Mohammad A. ZeinEddin
      3. OCI8Statement.php
        10 kB
        Mohammad A. ZeinEddin

        Activity

        Hide
        Steve Müller added a comment -

        IDENTITY generation strategy is SOMEHOW implemented in Oracle with the workaround of creating a (before insert) trigger on the specific table that uses a sequence to emulate an autoincrementation. I guess this is just a compatibility approach for IDENTITY strategy on a best effort basis and should not be relied on. This is also the reason why it is stated in the documentation as not fully portable. The issue discussed here is also not an issue of Doctrine ORM IMO as it is not responsible for evaluating if an IDENTITY strategy needs a sequence for the underlying driver to obtain the last insert ID. However there already seems to be hack for exactly the same case in PostgreSQL. See:

        https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Mapping/ClassMetadataFactory.php#L453

        What we probaby COULD do is add another check in the ClassMetadataFactory for the Oracle platform to tell it to use a sequence for IDENTITY strategy. But that still is rather hackish to be honest...

        Show
        Steve Müller added a comment - IDENTITY generation strategy is SOMEHOW implemented in Oracle with the workaround of creating a (before insert) trigger on the specific table that uses a sequence to emulate an autoincrementation. I guess this is just a compatibility approach for IDENTITY strategy on a best effort basis and should not be relied on. This is also the reason why it is stated in the documentation as not fully portable. The issue discussed here is also not an issue of Doctrine ORM IMO as it is not responsible for evaluating if an IDENTITY strategy needs a sequence for the underlying driver to obtain the last insert ID. However there already seems to be hack for exactly the same case in PostgreSQL. See: https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Mapping/ClassMetadataFactory.php#L453 What we probaby COULD do is add another check in the ClassMetadataFactory for the Oracle platform to tell it to use a sequence for IDENTITY strategy. But that still is rather hackish to be honest...
        Hide
        Benjamin Eberlei added a comment -

        Steve Müller The real issue is indeed, that IDENTITY is not really supported for Oracle. We would need to find a way to support it generically or throw an exception in the ORM if Oracle is used with IDENTITY.

        Show
        Benjamin Eberlei added a comment - Steve Müller The real issue is indeed, that IDENTITY is not really supported for Oracle. We would need to find a way to support it generically or throw an exception in the ORM if Oracle is used with IDENTITY.
        Hide
        Steve Müller added a comment -

        Step one in fixing this issue has been applied in PR https://github.com/doctrine/dbal/pull/428 and fixed in commit https://github.com/doctrine/dbal/commit/d2845256d22a0ea2c5e5392aa67f4b95f252d5c4.
        Step two has been supplied in ORM in PR https://github.com/doctrine/doctrine2/pull/890.

        As soon as the PR on ORM side gets merged it is possible to use IDENTITY generator strategy with Oracle

        Show
        Steve Müller added a comment - Step one in fixing this issue has been applied in PR https://github.com/doctrine/dbal/pull/428 and fixed in commit https://github.com/doctrine/dbal/commit/d2845256d22a0ea2c5e5392aa67f4b95f252d5c4 . Step two has been supplied in ORM in PR https://github.com/doctrine/doctrine2/pull/890 . As soon as the PR on ORM side gets merged it is possible to use IDENTITY generator strategy with Oracle
        Hide
        Doctrine Bot added a comment -

        A related Github Pull-Request [GH-890] was closed:
        https://github.com/doctrine/doctrine2/pull/890

        Show
        Doctrine Bot added a comment - A related Github Pull-Request [GH-890] was closed: https://github.com/doctrine/doctrine2/pull/890
        Show
        Steve Müller added a comment - Fixed in commits: https://github.com/doctrine/dbal/commit/d2845256d22a0ea2c5e5392aa67f4b95f252d5c4 https://github.com/doctrine/doctrine2/commit/a7b9140d2fddcab995b2597be4d589155ff1aa8f

          People

          • Assignee:
            Steve Müller
            Reporter:
            Mohammad A. ZeinEddin
          • Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: