Appian Community and Appian Academy are being upgraded. As a part of the upgrade, Appian Community is currently in read-only mode, and user registration is disabled until August 3. We apologize for any inconvenience this may cause, but a more secure, stable, and performant Community experience is coming soon!
The new Appian Community launches August 3, followed by Appian Academy on August 7. During the migration, Appian Community Edition, Appian Academy, Documentation, Certifications, Instructor-led Customer Training, Partner Sales Training & Accreditation, and Forum (for Appian Partners and Customers only) will remain available.
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