Error when using Email Template addon

Hi there,

I`m using Cuba Studio 6.10.7 with a MySQL 8 database. After downloading the Email Template addon, I encountered the following issue when clicking on ‘Email templates’ in the Administration section:

MySQLSyntaxErrorException: Unknown column 't1.DTYPE' in 'field list'

Here’s the complete stacktrace:

com.haulmont.cuba.core.global.RemoteException:
---
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.2.cuba24): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't1.DTYPE' in 'field list'
Error Code: 1054
Call: SELECT t1.ID AS a1, t1.DTYPE AS a2, t1.BCC AS a3, t1.CC AS a4, t1.CODE AS a5, t1.DELETE_TS AS a6, t1.DELETED_BY AS a7, t1.FROM_ AS a8, t1.NAME AS a9, t1.SUBJECT AS a10, t1.TO_ AS a11, t1.TYPE_ AS a12, t1.USE_REPORT_SUBJECT AS a13, t1.VERSION AS a14, t1.GROUP_ID AS a15, t0.ID AS a16, t0.DELETE_TS AS a17, t0.DELETED_BY AS a18, t0.NAME AS a19, t0.VERSION AS a20 FROM EMAILTEMPLATES_EMAIL_TEMPLATE t1 LEFT OUTER JOIN EMAILTEMPLATES_TEMPLATE_GROUP t0 ON (t0.ID = t1.GROUP_ID) WHERE (t1.DELETE_TS IS NULL) LIMIT ?, ?
	bind => [0, 50]
Query: ReadAllQuery(referenceClass=EmailTemplate sql="SELECT t1.ID AS a1, t1.DTYPE AS a2, t1.BCC AS a3, t1.CC AS a4, t1.CODE AS a5, t1.DELETE_TS AS a6, t1.DELETED_BY AS a7, t1.FROM_ AS a8, t1.NAME AS a9, t1.SUBJECT AS a10, t1.TO_ AS a11, t1.TYPE_ AS a12, t1.USE_REPORT_SUBJECT AS a13, t1.VERSION AS a14, t1.GROUP_ID AS a15, t0.ID AS a16, t0.DELETE_TS AS a17, t0.DELETED_BY AS a18, t0.NAME AS a19, t0.VERSION AS a20 FROM EMAILTEMPLATES_EMAIL_TEMPLATE t1 LEFT OUTER JOIN EMAILTEMPLATES_TEMPLATE_GROUP t0 ON (t0.ID = t1.GROUP_ID) WHERE (t1.DELETE_TS IS NULL) LIMIT ?, ?")
FetchGroup(){cc, bcc, code, useReportSubject, subject, type, attachedTemplateReports => {class java.lang.Object=FetchGroup(attachedTemplateReports){deleteTs, updatedBy, createdBy, emailTemplate => {class java.lang.Object=FetchGroup(emailTemplate){cc, bcc, code, useReportSubject, subject, name, from, to, id, type, version}}, name, report => {class java.lang.Object=FetchGroup(report){reportType, deleteTs, localeNames, code, xml, defaultTemplate => {class java.lang.Object=FetchGroup(defaultTemplate){deleteTs, reportOutputType, id, version, deletedBy}}, name, description, id, version, deletedBy, group => {class java.lang.Object=FetchGroup(group){deleteTs, localeNames, id, title, version, deletedBy}}}}, createTs, id, updateTs, version, deletedBy, parameterValues => {class java.lang.Object=FetchGroup(parameterValues){deleteTs, parameterType, defaultValue, templateParameters => {class java.lang.Object=FetchGroup(templateParameters){name, id, version}}, alias, id, version, deletedBy}}}}, version, deletedBy, deleteTs, name, from, to, id, group => {class java.lang.Object=FetchGroup(group){deleteTs, name, id, version, deletedBy}}}
---
org.eclipse.persistence.exceptions.DatabaseException: 
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't1.DTYPE' in 'field list'
Error Code: 1054
Call: SELECT t1.ID AS a1, t1.DTYPE AS a2, t1.BCC AS a3, t1.CC AS a4, t1.CODE AS a5, t1.DELETE_TS AS a6, t1.DELETED_BY AS a7, t1.FROM_ AS a8, t1.NAME AS a9, t1.SUBJECT AS a10, t1.TO_ AS a11, t1.TYPE_ AS a12, t1.USE_REPORT_SUBJECT AS a13, t1.VERSION AS a14, t1.GROUP_ID AS a15, t0.ID AS a16, t0.DELETE_TS AS a17, t0.DELETED_BY AS a18, t0.NAME AS a19, t0.VERSION AS a20 FROM EMAILTEMPLATES_EMAIL_TEMPLATE t1 LEFT OUTER JOIN EMAILTEMPLATES_TEMPLATE_GROUP t0 ON (t0.ID = t1.GROUP_ID) WHERE (t1.DELETE_TS IS NULL) LIMIT ?, ?
	bind => [0, 50]
Query: ReadAllQuery(referenceClass=EmailTemplate sql="SELECT t1.ID AS a1, t1.DTYPE AS a2, t1.BCC AS a3, t1.CC AS a4, t1.CODE AS a5, t1.DELETE_TS AS a6, t1.DELETED_BY AS a7, t1.FROM_ AS a8, t1.NAME AS a9, t1.SUBJECT AS a10, t1.TO_ AS a11, t1.TYPE_ AS a12, t1.USE_REPORT_SUBJECT AS a13, t1.VERSION AS a14, t1.GROUP_ID AS a15, t0.ID AS a16, t0.DELETE_TS AS a17, t0.DELETED_BY AS a18, t0.NAME AS a19, t0.VERSION AS a20 FROM EMAILTEMPLATES_EMAIL_TEMPLATE t1 LEFT OUTER JOIN EMAILTEMPLATES_TEMPLATE_GROUP t0 ON (t0.ID = t1.GROUP_ID) WHERE (t1.DELETE_TS IS NULL) LIMIT ?, ?")
FetchGroup(){cc, bcc, code, useReportSubject, subject, type, attachedTemplateReports => {class java.lang.Object=FetchGroup(attachedTemplateReports){deleteTs, updatedBy, createdBy, emailTemplate => {class java.lang.Object=FetchGroup(emailTemplate){cc, bcc, code, useReportSubject, subject, name, from, to, id, type, version}}, name, report => {class java.lang.Object=FetchGroup(report){reportType, deleteTs, localeNames, code, xml, defaultTemplate => {class java.lang.Object=FetchGroup(defaultTemplate){deleteTs, reportOutputType, id, version, deletedBy}}, name, description, id, version, deletedBy, group => {class java.lang.Object=FetchGroup(group){deleteTs, localeNames, id, title, version, deletedBy}}}}, createTs, id, updateTs, version, deletedBy, parameterValues => {class java.lang.Object=FetchGroup(parameterValues){deleteTs, parameterType, defaultValue, templateParameters => {class java.lang.Object=FetchGroup(templateParameters){name, id, version}}, alias, id, version, deletedBy}}}}, version, deletedBy, deleteTs, name, from, to, id, group => {class java.lang.Object=FetchGroup(group){deleteTs, name, id, version, deletedBy}}}
---
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't1.DTYPE' in 'field list'
	at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:129)
	at sun.reflect.GeneratedMethodAccessor142.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:627)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:616)
	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.$Proxy249.loadList(Unknown Source)
	at sun.reflect.GeneratedMethodAccessor266.invoke(Unknown Source)
	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.$Proxy33.loadList(Unknown Source)
	at com.haulmont.cuba.client.sys.DataManagerClientImpl.loadList(DataManagerClientImpl.java:57)
	at com.haulmont.cuba.gui.data.impl.GenericDataSupplier.loadList(GenericDataSupplier.java:117)
	at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.loadData(CollectionDatasourceImpl.java:614)
	at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.refresh(CollectionDatasourceImpl.java:161)
	at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.refresh(CollectionDatasourceImpl.java:119)
	at com.haulmont.cuba.gui.data.impl.GroupDatasourceImpl.setSuspended(GroupDatasourceImpl.java:191)
	at com.haulmont.cuba.gui.data.impl.DsContextImpl.resumeSuspended(DsContextImpl.java:85)
	at com.haulmont.cuba.gui.WindowManager.afterShowWindow(WindowManager.java:1164)
	at com.haulmont.cuba.web.WebWindowManager.showWindow(WebWindowManager.java:422)
	at com.haulmont.cuba.web.WebWindowManager.lambda$showWindow$1(WebWindowManager.java:382)
	at com.haulmont.cuba.web.gui.WebWindow.close(WebWindow.java:1211)
	at com.haulmont.cuba.web.gui.WebWindow.closeAndRun(WebWindow.java:1120)
	at com.haulmont.cuba.gui.components.AbstractWindow.closeAndRun(AbstractWindow.java:306)
	at com.haulmont.cuba.web.WebWindowManager.showWindow(WebWindowManager.java:381)
	at com.haulmont.cuba.gui.WindowManager.openWindow(WindowManager.java:756)
	at com.haulmont.cuba.web.WebWindowManager.openWindow(WebWindowManager.java:158)
	at com.haulmont.cuba.gui.config.MenuCommand$ScreenCommand.run(MenuCommand.java:181)
	at com.haulmont.cuba.gui.config.MenuCommand.execute(MenuCommand.java:76)
	at com.haulmont.cuba.web.sys.SideMenuBuilder.lambda$createMenuCommandExecutor$0(SideMenuBuilder.java:182)
	at com.haulmont.cuba.web.gui.components.mainwindow.WebSideMenu$MenuItemImpl.lambda$setCommand$0(WebSideMenu.java:481)
	at com.haulmont.cuba.web.toolkit.ui.CubaSideMenu$1.menuItemTriggered(CubaSideMenu.java:92)

The database scripts that had to be run after downloading the addon were executed successfully.

Do you have any suggestions on what to do?
Thank you very much.

Best regards,
Markus

Hi,

I`ve changed the field ‘TYPE_’ in the table ‘emailtemplates_email_template’ to ‘DTYPE.’
After starting the application, I get a slightly different error when clicking on Email templates in the menu:

‘MySQLSyntaxErrorException: Unknown column ‘t1.TYPE_’ in ‘field list’’

My suggestions are:

  • The field ‘DTYPE’ is missing in the DB script of the addon (as TYPE_ is available initially).
  • Both field names are used in code, although there should only be one.

Any hints on this?

Thank you.

Best regards,
Markus

Hi,

there should be DTYPE varchar(100) column in EMAILTEMPLATES_EMAIL_TEMPLATE table. This column should be automatically created on DB update.
Please try to create this column manually and provide us yours EMAILTEMPLATES_EMAIL_TEMPLATE table init script for MySQL

Hi Evgeny,

here the init scripts for MySQL.

Greetings
Andreas

mysql.zip (1.2 KB)

Hi,

Thank you for MySQL scripts, we will add these scripts in our next release

Same issue for SQL Server version 1.0.2

image

org.eclipse.persistence.exceptions.DatabaseException: 
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'DTYPE'.
Error Code: 207
Call: SELECT t1.ID, t1.DTYPE, t1.BCC, t1.CC, t1.CODE, t1.DELETE_TS, t1.DELETED_BY, t1.FROM_, t1.NAME, t1.SUBJECT, t1.TO_, t1.TYPE_, t1.USE_REPORT_SUBJECT, t1.VERSION, t1.GROUP_ID, t0.ID, t0.DELETE_TS, t0.DELETED_BY, t0.NAME, t0.VERSION FROM EMAILTEMPLATES_EMAIL_TEMPLATE t1 LEFT OUTER JOIN EMAILTEMPLATES_TEMPLATE_GROUP t0 ON (t0.ID = t1.GROUP_ID) WHERE (t1.DELETE_TS IS NULL)
Query: ReadAllQuery(referenceClass=EmailTemplate sql="SELECT t1.ID, t1.DTYPE, t1.BCC, t1.CC, t1.CODE, t1.DELETE_TS, t1.DELETED_BY, t1.FROM_, t1.NAME, t1.SUBJECT, t1.TO_, t1.TYPE_, t1.USE_REPORT_SUBJECT, t1.VERSION, t1.GROUP_ID, t0.ID, t0.DELETE_TS, t0.DELETED_BY, t0.NAME, t0.VERSION FROM EMAILTEMPLATES_EMAIL_TEMPLATE t1 LEFT OUTER JOIN EMAILTEMPLATES_TEMPLATE_GROUP t0 ON (t0.ID = t1.GROUP_ID) WHERE (t1.DELETE_TS IS NULL)")
FetchGroup(){cc, bcc, code, useReportSubject, subject, type, attachedTemplateReports => {class java.lang.Object=FetchGroup(attachedTemplateReports){deleteTs, updatedBy, createdBy, emailTemplate => {class java.lang.Object=FetchGroup(emailTemplate){cc, bcc, code, useReportSubject, subject, name, from, to, id, type, version}}, name, report => {class java.lang.Object=FetchGroup(report){reportType, deleteTs, localeNames, code, xml, defaultTemplate => {class java.lang.Object=FetchGroup(defaultTemplate){deleteTs, reportOutputType, id, version, deletedBy}}, name, description, id, version, deletedBy, group => {class java.lang.Object=FetchGroup(group){deleteTs, localeNames, id, title, version, deletedBy}}}}, createTs, id, updateTs, version, deletedBy, parameterValues => {class java.lang.Object=FetchGroup(parameterValues){deleteTs, parameterType, defaultValue, templateParameters => {class java.lang.Object=FetchGroup(templateParameters){name, id, version}}, alias, id, version, deletedBy}}}}, version, deletedBy, deleteTs, name, from, to, id, group => {class java.lang.Object=FetchGroup(group){deleteTs, name, id, version, deletedBy}}}
---
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'DTYPE'.
	at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:129)
	at sun.reflect.GeneratedMethodAccessor272.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:627)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:616)
	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.$Proxy254.loadList(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.$Proxy33.loadList(Unknown Source)
	at com.haulmont.cuba.client.sys.DataManagerClientImpl.loadList(DataManagerClientImpl.java:57)
	at com.haulmont.cuba.gui.data.impl.GenericDataSupplier.loadList(GenericDataSupplier.java:117)
	at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.loadData(CollectionDatasourceImpl.java:614)
	at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.refresh(CollectionDatasourceImpl.java:161)
	at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.refresh(CollectionDatasourceImpl.java:119)
	at com.haulmont.cuba.gui.data.impl.GroupDatasourceImpl.setSuspended(GroupDatasourceImpl.java:191)
	at com.haulmont.cuba.gui.data.impl.DsContextImpl.resumeSuspended(DsContextImpl.java:85)
	at com.haulmont.cuba.gui.WindowManager.afterShowWindow(WindowManager.java:1164)
	at com.haulmont.cuba.web.WebWindowManager.showWindow(WebWindowManager.java:422)
	at com.haulmont.cuba.gui.WindowManager.openWindow(WindowManager.java:756)
	at com.haulmont.cuba.web.WebWindowManager.openWindow(WebWindowManager.java:158)
	at com.haulmont.cuba.gui.config.MenuCommand$ScreenCommand.run(MenuCommand.java:181)
	at com.haulmont.cuba.gui.config.MenuCommand.execute(MenuCommand.java:76)
	at com.haulmont.cuba.web.sys.SideMenuBuilder.lambda$createMenuCommandExecutor$0(SideMenuBuilder.java:182)
	at com.haulmont.cuba.web.gui.components.mainwindow.WebSideMenu$MenuItemImpl.lambda$setCommand$0(WebSideMenu.java:481)
	at com.haulmont.cuba.web.toolkit.ui.CubaSideMenu$1.menuItemTriggered(CubaSideMenu.java:92)
	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.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:324)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.service(CubaApplicationServlet.java:210)
	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:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493)
	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:800)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498)
	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)

Added DTYPE manually resolved that error, although I had to exclude the DROP scripts studio generated to remove the manually added column when it found it.

When attempting to save a template I then received the following error:

I assume the fix is the same and I need to add an HTML column to the table.

com.haulmont.cuba.core.global.RemoteException:
---
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.2.cuba24): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'HTML'.
Error Code: 207
Call: INSERT INTO EMAILTEMPLATES_EMAIL_TEMPLATE (ID, BCC, CC, CODE, CREATE_TS, CREATED_BY, DELETE_TS, DELETED_BY, FROM_, HTML, NAME, REPORT_XML, SUBJECT, TO_, TYPE_, UPDATE_TS, UPDATED_BY, USE_REPORT_SUBJECT, VERSION, GROUP_ID, DTYPE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	bind => [A09E65E1-B2F5-AC12-6030-23E64118C645, null, null, TEST_EMAIL, 2019-04-10 09:55:31.366, admin, null, null, null, <style>* { box-sizing: border-box; } body {margin: 0;}</style>, Test, {"metaClass":"report$Report","id":"9b588ca4-7059-4cda-1cbe-cf40b0041977","code":"TEST_EMAIL","roles":[],"defaultTemplate":{"metaClass":"report$ReportTemplate","id":"13a03abd-1c6b-28b7-eec5-0576fe0c6c2a","code":"DEFAULT","customDefinedBy":"100","custom":"false","reportOutputType":"30","report":{"metaClass":"report$Report","id":"9b588ca4-7059-4cda-1cbe-cf40b0041977"},"name":"template.html","alterable":"false"},"bands":[{"metaClass":"report$BandDefinition","id":"bd431b03-61f7-9d8d-5270-b9bbc63753c3","orientation":"","childrenBandDefinitions":[],"name":"Root","report":{"metaClass":"report$Report","id":"9b588ca4-7059-4cda-1cbe-cf40b0041977"},"dataSets":[{"metaClass":"report$DataSet","id":"08231c6d-1e99-ab17-12ba-b003ca99aeaf","type":"30","jsonSourceType":"10","name":"Root","useExistingView":"false","bandDefinition":{"metaClass":"report$BandDefinition","id":"bd431b03-61f7-9d8d-5270-b9bbc63753c3"},"text":"","view":"null"}],"position":"0"}],"inputParameters":[],"validationOn":"false","reportType":"10","reportScreens":[],"valuesFormats":[],"templates":[{"metaClass":"report$ReportTemplate","id":"13a03abd-1c6b-28b7-eec5-0576fe0c6c2a"}],"system":"false","name":"Test"}, null, null, json, 2019-04-10 09:55:31.366, null, true, 1, null, emailtemplates$JsonEmailTemplate]
Query: InsertObjectQuery(com.haulmont.addon.emailtemplates.entity.JsonEmailTemplate-a09e65e1-b2f5-ac12-6030-23e64118c645 [new,managed])
---
org.eclipse.persistence.exceptions.DatabaseException: 
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'HTML'.
Error Code: 207
Call: INSERT INTO EMAILTEMPLATES_EMAIL_TEMPLATE (ID, BCC, CC, CODE, CREATE_TS, CREATED_BY, DELETE_TS, DELETED_BY, FROM_, HTML, NAME, REPORT_XML, SUBJECT, TO_, TYPE_, UPDATE_TS, UPDATED_BY, USE_REPORT_SUBJECT, VERSION, GROUP_ID, DTYPE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	bind => [A09E65E1-B2F5-AC12-6030-23E64118C645, null, null, TEST_EMAIL, 2019-04-10 09:55:31.366, admin, null, null, null, <style>* { box-sizing: border-box; } body {margin: 0;}</style>, Test, {"metaClass":"report$Report","id":"9b588ca4-7059-4cda-1cbe-cf40b0041977","code":"TEST_EMAIL","roles":[],"defaultTemplate":{"metaClass":"report$ReportTemplate","id":"13a03abd-1c6b-28b7-eec5-0576fe0c6c2a","code":"DEFAULT","customDefinedBy":"100","custom":"false","reportOutputType":"30","report":{"metaClass":"report$Report","id":"9b588ca4-7059-4cda-1cbe-cf40b0041977"},"name":"template.html","alterable":"false"},"bands":[{"metaClass":"report$BandDefinition","id":"bd431b03-61f7-9d8d-5270-b9bbc63753c3","orientation":"","childrenBandDefinitions":[],"name":"Root","report":{"metaClass":"report$Report","id":"9b588ca4-7059-4cda-1cbe-cf40b0041977"},"dataSets":[{"metaClass":"report$DataSet","id":"08231c6d-1e99-ab17-12ba-b003ca99aeaf","type":"30","jsonSourceType":"10","name":"Root","useExistingView":"false","bandDefinition":{"metaClass":"report$BandDefinition","id":"bd431b03-61f7-9d8d-5270-b9bbc63753c3"},"text":"","view":"null"}],"position":"0"}],"inputParameters":[],"validationOn":"false","reportType":"10","reportScreens":[],"valuesFormats":[],"templates":[{"metaClass":"report$ReportTemplate","id":"13a03abd-1c6b-28b7-eec5-0576fe0c6c2a"}],"system":"false","name":"Test"}, null, null, json, 2019-04-10 09:55:31.366, null, true, 1, null, emailtemplates$JsonEmailTemplate]
Query: InsertObjectQuery(com.haulmont.addon.emailtemplates.entity.JsonEmailTemplate-a09e65e1-b2f5-ac12-6030-23e64118c645 [new])
---
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'HTML'

Looking at the scripts on github, it seems the missing columns for EMAILTEMPLATES_EMAIL_TEMPLATE are:

        DTYPE nvarchar(100)
-- from emailtemplates$ReportEmailTemplate
        EMAIL_BODY_REPORT_ID UNIQUEIDENTIFIER
-- from emailtemplates$JsonEmailTemplate
      , HTML                 NVARCHAR(MAX)
      , REPORT_XML           NVARCHAR(MAX);

Another error:

SQLServerException: Invalid object name 'EMAILTEMPLATES_EMLTPTE_FDLINK'.

com.haulmont.cuba.core.global.RemoteException:
---
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.2.cuba24): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'EMAILTEMPLATES_EMLTPTE_FDLINK'.
Error Code: 208
Call: SELECT t1.ID, t1.CREATE_DATE, t1.DELETE_TS, t1.DELETED_BY, t1.EXT, t1.NAME, t1.FILE_SIZE, t1.VERSION, t0.EMAIL_TEMPLATE_ID FROM EMAILTEMPLATES_EMLTPTE_FDLINK t0, SYS_FILE t1 WHERE (((t1.ID = t0.FILE_DESCRIPTOR_ID) AND (t0.EMAIL_TEMPLATE_ID IN (?))) AND (t1.DELETE_TS IS NULL))
	bind => [8226B351-4DB9-3778-C76F-EC85348738BD]
Query: ReadAllQuery(name="attachedFiles" referenceClass=FileDescriptor sql="SELECT t1.ID, t1.CREATE_DATE, t1.DELETE_TS, t1.DELETED_BY, t1.EXT, t1.NAME, t1.FILE_SIZE, t1.VERSION, t0.EMAIL_TEMPLATE_ID FROM EMAILTEMPLATES_EMLTPTE_FDLINK t0, SYS_FILE t1 WHERE (((t1.ID = t0.FILE_DESCRIPTOR_ID) AND (t0.EMAIL_TEMPLATE_ID IN ?)) AND (t1.DELETE_TS IS NULL))")
FetchGroup(attachedFiles){deleteTs, extension, size, name, id, version, deletedBy, createDate}
---
org.eclipse.persistence.exceptions.DatabaseException: 
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'EMAILTEMPLATES_EMLTPTE_FDLINK'.
Error Code: 208
Call: SELECT t1.ID, t1.CREATE_DATE, t1.DELETE_TS, t1.DELETED_BY, t1.EXT, t1.NAME, t1.FILE_SIZE, t1.VERSION, t0.EMAIL_TEMPLATE_ID FROM EMAILTEMPLATES_EMLTPTE_FDLINK t0, SYS_FILE t1 WHERE (((t1.ID = t0.FILE_DESCRIPTOR_ID) AND (t0.EMAIL_TEMPLATE_ID IN (?))) AND (t1.DELETE_TS IS NULL))
	bind => [8226B351-4DB9-3778-C76F-EC85348738BD]
Query: ReadAllQuery(name="attachedFiles" referenceClass=FileDescriptor sql="SELECT t1.ID, t1.CREATE_DATE, t1.DELETE_TS, t1.DELETED_BY, t1.EXT, t1.NAME, t1.FILE_SIZE, t1.VERSION, t0.EMAIL_TEMPLATE_ID FROM EMAILTEMPLATES_EMLTPTE_FDLINK t0, SYS_FILE t1 WHERE (((t1.ID = t0.FILE_DESCRIPTOR_ID) AND (t0.EMAIL_TEMPLATE_ID IN ?)) AND (t1.DELETE_TS IS NULL))")
FetchGroup(attachedFiles){deleteTs, extension, size, name, id, version, deletedBy, createDate}
---
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'EMAILTEMPLATES_EMLTPTE_FDLINK'.
	at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:129)
	at sun.reflect.GeneratedMethodAccessor264.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:627)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:616)
	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.$Proxy254.load(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.$Proxy33.load(Unknown Source)
	at com.haulmont.cuba.client.sys.DataManagerClientImpl.load(DataManagerClientImpl.java:51)
	at com.haulmont.cuba.client.sys.DataManagerClientImpl.reload(DataManagerClientImpl.java:91)
	at com.haulmont.cuba.client.sys.DataManagerClientImpl.reload(DataManagerClientImpl.java:78)
	at com.haulmont.cuba.client.sys.DataManagerClientImpl.reload(DataManagerClientImpl.java:73)
	at com.haulmont.cuba.client.sys.DataManagerClientImpl.reload(DataManagerClientImpl.java:68)
	at com.haulmont.addon.emailtemplates.web.emailtemplate.browse.EmailTemplateBrowse.onSendEmailClick(EmailTemplateBrowse.java:85)
	at com.haulmont.addon.emailtemplates.web.emailtemplate.browse.EmailTemplateBrowse.lambda$init$0(EmailTemplateBrowse.java:66)
	at com.haulmont.cuba.gui.components.actions.BaseAction.actionPerform(BaseAction.java:228)
	at com.haulmont.cuba.web.gui.components.WebButton.performAction(WebButton.java:46)
	at com.haulmont.cuba.web.gui.components.WebButton.lambda$new$61446b05$1(WebButton.java:38)
	at sun.reflect.GeneratedMethodAccessor325.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:69)
	at com.vaadin.ui.Button$1.click(Button.java:54)
	at sun.reflect.GeneratedMethodAccessor324.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:324)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.service(CubaApplicationServlet.java:210)
	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:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493)
	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:800)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498)
	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 @tommeacham

Please provide which addon and CUBA versions do you use in your project and what is your CUBA studio version?

Regards,
Evgeny

Hi,

We have recently released new addon version 1.0.3 for CUBA 6.10 and 1.1.3 for CUBA 7.0, these versions contain DB scripts for MS SQL and MySQL databases.

Regards,
Evgeny

1 Like

Wow, thanks for the quick response! I will try out the new version shortly.

The project is in 6.10 for the record.