I have read the excel file as data table and trying to insert the whole data table into HSQLDB table using INSERT activity in Database activities package. I can able to insert the whole data table into HSQLDB table. But it inserts all the numeric values as blank values and string column values as NULL value. I am not sure whether it is a bug or the feature not supported by HSQLDB.
I used same flow for PostgresSQL DB, it works fine. Any idea on resolving this issue?
Note that the connections are fine. i am using ODBC data source. when i iterate each row in the data table and insert one by one row into HSQLDB table, it works.
In UiPath, we have a activity called INSERT in database.activities package which is used to insert the datatable values into SQL table. It doesn’t works for HSQLDB. (Note that it inserts the datatable values into SQL table. but evertything as null not the actual values. For your information, I don’t have any trigger to update it as null)
So, I am just using FOR EACH ROW IN DATATABLE to loop each row in datatable and inserting row by row(iteration).
I Just want to know, Why the INSERT activity doesn’t works for HSQLDB?
Our team investigated a bit and this is the conclusion:
Insert activity uses a DataAdapter to automatically generate the proper insert/update queries, based on database schema. In order to generate the queries, ODBC implements standard schema calls. This combination of HSQLDB and PostgreSQL does not return required metadata and the insert sentence provided is “Insert into TableName Default Values”.
We are still investigating, but it comes to the fact that having a DB that is not implementing the standard will not work properly.