Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-75079

Using LENGTH operator causes SQLException on SQL Server

    Details

      Description

      Description
      Currently, we cannot use the LENGTH operator inside of SQL statements that we send through the SQLTransformer, because the SQLTransformer does not account for the fact that the operator is called "LEN" in SQL Server rather than "LENGTH". We should add support for the LENGTH operator, as it would be a very useful operator to improve the performance of some upgrade processes.

      Steps to Reproduce
      1. Start up Liferay connected to a SQL Server database.
      2. Run the attached TestSQLLengthOperator.groovy script.
      3. Navigate to Control Panel > Configuration > Virtual Instances.

      Expected Result: The maximum number of users for each virtual instance would be equal to the length of the Web ID, per the groovy script. For the "liferay.com" company, this should be 11.
      Actual Result: The maximum number of users for each virtual instance is still Unlimited (or whatever it was before). Addtionally, when you ran the groovy script, the following stack trace appeared in the console:

      com.microsoft.sqlserver.jdbc.SQLServerException: 'LENGTH' is not a recognized built-in function name.
              at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:786)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:685)
              at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
              at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:642)
              at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:120)
              at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)
              at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:293)
              at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:315)
              at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:307)
              at com.liferay.portal.kernel.dao.db.BaseDBProcess.runSQL(BaseDBProcess.java:63)
              at com.liferay.portal.kernel.dao.db.DBProcess$runSQL.callCurrent(Unknown Source)
              at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:52)
              at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:154)
              at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:166)
              at TestSQLLengthOperator.doTestSQLLengthOperator(Script1.groovy:8)
              at TestSQLLengthOperator$doTestSQLLengthOperator.call(Unknown Source)
              at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)
              at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)
              at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117)
              at Script1.run(Script1.groovy:20)
              at com.liferay.portal.scripting.groovy.internal.GroovyExecutor.eval(GroovyExecutor.java:64)
              at com.liferay.portal.scripting.internal.ScriptingImpl.eval(ScriptingImpl.java:87)
              at com.liferay.portal.scripting.internal.ScriptingImpl.exec(ScriptingImpl.java:122)
              at com.liferay.server.admin.web.internal.portlet.action.EditServerMVCActionCommand.runScript(EditServerMVCActionCommand.java:453)
              at com.liferay.server.admin.web.internal.portlet.action.EditServerMVCActionCommand.doProcessAction(EditServerMVCActionCommand.java:197)
              at com.liferay.portal.kernel.portlet.bridges.mvc.BaseMVCActionCommand.processAction(BaseMVCActionCommand.java:61)
              at com.liferay.portal.kernel.portlet.bridges.mvc.MVCPortlet.callActionMethod(MVCPortlet.java:380)
              at com.liferay.portal.kernel.portlet.LiferayPortlet.processAction(LiferayPortlet.java:93)
              at com.liferay.portal.kernel.portlet.bridges.mvc.MVCPortlet.processAction(MVCPortlet.java:260)
              at com.liferay.portlet.FilterChainImpl.doFilter(FilterChainImpl.java:71)
              at com.liferay.portal.kernel.portlet.PortletFilterUtil.doFilter(PortletFilterUtil.java:48)
              at com.liferay.portal.kernel.servlet.PortletServlet.service(PortletServlet.java:108)
              at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
              at org.eclipse.equinox.http.servlet.internal.registration.EndpointRegistration.service(EndpointRegistration.java:153)
              at org.eclipse.equinox.http.servlet.internal.servlet.ResponseStateHandler.processRequest(ResponseStateHandler.java:62)
              at org.eclipse.equinox.http.servlet.internal.context.DispatchTargets.doDispatch(DispatchTargets.java:117)
              at org.eclipse.equinox.http.servlet.internal.servlet.RequestDispatcherAdaptor.include(RequestDispatcherAdaptor.java:48)
              at com.liferay.portlet.InvokerPortletImpl.invoke(InvokerPortletImpl.java:531)
              at com.liferay.portlet.InvokerPortletImpl.invokeAction(InvokerPortletImpl.java:577)
              at com.liferay.portlet.InvokerPortletImpl.processAction(InvokerPortletImpl.java:335)
              at com.liferay.portal.monitoring.internal.portlet.MonitoringInvokerPortlet.processAction(MonitoringInvokerPortlet.java:189)
              at com.liferay.portlet.PortletContainerImpl._processAction(PortletContainerImpl.java:386)
              at com.liferay.portlet.PortletContainerImpl.processAction(PortletContainerImpl.java:118)
              at com.liferay.portlet.SecurityPortletContainerWrapper.processAction(SecurityPortletContainerWrapper.java:94)
              at com.liferay.portlet.RestrictPortletContainerWrapper.processAction(RestrictPortletContainerWrapper.java:75)
              at com.liferay.portal.kernel.portlet.PortletContainerUtil.processAction(PortletContainerUtil.java:114)
              at com.liferay.portal.action.LayoutAction.processLayout(LayoutAction.java:295)
              at com.liferay.portal.action.LayoutAction.execute(LayoutAction.java:159)
              at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
              at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228)
              at com.liferay.portal.struts.PortalRequestProcessor.process(PortalRequestProcessor.java:170)
              at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
              at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
              at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
              at com.liferay.portal.servlet.MainServlet.callParentService(MainServlet.java:602)
              at com.liferay.portal.servlet.MainServlet.service(MainServlet.java:579)
              at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
              at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:119)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.servlet.filters.uploadservletrequest.UploadServletRequestFilter.processFilter(UploadServletRequestFilter.java:100)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.servlet.filters.secure.SecureFilter.processFilter(SecureFilter.java:337)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.monitoring.internal.servlet.filter.MonitoringFilter.processFilter(MonitoringFilter.java:181)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilter.doFilter(InvokerFilter.java:99)
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
              at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:720)
              at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:466)
              at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:391)
              at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:318)
              at com.liferay.friendly.url.servlet.FriendlyURLServlet.service(FriendlyURLServlet.java:367)
              at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
              at com.liferay.portal.servlet.ServletAdapter.service(ServletAdapter.java:96)
              at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
              at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:119)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.servlet.filters.uploadservletrequest.UploadServletRequestFilter.processFilter(UploadServletRequestFilter.java:100)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.servlet.filters.strip.StripFilter.processFilter(StripFilter.java:336)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.servlet.filters.gzip.GZipFilter.processFilter(GZipFilter.java:125)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.servlet.filters.secure.SecureFilter.processFilter(SecureFilter.java:337)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.servlet.filters.i18n.I18nFilter.processFilter(I18nFilter.java:364)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.servlet.filters.etag.ETagFilter.processFilter(ETagFilter.java:86)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.servlet.filters.autologin.AutoLoginFilter.processFilter(AutoLoginFilter.java:261)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.sharepoint.SharepointFilter.processFilter(SharepointFilter.java:88)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.servlet.filters.virtualhost.VirtualHostFilter.processFilter(VirtualHostFilter.java:265)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:142)
              at com.liferay.portal.monitoring.internal.servlet.filter.MonitoringFilter.processFilter(MonitoringFilter.java:181)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDirectCallFilter(InvokerFilterChain.java:188)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:96)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDirectCallFilter(InvokerFilterChain.java:188)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:96)
              at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:176)
              at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145)
              at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:92)
              at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:394)
              at com.liferay.portal.servlet.filters.urlrewrite.UrlRewriteFilter.processFilter(UrlRewriteFilter.java:65)
              at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:48)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDoFilter(InvokerFilterChain.java:207)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:112)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDirectCallFilter(InvokerFilterChain.java:168)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:96)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDirectCallFilter(InvokerFilterChain.java:168)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:96)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.processDirectCallFilter(InvokerFilterChain.java:188)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:96)
              at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilter.doFilter(InvokerFilter.java:99)
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
              at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
              at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
              at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
              at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
              at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
              at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
              at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
              at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522)
              at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095)
              at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)
              at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
              at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
              at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
              at java.lang.Thread.run(Thread.java:748)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              linda.sui Linda Sui
              Reporter:
              michael.bowerman Michael Bowerman
              Participants of an Issue:
              Recent user:
              Marta Elicegui
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since last comment:
                4 years, 3 weeks, 1 day ago

                  Packages

                  Version Package
                  7.0.0 DXP FP33
                  7.0.0 DXP SP7
                  7.0.5 CE GA6
                  7.1.X
                  Master