You can translate the question and the replies:

How can we insert rows with a sequence generated primary key?

We would like to do POST requests to Denodo 5 webservices without including a primary key in the HTTP body/document and then have oracle generate the primary key based on a sequence. The equivalent SQL statement we are trying to mimic in Denodo is: insert into sample.test_example (my_code, my_name, my_strategy) values (sample.test_sequence.nextval, '123', '456'); "my_code" is the primary key. However, when we submit the '123', '456' data via POST to Denodo, we are getting this error back: <error> <message code="50025">Error executing sentence: Cannot insert NULL into 'my_code'</message> </error> Is there a way to insert rows into our tables when we are generating the primary key via sequence (e.g., "sample.test_sequence.nextval")
user
09-10-2014 14:30:52 -0400

2 Answers

Hi, the best approach in this case will be to add an Oracle Trigger for your Insert command in order to get the value from the Sequence. Similar to: CREATE SEQUENCE t1_id_seq start with 1 increment by 1; CREATE TRIGGER trigger_name BEFORE INSERT ON t1 FOR EACH ROW DECLARE BEGIN IF( :new.id IS NULL ) THEN :new.id := t1_id_seq.nextval; END IF; END; The error you are getting is due to the "Not Null" restriction in your field. In order to avoid the error you need to change your view VQL to add the IS_AUTOINCREMENT property to your id column. While working with other databases such as MySQL, auto increment fields are automatically detected. As Oracle doesn't provide an auto increment type this needs to be done manually. In order to do that: - Click on the VQL button for your view - Copy and edit the WRAPPER VQL, adding IS_AUTOINCREMENT to your field (in the example we are using "client_id") CREATE OR REPLACE WRAPPER JDBC target_client_7660 FOLDER = '' DATASOURCENAME=acme_crm CATALOGNAME='acme_crm' RELATIONNAME='target_client' OUTPUTSCHEMA ( client_id = 'client_id' :'java.lang.Integer' (OPT) (sourcetyperadix='10', sourcetypename='INT', sourcetypeid='4', sourcetypedecimals='0', sourcetypesize='10') NOT NULL SORTABLE IS_AUTOINCREMENT, name = 'name' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypename='VARCHAR', sourcetypeid='12', sourcetypesize='100') NOT NULL SORTABLE, surname = 'surname' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypename='VARCHAR', sourcetypeid='12', sourcetypesize='100') SORTABLE, client_type = 'client_type' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypename='VARCHAR', sourcetypeid='12', sourcetypesize='2') SORTABLE, company_name = 'company_name' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypename='VARCHAR', sourcetypeid='12', sourcetypesize='100') SORTABLE ) - Load the VQL Please let us know if this approach is valid in your case.
Denodo Team
09-10-2014 21:03:58 -0400
Hi, This is very much an oracle question. The answer already supplied ( ie. use the trigger ) is the standard solution. However, be aware of the fact that this process can easily raise "Duplicate value in index" exception. Kind Regards Arif Zaman
user
10-10-2014 01:07:01 -0400
You must sign in to add an answer. If you do not have an account, you can register here