Hi Appian,
I have a use case to create a CDT for an oracle table. The id is configured as below, I am trying to create CDT with not specifying the sequence using JPA Annotation @GeneratedValue ( please find example 1 XML) when I do this the datastore doesn't publish. When I tried to publish my CDT with Example 2 CDT then write to db calls fail with error : ids for this class must be manually assigned before calling save().Curious to know if we can create a CDT with no sequence and make the inserts work from Appian ?
Oracle Code for ID:
"ID" NUMBER(38,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
Example 1 XML:
<xsd:element name="id" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Id @GeneratedValue @Column(name="ID", nullable=true, columnDefinition="NUMBER")</xsd:appinfo> </xsd:annotation> </xsd:element>
Example 2 XML:
<xsd:appinfo source="appian.jpa">@Id @Column(name="ID", nullable=false, columnDefinition="NUMBER")</xsd:appinfo>
Discussion posts and replies are publicly visible
Can you try to create your CDT in Appian first and see what table gets generated when you publish the data store? That's a good way to identify what the structure of the table should be for it to map correctly. I agree with others that the 38 length seems too large - generally Appian integers are much smaller (since Appian itself doesn't support integer values past 2^32).
Also, what version of Oracle are you using?
Hi Peter, Let me be descriptive with all the steps I performed:
Oracle Version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
As suggested I created CDT in appian and jpa annotation that appian created is
<xsd:element name="id" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Id @Column(name="ID", nullable=false, columnDefinition="NUMBER")</xsd:appinfo> </xsd:annotation> </xsd:element>
The sql asscociated with id is as below.
"ID" NUMBER(2,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
When I try to make an insert using write to datastore entity smart service, I get the below error:
Details: org.hibernate.id.IdentifierGenerationException: ids for this class must be manually assigned before calling save()
When I do the insert with below query in db it works,
Insert into REF.Table(ID,col1,col2,IS_ACTIVE,CREATED_DT,CREATED_BY,LAST_MODIFIED_DT,LAST_MODIFIED_BY)values(NULL,'test','teste2','Y',to_timestamp('03-MAY-22 11.22.00.860000000 PM','DD-MON-RR HH.MI.SSXFF AM'),'system',to_timestamp('12-MAY-22 01.58.16.200000000 PM','DD-MON-RR HH.MI.SSXFF AM'),'system');
I believe appian needs an @GeneratedValue to understand that the id will be autogenerated but when I used below JPA annotation and tried to update the CDT, it errors with error :
The data source schema does not match the type mappings: Missing sequence or table: Table_sq
<xsd:element name="id" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Id @GeneratedValue @Column(name="ID", nullable=false, columnDefinition="NUMBER")</xsd:appinfo> </xsd:annotation> </xsd:element>
Did you check the box for "Auto-generate the next unique identifier when new records are written to a data store entity" when creating your CDT? That should add that annotation automatically and you shouldn't need to update your CDT and include that annotation afterwards.
Hi ,
Please share if you are able to figure it out how to set the autoincrement while writing to DB without creating the sequence in Oracle DB.
Thanks
Manish