Jaspersoft studio throws error while running SQL statements against SAAS systems

Products

• Clarity PPM SaaS
• Clarity 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:

1. 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
   
2. In the JasperSoft studio, create a blank report and then click on the Dataset and Query editor icon
   
3. 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: