Jaspersoft studio throws error while running SQL statements against SAAS systems
book
Article ID: 266385
calendar_today
Updated On:
Products
Clarity PPM SaaSClarity PPM On Premise
Issue/Introduction
While connecting to the Clarity PPM database using JDBC driver - postgresql-42.2.6.jar, running SQL statements like: SELECT * FROM INV_INVESTMENTS are failing over. This is occurring in both cases, while running SQL queries using JasperSoft Studio as well as from a SQL editor, that is using this JDBC adapter for connection.
SQL statement like, SELECT * FROM <table> are failing on any table that has a SQL curve.
Steps to Reproduce:
Download and install JasperSoft Studio version 7.9. Create a connection to Clarity PPM Database using the JDBC adapters, ensure that the JDBC adapter under the driver classpath are set as follow
In the JasperSoft studio, create a blank report and then click on the Dataset and Query editor icon
In the resulting Dataset and Query Dialog box, input the following statement: SELECT * FROM INV_INVESTMENTS and then click on Read Fields button:
Expected Results:
The above query should execute and all the columns from the INV_INVESTMENTS table should be listed
Actual Results:
The query does not execute and the following error is being observed:
Environment
Release : This is being observed in Clarity version: 16.1.1.1485 00 01 12 and JasperSoft Studio version 7.9, using the JDBC driver: postgresql-42.2.6.jar
Cause
This issue is occurring when the following JDBC Driver: postgresql-42.2.6.jar , is being used.
Resolution
Note: This issue does not occur for all tables, it is mostly observed for tables which has a SQL curve data type.
The observed issue is identified with the postgresql driver, following are the recommended steps:
Attached, is the postgresql driver version 42.6.0. Download this provided postgresql driver.
From the SQL editor client / JasperSoft Studio, remove any existing configured postgresql driver. Reference in the provided postgresql driver 42.6.0 and then restart the SQL editor client / JasperSoft Studio
Reference: For example, referring to JasperSoft Studio, the Drivers under the classpath, should be as follows: