HI,
In my Appian app I have a datatype (item) which has item_id column as primary key and this column is marked in Appian to generate next unique identifier. At the same time user can add new entry using form and then process model use write to data store entity node.
Mentioned solution worked fine.item_id was generated by Appian until I have added multiple rows to DB using SQL. After that Appian throw an error as it generates next identified based on Appian previous identifier.
How to force Appian to refresh sequence on Appian side so we can use form to add new entries using write to data store entity?
Details: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update: java.sql.BatchUpdateException: ORA-00001: unique constraint (APN_WARSAW_RAD.SYS_C0030000) violated
Discussion posts and replies are publicly visible
I have found 2 ways how to solve this:
1. Delete table on your data server and recreate them using Appian data store - THIS WILL DELETE ALL YOUR DATA - I haven't tested this solution personally but this should work.
2. Recreate sequence used for that table and PK - this will not delete your data.
drop sequence "sequence_name_SQ"; CREATE SEQUENCE "sequence_name_SQ" INCREMENT BY 1 START WITH 3110669; --This has to be the highest number from pk + 1
While you are adding new data (using insert SQL) and you provide pk by yourself the sequence is not increased. Instead of providing PK value NEXTVAL should be used as fallow:
INSERT INTO Orders_tab (Orderno, Custno) VALUES (Order_seq.NEXTVAL, 1032);
If you want to check what is the next sequence value use code:
select "sequence_name_SQ".nextval from dual;
Provided SQLs are compatible with Oracle DB only.
One clarification - are you providing a PK number in your insert SQL statement? If the column is already set to auto-generate, then you don't need to provide any value for the PK in your insert statement at all; the database will automatically insert the row using the next available PK and increment the sequence.
Hi Peter,
when I'm using write to data store entity node then I don't provide any value for PK.
When I used Insert SQL i did it from Oracle SQLDeveloper not from Appian. In such Insert statement I have provided pk manually as value. This caused that pk value at the end was higher that nextValue from sequence.
Sorry if it was not clear from my first post. I'm glad that it is solved.
Ah gotcha, glad you got it to work!
Hi Furman,
In SQL Developer even has an option for Auto increment in identity column tab. You don't need to provide any value manually.
Although you try to insert data from SQL Developer or from process model, SQL will automatically increment the identity value.
In this case, you don't need to delete any data from your table.
Hi,
I don't think it will work as you described. I have created tables using Appian v20.2 (tables were created in db based on save & publish button on Appian. In this way pk is created, sequence is created but pk is not set as auto increment column. As a consequence insert statement from sql developer requires either pk as value or sequence.NEXTVAL