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 188.8.131.52.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.
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.
Hey Lewis, The issue I am facing is not about adding the annotation. When I select Auto-generate checkbox system prompts me to select a sequence. The argument from my team is oracle would automatically create an id when passed null and appian need not use the sequence to generate this Id. The issue with selecting these sequences is that they are autogenerated and when moved to higher environment the sequence names changes and it would be hard to maintain these CDTs when moving to higher environments as every time I move I need to associate them with the appropriate sequence.
That option should only display if you create a CDT that directly references a database table - I'm suggesting instead that you create a CDT from scratch and configure the primary key there with the "auto-generate" option selected. Then, add the CDT to a data store and publish the data store. When you do this, it should create a table in your database with the appropriate configurations that use auto-increment (without a sequence).
Hey Peter, Thank you for the suggested approach. I was able to create a cdt and table without associating a sequence to it. But this would only solve my issue halfway. With this approach I think appian handles sequence generation part of the code. Can you shed some light on how the auto increment mechanism is handled at appian. The reason I am asking this is : when I tried to insert a null value to primary key column into the table it fails but when I tried to write using write to datastore entity it is writing successfully. The table we use are being shared between appian and an other application. The other application would just write null and db is handling the sequence generation part. If I have more details on how these id value are being generated from appian I can suggest the other application team to implement the same.
Sorry for the delay - in general the auto-increment behavior for CDTs is not managed by Appian; instead it uses the database to auto-increment the value (either through an auto-increment property or sequence).
It's possible I'm misremembering a difference with Oracle compared to other sources, but usually as long as you define the auto-generate property when setting up your CDT and then it will create the table with that property as well.
Can you share the CDT that is created when you create from scratch? Then when you add the CDT to a data store, you should also see an option to download the DDL script from the data store - can you share that DDL script as well?
I think that's how it works, though I remember that some DBs make you go through a little bit of extra effort defining a sequence. It's been a long time, but I think it was Oracle we were using when I had to include a SEQ script with every one of my DDL scripts. Those were in general not a huge lift, like 3 or 4 lines of code, and you could tack them onto the end of your DLL.
I generally find that creating the table from the DB Client, then manually constructing an XSD in Notepad++, then creating the CDT from XSD last, though time consuming, does ensure the least likelihood of serious complications. If you generate typos like me, you may have to create new version from XSD a couple of times, and then it's fine.
© 2022 Appian. All rights reserved.