Error Message while saving a data import configuration

Hi @mario
I have just configured the data-import add on, and while trying to save the configuration i got the below error:

 com.haulmont.cuba.core.global.RemoteException:
---
org.springframework.transaction.TransactionSystemException: Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.2.cuba23): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: column "association_lookup_attribute" of relation "ddcdi_import_attribute_mapper" does not exist
  Position: 48
Error Code: 0
Call: INSERT INTO DDCDI_IMPORT_ATTRIBUTE_MAPPER (ID, ASSOCIATION_LOOKUP_ATTRIBUTE, ATTRIBUTE_TYPE, CREATE_TS, CREATED_BY, CUSTOM_ATTRIBUTE_BIND_SCRIPT, DELETE_TS, DELETED_BY, ENTITY_ATTRIBUTE, FILE_COLUMN_ALIAS, FILE_COLUMN_NUMBER, MAPPER_MODE, UPDATE_TS, UPDATED_BY, VERSION, CONFIGURATION_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	bind => [ed4e2616-0d8e-4c98-1274-bd7878e65b0e, null, DIRECT_ATTRIBUTE, 2018-07-20 23:45:20.62, admin, null, null, null, code, code, 0, AUTOMATIC, 2018-07-20 23:45:20.62, null, 1, e4f64a62-b426-c257-e7dd-d94f798f6cea]
Query: InsertObjectQuery(de.diedavids.cuba.dataimport.entity.attributemapper.ImportAttributeMapper-ed4e2616-0d8e-4c98-1274-bd7878e65b0e [new,managed])
---
javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.2.cuba23): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: column "association_lookup_attribute" of relation "ddcdi_import_attribute_mapper" does not exist
  Position: 48
Error Code: 0
Call: INSERT INTO DDCDI_IMPORT_ATTRIBUTE_MAPPER (ID, ASSOCIATION_LOOKUP_ATTRIBUTE, ATTRIBUTE_TYPE, CREATE_TS, CREATED_BY, CUSTOM_ATTRIBUTE_BIND_SCRIPT, DELETE_TS, DELETED_BY, ENTITY_ATTRIBUTE, FILE_COLUMN_ALIAS, FILE_COLUMN_NUMBER, MAPPER_MODE, UPDATE_TS, UPDATED_BY, VERSION, CONFIGURATION_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	bind => [ed4e2616-0d8e-4c98-1274-bd7878e65b0e, null, DIRECT_ATTRIBUTE, 2018-07-20 23:45:20.62, admin, null, null, null, code, code, 0, AUTOMATIC, 2018-07-20 23:45:20.62, null, 1, e4f64a62-b426-c257-e7dd-d94f798f6cea]
Query: InsertObjectQuery(de.diedavids.cuba.dataimport.entity.attributemapper.ImportAttributeMapper-ed4e2616-0d8e-4c98-1274-bd7878e65b0e [new,managed])
---
org.eclipse.persistence.exceptions.DatabaseException: 
Internal Exception: org.postgresql.util.PSQLException: ERROR: column "association_lookup_attribute" of relation "ddcdi_import_attribute_mapper" does not exist
  Position: 48
Error Code: 0
Call: INSERT INTO DDCDI_IMPORT_ATTRIBUTE_MAPPER (ID, ASSOCIATION_LOOKUP_ATTRIBUTE, ATTRIBUTE_TYPE, CREATE_TS, CREATED_BY, CUSTOM_ATTRIBUTE_BIND_SCRIPT, DELETE_TS, DELETED_BY, ENTITY_ATTRIBUTE, FILE_COLUMN_ALIAS, FILE_COLUMN_NUMBER, MAPPER_MODE, UPDATE_TS, UPDATED_BY, VERSION, CONFIGURATION_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	bind => [ed4e2616-0d8e-4c98-1274-bd7878e65b0e, null, DIRECT_ATTRIBUTE, 2018-07-20 23:45:20.62, admin, null, null, null, code, code, 0, AUTOMATIC, 2018-07-20 23:45:20.62, null, 1, e4f64a62-b426-c257-e7dd-d94f798f6cea]
Query: InsertObjectQuery(de.diedavids.cuba.dataimport.entity.attributemapper.ImportAttributeMapper-ed4e2616-0d8e-4c98-1274-bd7878e65b0e [new])
---
org.postgresql.util.PSQLException: ERROR: column "association_lookup_attribute" of relation "ddcdi_import_attribute_mapper" does not exist
  Position: 48
	at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:129)
	at sun.reflect.GeneratedMethodAccessor138.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:629)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:618)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
	at com.sun.proxy.$Proxy243.commit(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.haulmont.cuba.core.sys.remoting.LocalServiceInvokerImpl.invoke(LocalServiceInvokerImpl.java:94)
	at com.haulmont.cuba.web.sys.remoting.LocalServiceProxy$LocalServiceInvocationHandler.invoke(LocalServiceProxy.java:154)
	at com.sun.proxy.$Proxy30.commit(Unknown Source)
	at com.haulmont.cuba.client.sys.DataManagerClientImpl.commit(DataManagerClientImpl.java:96)
	at com.haulmont.cuba.gui.data.impl.GenericDataSupplier.commit(GenericDataSupplier.java:90)
	at com.haulmont.cuba.gui.data.impl.DsContextImpl.commit(DsContextImpl.java:165)
	at com.haulmont.cuba.gui.components.EditorWindowDelegate.commit(EditorWindowDelegate.java:283)
	at com.haulmont.cuba.web.gui.WebWindow$Editor.commitAndClose(WebWindow.java:1755)
	at com.haulmont.cuba.gui.components.AbstractEditor.commitAndClose(AbstractEditor.java:111)
	at com.haulmont.cuba.gui.components.EditorWindowDelegate$2.actionPerform(EditorWindowDelegate.java:102)
	at com.haulmont.cuba.web.gui.components.WebButton.performAction(WebButton.java:45)
	at com.haulmont.cuba.web.gui.components.WebButton.lambda$new$61446b05$1(WebButton.java:37)
	at sun.reflect.GeneratedMethodAccessor285.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:510)
	at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:200)
	at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:163)
	at com.vaadin.server.AbstractClientConnector.fireEvent(AbstractClientConnector.java:1037)
	at com.vaadin.ui.Button.fireClick(Button.java:377)
	at com.haulmont.cuba.web.toolkit.ui.CubaButton.fireClick(CubaButton.java:54)
	at com.vaadin.ui.Button$1.click(Button.java:54)
	at sun.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:158)
	at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:119)
	at com.vaadin.server.communication.ServerRpcHandler.handleInvocation(ServerRpcHandler.java:444)
	at com.vaadin.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:409)
	at com.vaadin.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:274)
	at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:90)
	at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:41)
	at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1435)
	at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:361)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.serviceAppRequest(CubaApplicationServlet.java:312)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.service(CubaApplicationServlet.java:203)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:107)
	at org.springframework.web.filter.CompositeFilter.doFilter(CompositeFilter.java:73)
	at com.haulmont.cuba.web.sys.CubaHttpFilter.doFilter(CubaHttpFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)

hi,

thanks for reaching out. You are absolutely right. There is an error in the postgres scripts. I’ve updated the app component: Release version 0.5.3 · mariodavid/cuba-component-data-import · GitHub

Please check if it works now. Thanks!

Bye
Mario

Thanks a lot Mario, the addon is very cool, i have a couple of questions that i appreciate your help with:

  • if there are errors during the import process how can i report them to the user, for example i have an entity called currency with a code attribute that accepts only capital letters defined as Regex in the @Pattern annotation of the attribute definition. now if the user imported a list of 100 currency entries, some of the entries have lower letter codes, what happened is that the import process didn’t accept any of the entries but also didn’t report any reason to the user, how can i have a log file or something similar.

  • there are 2 behaviors to deal with Unique constraints either to skip the record or to update the entity record, is there a way to stop the whole import process as a 3rd option.

  • can i insert a hidden column in the loader configuration that have a default value that should be programmaticly set, for example if i have have a an entity named Employee, with an attribute called employeesGroup, the EmployeesGroup is the master entity of the Employee, when a user imports employees he first selects a particular EmployeeGroup in the master and then click the import button for the employees.

  • finally the most important question that i have, is it possible to build a loader that doesn’t depend on a particular entity, what i want to accomplish is that a single entry in an excel sheet can possibly enter 5 records in different entities. I can provide a live scenario that i have if needed.

Hi,

ok, so i’ll try to answer the questions accordingly:

1. regarding the logging

There is some logging in place for an execution of a data-import. However, this is not very detailed at the moment, so you cannot really rely on that. I will improve it in the next versions, but up until now this is not really good to let the user look at.

What you can do - and what you should do actually is that instead of letting the user hit against the wall, try to solve the problem for them in the first place. This means: Instead of not excepting the not uppercase values - why don’t you just uppercase the values during import? Then there will be no error and the users will be happier :slight_smile:

This is where custom attribute binding scripts come into play: GitHub - mariodavid/cuba-component-data-import: CUBA component for easy data import

In your case, you could create a custom binding script for the currency attribute that looks like this:

// if the rawValue would be something like "eur" it will convert it to "EUR"
return rawValue.toUpperCase()

Another possibility that will probably come in a later version is the ability for the users to adjust the data directly before the import:

2. stop import process on unique violation

This is correctly not really implemented. You could try to emulate a feature like this with a pre commit script GitHub - mariodavid/cuba-component-data-import: CUBA component for easy data import and throwing exceptions, but this feels a little bit like a hack. Feel free to add a feature request to the issue tracker: Issues · mariodavid/cuba-component-data-import · GitHub

3. initial values for entities

This is an interesting idea. I have never thought about that. It is possible to solve it by defining a column in the import file, which points to the value that you otherwise want to use as a initial value. However, to support that use case, we need some adjustments in the app component. Feel free to add a feature request to the issue tracker: Issues · mariodavid/cuba-component-data-import · GitHub

4. import file → entity 1:N mapping

Currently also not supported, because the whole attribute matching is always based on a root entity. It is possible for a Composition to add elements of the child entity into the import process. But entites that do not have a relation, this is currently not supported. But you can just create multiple import configurations where you reference the different entities and then just use the corresponding subset of columns for the entities.

If you can give a live example, this would be great…

Bye
Mario

Hi Mario,

Thanks i have created 2 issues as suggested. for my 4th question the situation that i have is as following:

  • one excel record has the following attributes:
    1. Transaction Date
    2. Employees Group Name
    3. Employee Number
    4. Employee Name
    5. Cost Center Code
  • what should happen in the system is as following:
    - if the employee doesn’t exist:
    1. insert new Employee using (Employees Group, employee number and employee name)
    2. if the cost center code is not null:
    • insert a new employeeCostCenterLinkage with the following attributes (Employee ID, Cost Center ID (retrieved from getting the cost center entered by the user), start date (from transaction date)).
  • if the employee exists:

    1. update the employee and set the name to the new new name where employee number = employee number of the imported record and employees group = employees group of the imported record
    2. if the cost center code is not null:
    • insert a new employeeCostCenterLinkage with the following attributes (Employee ID, Cost Center ID (retrieved from getting the cost center entered by the user), start date (from transaction date)).
    • update any existing EmployeeCostCenterLinkage if exists and put end date as transaction date - 1.
  • throw an error if the cost center doesn’t exist as a result of wrong cost center code.

now, i think i might be able to do all of this from a pre-commit script but i am confused if it will be called multiple times for each record imported in case the transaction strategy is single transaction or it will be called once in that case?

hi,

the pre commit script gets called for every instance, before it gets persisted. You’ll get in the full entity with all bound values from the file according to your rules and or custom import binding scripts.

The transaction strategy actually does not matter here (although I understand the “preCommit” might sound like this when only one transaction to commit).

So, although a preCommit script would probably do the job, i’m not sure if it works. Because you would have to call dataManager.commit on your own - so an explicit transaction is used. But you could try it.

Generally there is a basic assumption in this app component backed in, that reference data are already there. Your case is a little more advanced.

If you give me a complete example application with the domain model and an example import file and import configuration, i could try to make it happen, but i’m not sure where this is going.

Is this a one-off import, or are you planning to import those multiple times on an ongoing basis?
Depending on that it might also be totally sufficient to just create a service that will do the heavy lifting.

Bye
Mario