ReportServer Forum

unsi_2

Member

Registered: 2020-06-24

datasource issue postgres

hi
we can connect via BIRT and JDBC, but we are getting this error when trying to connect to posgresql (incorta) via a datasource:

org.postgresql.util.PSQLException: ERROR: Failed to parse query SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
[net sf jsqlparser JSQL Parser Exception

is there something we can add to properties in the datasource?

kind regards

eduardo

Administrator

Registered: 2016-11-01

Website

Re: datasource issue postgres

Hi unsi_2,

an you pls post the complete error stack?
Can you try via a simple dynamic list with a simple query e.g. "select * from RS_USER" ? do you get this error too?

Regards,
Eduardo

unsi_2

Member

Registered: 2020-06-24

Re: datasource issue postgres

Hi Eduardo

this the error stack, it seems RS sends an extra query not recognized by the server:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

(yes, it happens the same testing a simple query in a dynamic list)
-----------------------------------------------------------------------------------

net.datenwerke.gxtdto.client.servercommunication.exceptions.NonFatalException: The report could not be executed: Could not open connection to: jdbc:postgresql://xxxxxxxx.xxxx/xxxx with user: user. org.postgresql.util.PSQLException: ERROR: Failed to parse query SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED[net sf jsqlparser JSQL Parser Exception: Failed to parse que
    at net.datenwerke.rs.base.server.table.TableReportUtilityServiceImpl.loadColumnDefinition(TableReportUtilityServiceImpl.java:367)
    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:681)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    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.inject.servlet.ServletDefinition.doServiceImpl(ServletDefinition.java:290)
    at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:280)
    at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:184)
    at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:89)
    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:85)
    at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:94)
    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:82)
    at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:121)
    at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:133)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:190)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:163)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1723)
    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)
Caused by: net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: The report could not be executed: Could not open connection to: jdbc:postgresql://xxxxxxxx.xxxx/xxxx with user: user. org.postgresql.util.PSQLException: ERROR: Failed to parse query SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED[net sf jsqlparser JSQL Parser Exception: Failed to parse que
    at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:243)
    at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:144)
    at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:136)
    at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:131)
    at net.datenwerke.rs.base.service.reportengines.table.TableReportUtilsImpl.getReturnedColumns(TableReportUtilsImpl.java:191)
    at net.datenwerke.rs.base.service.reportengines.table.TableReportUtilsImpl.getReturnedPlainColumns(TableReportUtilsImpl.java:149)
    at net.datenwerke.rs.base.server.table.TableReportUtilityServiceImpl.loadColumnDefinition(TableReportUtilityServiceImpl.java:363)
    ... 47 more
Caused by: net.datenwerke.rs.core.service.reportmanager.exceptions.DatabaseConnectionException: Could not open connection to: jdbc:postgresql://xxxxxxxx.xxxx/xxxx with user: user. org.postgresql.util.PSQLException: ERROR: Failed to parse query SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED[net sf jsqlparser JSQL Parser Exception: Failed to parse que
    at net.datenwerke.rs.base.service.datasources.transformers.database.Database2TableTransformer.transform(Database2TableTransformer.java:57)
    at net.datenwerke.rs.base.service.datasources.transformers.database.Database2TableTransformer.transform(Database2TableTransformer.java:1)
    at net.datenwerke.rs.base.service.datasources.transformers.DatasourceTransformationServiceImpl.transform(DatasourceTransformationServiceImpl.java:29)
    at net.datenwerke.rs.core.service.reportmanager.engine.ReportEngine.transformDatasource(ReportEngine.java:229)
    at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.doExecuteNormal(TableReportEngine.java:171)
    at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.doExecuteNormal(TableReportEngine.java:151)
    at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.doExecute(TableReportEngine.java:88)
    at net.datenwerke.rs.core.service.reportmanager.engine.ReportEngine.execute(ReportEngine.java:143)
    at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:211)
    ... 53 more
Caused by: java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: Failed to parse query SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED[net sf jsqlparser JSQL Parser Exception: Failed to parse query SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED].
Failed to parse query SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.
JSQL Parser Exception.
Encountered"" AS"" AS"" at line 1, column 29 Was expecting one of:" TO"" NULL"" CASE"" INTERVAL"" GROUP_ CONCAT"< K_ TIME_ KEY_ EXPR>< S_ CHAR_ LITERAL>"=""~""@""!!""|/""||/""{d""{t""{ts""@"

  Detail: java.sql.SQLException: Failed to parse query SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED [net.sf.jsqlparser.JSQLParserException: Failed to parse query SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED]
    at com.incorta.sqlinterface.exception.ErrorCode.toSQLException(ErrorCode.java:61)
    at com.incorta.sqlinterface.exception.BridgeException.get(BridgeException.java:58)
    at com.incorta.sqlinterface.exception.BridgeException.convert(BridgeException.java:135)
    at com.incorta.sqlinterface.exception.BridgeException.toSQLException(BridgeException.java:108)
    at com.incorta.sqlinterface.net.traffic.io.MessageWriter.sendErrorResponse(MessageWriter.java:561)
    at com.incorta.sqlinterface.net.traffic.command.AbstractQueryCommand.executeQuery(AbstractQueryCommand.java:150)
    at com.incorta.sqlinterface.net.traffic.command.ExecuteCommand.process(ExecuteCommand.java:97)
    at com.incorta.sqlinterface.net.traffic.command.PG_COMMAND.execute(PG_COMMAND.java:42)
    at com.incorta.sqlinterface.net.traffic.TrafficCop.processLoop(TrafficCop.java:226)
    at com.incorta.sqlinterface.net.traffic.TrafficCop.run(TrafficCop.java:143)
Caused by: net.sf.jsqlparser.JSQLParserException: Failed to parse query SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    at com.incorta.sqlinterface.parser.Query.parseQuery(Query.java:748)
    at com.incorta.sqlinterface.parser.Query.parseStatement(Query.java:758)
    at com.incorta.sqlinterface.parser.Query.analyse(Query.java:559)
    at com.incorta.sqlinterface.net.traffic.command.AbstractQueryCommand.getResult(AbstractQueryCommand.java:259)
    at com.incorta.sqlinterface.net.traffic.command.AbstractQueryCommand.executeQuery(AbstractQueryCommand.java:144)
    ... 4 more
Caused by: net.sf.jsqlparser.JSQLParserException
    at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:57)
    at com.incorta.sqlinterface.parser.Query.parseQuery(Query.java:746)
    ... 8 more
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered " "AS" "AS "" at line 1, column 29.
Was expecting one of:
    "TO" ...
    "NULL" ...
    "CASE" ...
    "INTERVAL" ...
    "GROUP_CONCAT" ...
    <K_TIME_KEY_EXPR> ...
    <S_CHAR_LITERAL> ...
    "=" ...
    "~" ...
    "@" ...
    "!!" ...
    "|/" ...
    "||/" ...
    "{d" ...
    "{t" ...
    "{ts" ...
    "@" ...
   
    at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:22186)
    at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:22047)
    at net.sf.jsqlparser.parser.CCJSqlParser.PrimaryExpression(CCJSqlParser.java:9452)
    at net.sf.jsqlparser.parser.CCJSqlParser.UnaryOperation(CCJSqlParser.java:9026)
    at net.sf.jsqlparser.parser.CCJSqlParser.Exponentiation(CCJSqlParser.java:8942)
    at net.sf.jsqlparser.parser.CCJSqlParser.MultiplicativeExpression(CCJSqlParser.java:8875)
    at net.sf.jsqlparser.parser.CCJSqlParser.AdditiveExpression(CCJSqlParser.java:8812)
    at net.sf.jsqlparser.parser.CCJSqlParser.ConcatExpression(CCJSqlParser.java:8549)
    at net.sf.jsqlparser.parser.CCJSqlParser.BitwiseNot(CCJSqlParser.java:8773)
    at net.sf.jsqlparser.parser.CCJSqlParser.BitwiseOperation(CCJSqlParser.java:8684)
    at net.sf.jsqlparser.parser.CCJSqlParser.SimpleExpression(CCJSqlParser.java:8515)
    at net.sf.jsqlparser.parser.CCJSqlParser.Set(CCJSqlParser.java:530)
    at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:222)
    at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:116)
    at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:55)
    ... 9 more

Last edited by unsi_2 (2021-11-24 16:06:11)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: datasource issue postgres

Hi unsi_2,

jsqlparser ? this seems to not be included in a standard reportserver installation, did you add any libraries / drivers regarding your postgres database ?

Regards,
Eduardo

unsi_2

Member

Registered: 2020-06-24

Re: datasource issue postgres

hi, no, we have not

that part of the error stack is provided by the server response,  the incorta server response when reportserver opens the connection.

it does not seem to support the query that RS adds SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

as i told you there are no problems connecting via jdbc on birt, or via script reports using jdbc in groovy.

but we need to connect also in dynamic lists, and the data source connection fails.

is there any property we could indicate RS not to send that unsolicited query/instruction?

or maybe RS developers could provide another postgres datasource type, one did not add up additional non select queries

kind regards

Last edited by unsi_2 (2021-11-25 09:45:29)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: datasource issue postgres

Hi unsi_2,

what reportserver version exactly are you using and what postgres version?

Regards,
Eduardo

unsi_2

Member

Registered: 2020-06-24

Re: datasource issue postgres

the version is RS3.7.0-6044

and the server to connect uses:
https://docs.incorta.com/5.1/concepts-sqli/

it supports select queries via postgres jdbc

thanks

unsi_2

Member

Registered: 2020-06-24

Re: datasource issue postgres

maybe a datasource for incorta would be added in futures versions of RS? can we ask santa for it?

or maybe there is a way of using the current postgres one adding some kind of hook to avoid non select statements added by the current ?

kind regards

eduardo

Administrator

Registered: 2016-11-01

Website

Re: datasource issue postgres

Hi unsi_2,

pls take a look here:
https://reportserver.net/en/guides/scri … tasources/

You can try to add this datasource type via script. It this works you can send us the script and we will consider integrating this into ReportServer.
Pls. let us now.

As the only issue seem to be the statements added, this should not be very difficult if you base on the Postgres database helper:net.datenwerke.rs.base.service.dbhelper.db.PostgreSQL

https://reportserver.net/api/current/ja … reSQL.html

Regards,
Eduardo

eduardo

Administrator

Registered: 2016-11-01

Website

Re: datasource issue postgres

Hi unsi_2,

if you have any possibility of letting us access your (test)-incorta database (we just need to connect to a simple test table, e.g. "select * from mytable"), we can also take a look at this, as this seems interesting.

Regards,
Eduardo

unsi_2

Member

Registered: 2020-06-24

Re: datasource issue postgres

thanks Eduardo

i think there are available dummy environments in their free online courses: https://learn.incorta.com/

i took a look at the classes net.datenwerke.rs.base.service.dbhelper.db.PostgreSQL but did not found how to avoid this

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

maybe something related to  the line   @Transactional(rollbackOn = { Exception.class }),
in TableReportUtility, datasourcetester and other classes

dont know

regards

Last edited by unsi_2 (2022-03-01 10:17:59)

quintg

Member

Registered: 2016-10-19

Re: datasource issue postgres

Hi, I ran into the same issue and logged it under 2137

Did you manage to get it to work by any means?

Regards Gerard

eduardo

Administrator

Registered: 2016-11-01

Website

Re: datasource issue postgres

Hi Gerard,

the same here:

if you have any possibility of letting us access your (test)-incorta database (we just need to connect to a simple test table, e.g. "select * from mytable"), we can also take a look at this, as this seems interesting.

Regards,
Eduardo

quintg

Member

Registered: 2016-10-19

Re: datasource issue postgres

Hi Eduardo, we can't expose our environments to you. Would you be willing to join a conference call so we can do screen sharing?
Regards, Gerard

eduardo

Administrator

Registered: 2016-11-01

Website

Re: datasource issue postgres

Hi Gerard,

a conference call would not help in this case. For the moment we don't have an incorta database, and in order to (eventually) support it, we would need to test it of course.

Regards,
Eduardo

eduardo

Administrator

Registered: 2016-11-01

Website

Re: datasource issue postgres

Hi Gerard,

we tested ReportServer (4.5.0) with Incorta (in the cloud) and everything works as expected, no error. Maybe the newer Incorta versions allow the same configuration ? Can you pls check ?
Are you using Incorta on the Cloud? Maybe you use an older version.
My connection URL is similar to:
jdbc:postgresql://incorta-4439.sqli.cloud4.incorta.com:9603/default

Anyway, ReportServer 4.6.0 will probably include Incorta support, but as I mentioned, the error you got is not reproducible.

Regards,
Eduardo

eduardo

Administrator

Registered: 2016-11-01

Website

Re: datasource issue postgres

Hi Gerard,

in your error stack I see many entries containing:

net.sf.jsqlparser

This is *not* included in reportserver. You seem to have additional libraries other than the shipped ones. This is probably the cause of the error.
Using the official Postgres driver (shipped with ReportServer) I don't get any errors with Incorta.
Pls double check your additional libraries (in the external config dir) and also in web-inf/lib (these should not be modified!)

Regards,
Eduardo

eduardo

Administrator

Registered: 2016-11-01

Website

Re: datasource issue postgres

Hi Gerard,

ReportServer 4.6.0 adds Incorta support, but as I mentioned, the error you got is not reproducible.

Here the release notes: https://reportserver.net/releasenotes/RS4.6.0.html

Regards,
Eduardo