Report migration to Postgre from Oracle

[popkorn]

Hi,

 

We are currently having jasper server deployed (with mysql as the repository DB) in production.

Currently we have written SQL queries inside the Jasper report (I mean in jrxml inside jasper studio)

Currently we have connected jasper reports to Oracle Autonomous database to generate reports fetching data from. Next step is we are going to replace Oracle DB with Postgres as our transaction database.

So we want to change the queries inside the Jasper reports to suit them for Postgres and since there are 300+reports, this has become a tedious task. We have identified some keywords like DECODE should be changed

If you have come across such a situation, can you please provide a way to convert them to run compatible to Postgres.

 

Thanks in advance.

 

 

 

Edited June 11 by popkorn

[davidgarratt]

On 6/11/2024 at 6:19 AM, popkorn said:

Hi,

 

We are currently having jasper server deployed (with mysql as the repository DB) in production.

Currently we have written SQL queries inside the Jasper report (I mean in jrxml inside jasper studio)

Currently we have connected jasper reports to Oracle Autonomous database to generate reports fetching data from. Next step is we are going to replace Oracle DB with Postgres as our transaction database.

So we want to change the queries inside the Jasper reports to suit them for Postgres and since there are 300+reports, this has become a tedious task. We have identified some keywords like DECODE should be changed

If you have come across such a situation, can you please provide a way to convert them to run compatible to Postgres.

 

Thanks in advance.

 

 

 

I don't have an answer for you - but I can share my experience. My application has been written to work with mySQL, SQL Server and Oracle and I use the same reports across all platforms.

Now - I do develop against mySQL and my reports do include sql for that platform, however when I run my reports I'm passing resultset to the report rather than using a hard coded query with parameters.

This works for me because my application handles the differences between SQL dialects outside the report and as long as the result contains the same column names and types then the report is blissfully unaware of the database engine.

Now - having said that - there are instances where I have to manually change the data type in the report design as sometimes it will set a timestamp field to date local - or some phrase like that. 

My application stores all the sql commands in a xml file for each platform and I have some libraries which deal with syntax differences like top, limit, rownum etc

Hope this helps.

 

Dave