Import of Data to Oracle and PostgreSql fails

[linhpd-axonivy]

When importing data into the Oracle database, I encountered an error with the ID field. I am using Excel Dialog Importer version 10.0.10.

Log for Oracle: java.sql.BatchUpdateException: ORA-01400: cannot insert NULL into ("EXCEL_IMPORTER"."ARZNEIMITTELLIGHT"."ID") at oracle.jdbc.driver.OraclePreparedStatement.generateBatchUpdateException(OraclePreparedStatement.java:11016) at oracle.jdbc.driver.OraclePreparedStatement.executeBatchFromQueue(OraclePreparedStatement.java:10700) at oracle.jdbc.driver.OraclePreparedStatement.executeLargeBatch(OraclePreparedStatement.java:10275) at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10221) at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:261) at ch.ivyteam.db.jdbc.proxy.StatementProxy.executeBatch(StatementProxy.java:166) at com.axonivy.util.excel.importer.EntityDataLoader.load(EntityDataLoader.java:57) at com.axonivy.util.excel.importer.EntityDataLoader.load(EntityDataLoader.java:43) at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.lambda$3(ExcelImportProcessor.java:152) at ch.ivyteam.util.callable.AbstractExecutionContext.callInContext(AbstractExecutionContext.java:10) at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.importData(ExcelImportProcessor.java:150) at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.importExcel(ExcelImportProcessor.java:126) at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.lambda$0(ExcelImportProcessor.java:97) at org.eclipse.core.internal.resources.Workspace.run(Workspace.java:2315) at org.eclipse.core.internal.resources.Workspace.run(Workspace.java:2340) at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.run(ExcelImportProcessor.java:94) at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:122) Log for PostgreSql ` java.sql.BatchUpdateException: Batch entry 0 INSERT INTO ArzneimittelLight13 (arzneimittelklassifikation1,arzneimittelklassifikation2,zulassungsnummerZNR,arzneimittelname,zulassungsinhaberName,pNRZulassungsinhaber,darreichungsform,bVLPackungsIDBVLPIDPACKUNG_IDENTIFIKATOR,packungsmenge,anzahlPackungsbehltnisse,packungstyp,inneresBehltnis,anzahlInneresBehltnis,materialInneresBehltnis,verschlusstyp,verschlussmaterial,packungsbeschreibung,platzhalterUPDPI,buyat,buyatunix) VALUES ('BVL-P45ABS6NR1', NULL, '15024.00.00', 'Vetogent oral', 'Bela-Pharm GmbH & Co.KG', 3072170.0, 'Pulver', 1.0, '1000 g', '1 x 1', 'Originalpackung aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'Schachtel', 1.0, 'Papier|Polyethylen|Aluminium|Polyethylen', NULL, 1.0, 'OP1000g; Papier/PE/Al/PE-Schachtel', '00000000-0000-0000-0000-000000000000', '2024-03-07 00:00:00+07'::timestamp, 1.717486924E9) was aborted: ERROR: null value in column "id" of relation "arzneimittellight13" violates not-null constraint Detail: Failing row contains (null, 1, 1 x 1, BVL-P45ABS6NR1, null, Vetogent oral, 1, 2024-03-07 00:00:00, 1717486924, Pulver, Schachtel, Papier|Polyethylen|Aluminium|Polyethylen, 3072170, OP1000g; Papier/PE/Al/PE-Schachtel, 1000 g, Originalpackung aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa..., 00000000-0000-0000-0000-000000000000, 1, null, Bela-Pharm GmbH & Co.KG, 15024.00.00). Call getNextException to see other errors in the batch.

at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)

at org.postgresql.core.ResultHandlerDelegate.handleError(ResultHandlerDelegate.java:52)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2367)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:560)

at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:893)

at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:916)

at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1684)

at ch.ivyteam.db.jdbc.proxy.StatementProxy.executeBatch(StatementProxy.java:166)

at com.axonivy.util.excel.importer.EntityDataLoader.load(EntityDataLoader.java:57)

at com.axonivy.util.excel.importer.EntityDataLoader.load(EntityDataLoader.java:43)

at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.lambda$3(ExcelImportProcessor.java:152)

at ch.ivyteam.util.callable.AbstractExecutionContext.callInContext(AbstractExecutionContext.java:10)

at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.importData(ExcelImportProcessor.java:150)

at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.importExcel(ExcelImportProcessor.java:126)

at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.lambda$0(ExcelImportProcessor.java:97)

at org.eclipse.core.internal.resources.Workspace.run(Workspace.java:2315)

at org.eclipse.core.internal.resources.Workspace.run(Workspace.java:2340)

at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.run(ExcelImportProcessor.java:94)

at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:122)

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "id" of relation "arzneimittellight13" violates not-null constraint Detail: Failing row contains (null, 1, 1 x 1, BVL-P45ABS6NR1, null, Vetogent oral, 1, 2024-03-07 00:00:00, 1717486924, Pulver, Schachtel, Papier|Polyethylen|Aluminium|Polyethylen, 3072170, OP1000g; Papier/PE/Al/PE-Schachtel, 1000 g, Originalpackung aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa..., 00000000-0000-0000-0000-000000000000, 1, null, Bela-Pharm GmbH & Co.KG, 15024.00.00).

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)

... 16 more

`

[ivy-rew]

I'd consider to set the incremental-id manually when working with oracle ... so at least the initial import would succeed. What do you think @linhpd-axonivy

[linhpd-axonivy]

Hi @ivy-rew, The bug is also occurring with PostgreSql (I updated the log in the issue's description). I don't understand what "setting the incremental ID manually" means. Could you please provide more details about it?

[ivy-rew]

Hi @ivy-rew, The bug is also occurring with PostgreSql (I updated the log in the issue's description). I don't understand what "setting the incremental ID manually" means. Could you please provide more details about it?

Ok with the updated log it seems as if just some columns actually contain 'NULL' values. Then I'd just think of a solution to handle this ... like inserting empty strings or the like

[linhpd-axonivy]

Hi @ivy-rew , I investigated the root cause: the ID field is a primary key and must have a non-null value. The modifier DataClassFieldModifier.GENERATED only works for MySQL and SQL Server. For Oracle and PostgreSQL, we need to create an auto-increment field using the sequence object. Therefore, it seems that DataClassFieldModifier.GENERATED does not work for these databases.

[ivy-rew]

Thanks for your your investigation @linhpd-axonivy . I see that there are APIs missing to configure the generator to work with Postgre/Oracle.

However; I think it should be possible to do the initial import with a manual counter, where you calculate the primary key based on a happy path import ... where the table is empty and you are the only importer of data.

[linhpd-axonivy]

Hi @ivy-rew , I got your ideas. Will the "manual counter" be apply only for Oracle and PostgreSql?

[ivy-rew]

Hi @ivy-rew , I got your ideas. Will the "manual counter" be apply only for Oracle and PostgreSql?

this would be my preference yes

[linhpd-axonivy]

Hi @ivy-sgi , Please assist in creating a Jira ticket to address the bug related to the ID field by implementing a manual counter for Oracle and PostgreSql

[ivy-sgi]

Will be implemented in next sprint, starting 01.07