#1 2021-11-23 11:26:37

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

Offline

#2 2021-11-24 14:05:24

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

Offline

#3 2021-11-24 15:46:48

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)

Offline

#4 2021-11-25 08:15:19

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

Offline

#5 2021-11-25 09:30:01

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)

Offline

#6 2021-11-25 09:42:23

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

Offline

#7 2021-11-25 09:55:06

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

Offline

Board footer

Powered by FluxBB