We are using Oracle database and below is the issue:
I have a requirement to load the CSV file data into database. For this, I have created one CDT and it’s primary key is configured as auto generated.
And I am using the “Import CSV to Database V5” smart service to load the data.
Here I am facing an issue and data is not getting inserted into database. This smart service is showing the below error message.
“ORA-01400: Cannot insert NULL into (“APPIANBD”.”testTable”.”id”)”
Where “testTable” is the table name and “id” is the primary key.
The other tables, for which I am loading the data using “Write to Datastore entity” smart service , they are working fine. Data is getting inserted and also id is generated automatically.
Only for these tables, in which I am loading the data using this smart interface, I am facing this issue.
Also, If I manually update the CSV file with some id values, then the data is storing in database. Otherwise, it is not auto generating the id values and throwing the above mentioned error.
Discussion posts and replies are publicly visible
Hi hemasreekosuru
In Write to Datastore entity the cdt has the @generated annotation, that's why the id generates automatically.
In this case, you are inserting the data directly. I would suggest to create a sequence(eg: test_sq) against that table and use test_sq.nextval in the csv file against the ID column and then load the csv
Hi Ankita,
I actually don't have this balance id column in my source (csv) file. I have that in my CDT and database table as primary key and it was configured as auto generated. Also there is a sequence created in Oracle DB.
As the system was throwing the mentioned error and data was not storing in DB, I tried adding that balance id in the source file itself and added values manually. Then the smart service worked and the data was stored.
But in actual scenario, we shouldn't edit the source file and doesn't contain this balance_id column and it should be auto generated just like the way it works when we store the data using Write to data store entity smart service.
I am not sure which version you are using. I got the same issue when I didn't include the ID column in the csv doc with my configurations.
I guess under this configuration, the system expects all the column values. Unless, there's a parameter to read specific columns
Okay. But I tried the same scenario in another Appian server which has MySQL DB, there this process worked. In this I have used Import CSV to Database V4 smart service.
But when i am doing in this Appian server which has Oracle 12c DB, it is failing. And here I am using Import CSV to Database V5 smart service.
Have you tried executing an insert statement in database directly without passing the primary key field.
Did it work properly?
Hi Vadivelan,
Yes I tried and it worked fine.
I am facing exactly the same issue, 'id' field is auto generated in CDT but while using import excel to DB smart service getting error that cannot insert null intio 'id'. if by anychgance you got the solution, can you please share?
I had the same issue as well, but figured it out.
I had a CDT called testTable with columns: id, firstName, lastNameid was defined as the primary key and set to autogenerateDatabase table was built from the CDT through appian
My spreadsheet has columns for firstName and lastName, but nothing for the id.Looks like the plugin was making a straight insert using the columns and data supplied without knowing or doing anything about the id field.
like this...insert into testTable (firstName, lastName) values ("bob", "barker")
The trick was to create the table and CDT in the other order.
First manually create the table in the database with an auto increment primary keyNext create the CDT using the option, 'Create from database table or view'Point your CDT to the newly created table
Now the database knows what to do about the primary key and the import will work.
Instructions for an auto increment pk in oracle...
https://chartio.com/resources/tutorials/how-to-define-an-auto-increment-primary-key-in-oracle/
Hope this helps