Hi
I am working on version 19.2.
One of my existing application's DB related work is done on Appian's MySQL database. We are now migrating the DB to Oracle 12c.
There are many tables in MySQL where the primary key is autogenerated. Below is the XSD:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types" targetNamespace="urn:com:appian:types"> <xsd:complexType name="MYAPP_DataInsertion"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Table(name="MYAPP_DATA_INSERTION")</xsd:appinfo> <xsd:documentation><![CDATA[File Upload fields for Doc to be uploaded.]]></xsd:documentation> </xsd:annotation> <xsd:sequence> <xsd:element name="ID" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Id @GeneratedValue @Column(name="ID")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="Type" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="TYPE", nullable=true, columnDefinition="VARCHAR(20)")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="Action_By" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="ACTION_BY", nullable=true, columnDefinition="VARCHAR(50)")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="Action_Date" nillable="true" type="xsd:date"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="ACTION_DATE", nullable=true, columnDefinition="DATE")</xsd:appinfo> </xsd:annotation> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:schema>
When the user does insertion from the UI, the string generated for insertion in table is:
[ID=, Type=Type1,Action_By=dummyUser, Action_Date=14/08/2019]
In my PM, after the UI, Write to data store entity is hit and the above string is inserted in the corresponding table.
In Oracle 12c, the SQL command written to generate the same table is:
CREATE TABLE "B"."MYAPP_DATA_INSERTION" ( "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 167 CACHE 20 NOORDER NOCYCLE NOT NULL ENABLE, "TYPE" VARCHAR2(20 CHAR), "ACTION_BY" VARCHAR2(50 CHAR), "ACTION_DATE" DATE, PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "T_B" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "T_B" ;
Now if my Write to data store entity points to this Oracle table and I try to insert the same string as mentioned above, I get the below error:
An error occurred while trying to write to the entity “MYAPP_DataInsertion” [id=0eee4d0c-9afb-431c-b5f1-f264d38f6bcb@237544, type=MYAPP_DataInsertion (id=11724)] (data store: MYAPP Data Store (Oracle)). Details: org.hibernate.id.IdentifierGenerationException: ids for this class must be manually assigned before calling save(): MYAPPInsertProcess14931 Data: TypedValue[it=11725,v={{<null>,Type1,dummyUser,2019-08-14}}]
However, if I run an insert query directly from Oracle which is somewhat like this:
insert into MYAPP_DATA_INSERTION (ID,TYPE,ACTION_BY,ACTION_DATE) values (NULL,'Type1','dummyUser',to_date('14-AUG-19','DD-MON-YY'));
the insertion happens.
Has anyone faced any such problem while using Oracle12c with Appian?
Thanks in advance!!
Discussion posts and replies are publicly visible
Hi,
For Oracle : you must Specifies what sequence will be used to automatically generate the primary key value.
<xsd:annotation> <xsd:appinfo source="appian.jpa"> @Id @SequenceGenerator(name="MY_SQ", sequenceName="MY_SQ") @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="MY_SQ") @Column(name="MY_PK", nullable=false, unique=true, columnDefinition="NUMBER") </xsd:appinfo> </xsd:annotation>
Thanks
Vinay
Hi vinayr273
Thanks for replying!!
With Oracle 12c, a new feature of Identity has been introduced. So instead of Sequence Generator, IDENTITY is used in the code shared by me. With Sequence Generator it is working, but I am looking for an alternative in Appian to work with Oracle 12c IDENTITY.
Is it possible that the column in the Oracle DB is id and not ID, hence this does not work:@Column(name="ID"). Also did you let Appian create the sequence or di you create it? IF you created it manually, check the name (including case). Depending on your teneo properties, Oracle may be expecting all lower case. Just some things to check
I also try not to use reserved words as column names....
Hope this helps...
Have you tried: @GeneratedValue(strategy = GenerationType.IDENTITY)
Did it work for you ? I'm facing the same issue.
Using @GeneratedValue(strategy = GenerationType.IDENTITY) from the hibernate documentation result in this:
org.hibernate.MappingException: com.appiancorp.rdbms.hb.dialect.AppianOracleDialect does not support identity key generation Data
Seems that Appian does not support this yet so we have to go back to sequences. Maybe someone at Appian could implement this in the future, since new Database Schemas in newer oracle versions may start using Identity columns like we did.