Maximum length in Query wrapper?

hMobile · 2018-04-03 10:38:13

Hi,

When trying to have more Parameters I found a limit in the length of the Query wrapper, to approximately 256 chars, Is there any way to break this limit and have a longer query?

Thanks!

Rom

eduardo · 2018-04-04 07:40:45

Hi Rom,

how did you find this limit? Do you get an error if the query is longer than 256 characters? If yes -> could you please send the complete error?

Regards,
Eduardo

hMobile · 2018-04-04 08:09:26

Hi Eduardo,

Yes there is an error window.

I reach the limit doing some precalculations (more than one), but as you can see below the comments also counts...

Thanks in advance for your support.

Rom

/* Hi, this is a really long query just to test de max length
123456778901234567789012345677890123456778901234567789012345677890
123456778901234567789012345677890123456778901234567789012345677890
123456778901234567789012345677890123456778901234567789012345677890
1234567789012345677890*/
SELECT *, DAYOFWEEK(Createdat) DOW FROM ($!{TMP_TABLENAME})

net.datenwerke.gxtdto.client.servercommunication.exceptions.ServerCallFailedException: Error while committing the transaction at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.mapException(CatchStacktraceInterceptor.java:74) at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:47) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52) at net.datenwerke.rs.core.server.reportmanager.ReportManagerTreeHandlerImpl$$EnhancerByGuice$$6b970479.updateNode(<generated>) 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.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587) at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:333) at net.datenwerke.rs.core.server.reportmanager.ReportManagerTreeHandlerImpl$$EnhancerByGuice$$6b970479.CGLIB$processCall$30(<generated>) at net.datenwerke.rs.core.server.reportmanager.ReportManagerTreeHandlerImpl$$EnhancerByGuice$$6b970479$$FastClassByGuice$$b4de0380.invoke(<generated>) at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52) at net.datenwerke.rs.core.server.reportmanager.ReportManagerTreeHandlerImpl$$EnhancerByGuice$$6b970479.processCall(<generated>) at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303) at net.datenwerke.rs.core.server.reportmanager.ReportManagerTreeHandlerImpl$$EnhancerByGuice$$6b970479.CGLIB$processCall$29(<generated>) at net.datenwerke.rs.core.server.reportmanager.ReportManagerTreeHandlerImpl$$EnhancerByGuice$$6b970479$$FastClassByGuice$$b4de0380.invoke(<generated>) at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52) at net.datenwerke.rs.core.server.reportmanager.ReportManagerTreeHandlerImpl$$EnhancerByGuice$$6b970479.processCall(<generated>) at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:373) at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62) at javax.servlet.http.HttpServlet.service(HttpServlet.java:648) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at net.datenwerke.rs.core.server.reportmanager.ReportManagerTreeHandlerImpl$$EnhancerByGuice$$6b970479.CGLIB$service$51(<generated>) at net.datenwerke.rs.core.server.reportmanager.ReportManagerTreeHandlerImpl$$EnhancerByGuice$$6b970479$$FastClassByGuice$$b4de0380.invoke(<generated>) at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38) at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72) at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52) at net.datenwerke.rs.core.server.reportmanager.ReportManagerTreeHandlerImpl$$EnhancerByGuice$$6b970479.service(<generated>) at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263) at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178) at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91) at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62) at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:89) at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163) at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58) at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168) at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58) at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168) at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58) at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118) at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113) 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:94) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504) 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:620) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502) at org.apache.coyote.ajp.AbstractAjpProcessor.process(AbstractAjpProcessor.java:877) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684) at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2527) at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2516) 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) Caused by: javax.persistence.RollbackException: Error while committing the transaction at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:86) at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:87) at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109) at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38) 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.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587) at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:333) at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109) at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38) at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303) at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109) at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38) at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:373) at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62) at javax.servlet.http.HttpServlet.service(HttpServlet.java:648) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109) at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38) ... 31 more Caused by: javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not execute statement at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602) at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:67) ... 51 more Caused by: org.hibernate.exception.DataException: could not execute statement at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:52) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2890) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3384) at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:451) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:336) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:335) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1224) at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:464) at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2905) at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2281) at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:485) at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:146) at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38) at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:230) at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:65) at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:61) ... 51 more Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'query_wrapper' at row 1 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3876) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:1449) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204) ... 68 more

eduardo · 2018-04-04 08:22:12

This seems to be a mysql error:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column

I just tried with a much longer comment, and I am not getting any errors. Could you please post the query_wrapper complete sql query being produced ?
Which reportserver version are you using? Which mysql version?

Regards,
Eduardo

hMobile · 2018-04-04 08:37:57

Eduardo,

I'm using 3.0.3 6002 from Bitnami.

/opt/bitnami/mysql/bin/mysql.bin Ver 15.1 Distrib 10.1.31-MariaDB, for Linux (x86_64) using readline 5.1

I can't understand "the query_wrapper complete sql query being produced"... sorry.

Rom

eduardo · 2018-04-04 09:34:22

In the windows bitnami installer I am not able to reproduce.
I am installing in linux, maybe this is here different.

Regards,
Eduardo

hMobile · 2018-04-04 09:53:16

Ok, my installation is on Mac, VirtualBox, Linux (Debian Bitnami)

Thanks!

Rom

eduardo · 2018-04-05 08:01:15

Hi Rom,

for your information: we created ticket RS-2901 for investigating this issue.

Regards,
Eduardo

hMobile · 2018-04-05 08:09:48

Ok Eduardo, thanks.

... but it looks like due to Linux implementation or Bitnami one?

Because my plan is to put it in a real Linux server, sure with handmade config...

That question is not urgent at this time and maybe the answer comes with the solution, so waiting for news.

Rom

eduardo · 2018-04-05 08:17:35

Hi Rom,

you could try to install the latest bitnami on windows (for testing), this should work, and in manual installations I have also not seen this issue. Since a basic manual installation is very simple (install tomcat, drop reportserver to the webapps dir, and install the database with the sql scripts provided), you could test this manual installation (on linux?) easily. Please let us know if this works.

Regards,
Eduardo

hMobile · 2018-04-05 14:27:02

OMG! Eduardo,

Same issue with the new clean manual installation... It seems clearly related to mysql over Linux...

What do you think about to switch to Postgres in the same box?

Cheers,

Rom

hMobile · 2018-04-12 18:57:53

Hola Eduardo,

Just for your info maybe to add it to the ticket RS-2901.

In the same box, Bitnami 3.0.3-6002 for Linux I was able to make a JXLS with a sql.exec query longer than 256 chars, and it runs smoothly.

Cheers!

Rom

eduardo · 2018-04-16 07:39:31

Hi Rom,

so within the same reportserver installation, you are able to make a JXLS with a long sql.exec, but not a dynamic list with a long sql statement ? Is this correct ?

Regards,
Eduardo

hMobile · 2018-04-16 08:05:04

Yes Eduardo, is correct.

Un saludo,

Rom

eduardo · 2018-07-29 16:49:49

Hi Rom,

this is fixed in the next reportserver version.
For now, you can manually fix it. Change the syntax to your db. This is for mysql:

ALTER TABLE RS_CSV_DATASOURCE_CONF MODIFY query_wrapper longtext;
ALTER TABLE RS_CSV_DATASOURCE_CONF_A MODIFY query_wrapper longtext;

ALTER TABLE RS_SCRIPT_DATASOURCE_CONFIG MODIFY query_wrapper longtext;
ALTER TABLE RS_SCRIPT_DATASOURCE_CONFIG_A MODIFY query_wrapper longtext;

ALTER TABLE RS_BIRT_REPORT_DATASRC_CFG MODIFY query_wrapper longtext;
ALTER TABLE RS_BIRT_REPORT_DATASRC_CFG_A MODIFY query_wrapper longtext;

for postgres:

ALTER TABLE RS_CSV_DATASOURCE_CONF ALTER COLUMN query_wrapper TYPE text;
ALTER TABLE RS_CSV_DATASOURCE_CONF_A ALTER COLUMN query_wrapper TYPE text;

ALTER TABLE RS_SCRIPT_DATASOURCE_CONFIG ALTER COLUMN query_wrapper TYPE text;
ALTER TABLE RS_SCRIPT_DATASOURCE_CONFIG_A ALTER COLUMN query_wrapper TYPE text;

ALTER TABLE RS_BIRT_REPORT_DATASRC_CFG ALTER COLUMN query_wrapper TYPE text;
ALTER TABLE RS_BIRT_REPORT_DATASRC_CFG_A ALTER COLUMN query_wrapper TYPE text;

Regards,
Eduardo

hMobile · 2018-07-30 07:52:48

Hi Eduardo,

Thanks for the update!

Rom

ReportServer Forum

#1 2018-04-03 10:38:13

Maximum length in Query wrapper?

#2 2018-04-04 07:40:45

Re: Maximum length in Query wrapper?

#3 2018-04-04 08:09:26

Re: Maximum length in Query wrapper?

#4 2018-04-04 08:22:12

Re: Maximum length in Query wrapper?

#5 2018-04-04 08:37:57

Re: Maximum length in Query wrapper?

#6 2018-04-04 09:34:22

Re: Maximum length in Query wrapper?

#7 2018-04-04 09:53:16

Re: Maximum length in Query wrapper?

#8 2018-04-05 08:01:15

Re: Maximum length in Query wrapper?

#9 2018-04-05 08:09:48

Re: Maximum length in Query wrapper?

#10 2018-04-05 08:17:35

Re: Maximum length in Query wrapper?

#11 2018-04-05 14:27:02

Re: Maximum length in Query wrapper?

#12 2018-04-12 18:57:53

Re: Maximum length in Query wrapper?

#13 2018-04-16 07:39:31

Re: Maximum length in Query wrapper?

#14 2018-04-16 08:05:04

Re: Maximum length in Query wrapper?

#15 2018-07-29 16:49:49

Re: Maximum length in Query wrapper?

#16 2018-07-30 07:52:48

Re: Maximum length in Query wrapper?

Board footer