Question

Create CDT with no sequence associated to it oracle.

Forum|Forum|4 years ago
May 10, 2022
26 replies
0 views

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>

V

venkyu4136

Hi [mention:528d57c3686b4da186f5d2922f7428a2:e9ed411860ed4f2ba0265705b8793d05],
What is the error you getting while publishing with Example 1?

I can see that the coulmn is not nullable but in XML 1 it is nullable = true. try by setting it as false.

A

abhishekg985Author

Hi Venky thanks for the response. when I tried the code in Example 1 XML, its erroring out asking for a valid sequence. if I don't put @GeneratedValue the CDT is getting saved and datastore is being published but write to datastore fails.

V

venkyu4136

Can you try with @SequenceGenerator with/without @GeneratedValue annotation.

A

abhishekg985Author

Tried and got the below issue Venky

A type mapping annotation is invalid: No eclass found with name Id@SequenceGenerator (AnnotationParserException)

davel001150

My immediate first question is why do you want to avoid @GeneratedValue? How else is the XSD supposed to understand that your Oracle code will supply the ID?

Are you doing that because Example 1 won't publish? Why not the more general question: "Why won't example 1 publish?"? It might have nothing to do with the sequence. The first thing I would check is nullable being true. Is that correct?

A

abhishekg985Author

Hi Dave, when I put in @GeneratedValue the cdt publish is erroring out, I tried nillable="false" but that doesn't work either.

Error received : data source schema does not match the type mappings: Missing sequence or table: Table_sq

XML :

<xsd:element name="id" nillable="false" 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>

V

venkyu4136

Check this documentation which specifies to select sequence for Oracle database.

[View:https://docs.appian.com/suite/help/22.1/Mapping_CDTs_to_Pre-defined_Database_Tables.html#primary-keys]

peter.lewis

Employee

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?

A

abhishekg985Author

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>

peter.lewis

Employee

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.

Sign up

Login with SSO

Login to the community

Login with SSO