Hi All
I am working on version 17.2.
I am connecting with a third party data source. I have created a CDT which has the same structure as the table in the third party data source. I am able to select as well as update data in the third party data source. However, when I try to insert data in the table, the primary key value is not generated, i.e. it goes as null thus my process breaks at Write to data store entity; which is expected behavior.
At the third part data source, the primary key is a number which is created as "GENERATED AS IDENTITY NOT NULL". In my CDT I have given the annotation @SequenceGenerator and @GeneratedValue and the CDT is getting verified with the data source.
For reference, the ID column in my XSD is given as:
<xsd:element name="ID" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Id @GeneratedValue @SequenceGenerator @Column(name="ID", columnDefinition="NUMBER")</xsd:appinfo> </xsd:annotation> </xsd:element>
Now I want to know how to handle the primary key column i.e. what do I insert as a value in ID column.
Thanks in advance!!
Discussion posts and replies are publicly visible
Hi @komalc one small clarification, which database is getting used by Third Party System like Oracle, MySQL, PostgreSQL etc...
The reason behind asking is, @SequenceGenerator represents the generation strategy as Sequence which is one of the type of generator, and there many databases which do not support Sequence Strategy for PK Generation, such as MYSQL
These are few popular databases which supports Sequence Generation Strategy, such as:
Oracle, SQL Server 2012, PostgreSQL, DB2, HSQLDB etc..
So, if your DB supports Sequence Generation Strategy then try defining this as mention below:
@Id
@Column(name="ID", columnDefinition="NUMBER")
@GeneratedValue(generator="my_seq")
@SequenceGenerator(name="my_seq",sequenceName="MY_SEQ")
Hope this will help you.
I'm guessing you're on Oracle 12c? Have you tried it with the @GeneratedValue annotation but without the @SequenceGenerator annotation? I think IDENTITY means you don't get a sequence name - it's Oracle's equivalent of AUTO_INCREMENT. If it doesn't work without @SequenceGenerator, IDENTITY might not be supported yet - worth raising a support case to check.
As per my understanding, if that's a Third Party System, then we don't have any control over that system, i mean to say The Sequence must be created from 3rd party Side, the consumer or the one who is going to consume the service/Client, is no where responsible for configuring sequence over a table. So while creating the CDT, we just need to mark one Property (like ID) as PK but not Auto generated. Also, when you want to persist the data into 3rd Party System, the 3rd Party must define some approach, like an API or function etc. through which the Client can pass the remaining column/property values where the id will be generated by help of Sequence or using Sequence with Trigger