Question Re: Migration from Informix to PostgreSQL in Technology Q&A

Migration from Informix to PostgreSQL

former_member733560 — Sun, 24 Jul 2022 17:16:28 GMT

RPT designed for Informix does not work with PostgreSQL.

Detail:

I am using Crystal 2020 to design RPT files. Initially RPTs were designed for Informix database and linked Informix stored procedure into the report.

Then we wrote a C# application to load RPT files and dynamically loaded Informix ODBC Data Source to print the report. Report printed successfully and no issue at all with Informix. Similarly we have designed 500 reports and all were working perfectly with Informix ODBC driver data source.

Now the requirement is changed and customer want to use PostgreSQL database. We used same RPTs designed for Informix and provided PostgreSQL ODBC data source in C# application and then tried to print reports but all the reports are empty. My understanding is that the Crystal report is database independent and same RPT file will work with all databases if we dynamically providing/loading ODBC data source (DSN).

I tried to figure out the root cause and opened RPT in Crystal 2020 and linked similar function from PostgreSQL. Now I noticed that the returned fields name from stored procedure does not have any suffix ":n" while ":n" suffix is added to the field names returned by the Informix's stored procedures. And I think Crystal RPT expect exact same field name with suffix (:1,:2,:3..) in report to display because same fields are used in report to display. To make Informix's RPT work with PostgreSQL I have to manually replace all field name in RPT with new one (like ddr_key1:1 to ddr_key1 and batch_key1:1 to batch_key1)

Attaching Screenshot:

Please help me to design a universal RPT which could work with all Data Bases or could print crystal report after dynamically loading any ODBC data Source from different database. I am not sure if that possible with Crystal Report.

So the point is I don't want to redesign all 500+ reports again or replace field name everywhere in each and every RPT files which were designed for Informix and were working perfectly with Informix.

I want to design single RPT file that could work with all databases without any major changes or redesigning RPTs.

Re: Migration from Informix to PostgreSQL

ido_millet — Sun, 24 Jul 2022 17:59:59 GMT

Please create a PostgreSQL ODBC DSN with a different name.
Open the report in Crystal Designer and use
Database, Set Datasource Location...
to change the data source to the new DSN.

If that manual process fixes the issue, there are ways to automate it.

Re: Migration from Informix to PostgreSQL

former_member733560 — Sun, 24 Jul 2022 22:14:00 GMT

I have already tried that and that worked after making changes in RPT file. For that I would need to redesign all 500+ RPTs for PostgreSQL that's what I don't want (I don't know how to automate that).

So the point is I don't want to redesign all 500+ reports again which were already designed for Informix and were working perfectly with Informix.

I want to design single RPT file that could work with all databases without major changes or redesigning RPTs.

Re: Migration from Informix to PostgreSQL

ido_millet — Sun, 24 Jul 2022 22:46:39 GMT

There are 3rd-party utilities that can mass-update the data source, so you don't have to repeat this manual procedure across hundreds of reports.

Ken Hamady maintains a list of 3rd-party Crystal Reports Management Utilities here.

Here is a video demo of how my Visual CUT software mass updates the data source of multiple reports.

Re: Migration from Informix to PostgreSQL

former_member733560 — Sun, 24 Jul 2022 23:58:08 GMT

Providing data source is not an issue. I am providing that dynamically through C# code using class "CrystalDecisions.Shared.ConnectionInfo" and able to connect to both Informix or PostgreSQL.

But the point is, the field returned by stored procedure is used in formulas and in reports. Those field name are different in Informix (with suffix ":n") and PostgreSQL and I think that's why crystal failed to populates these field values for PostgreSQL when use the same RPT designed for Informix.

To make Informix's RPT work with PostgreSQL I have to replace all return fields like ddr_key1:1 => ddr_key1 and batch_key1:2 => batch_key1.

Re: Migration from Informix to PostgreSQL

former_member11696 — Mon, 25 Jul 2022 15:04:53 GMT

You should be able to using ReplaceConnection() API if Ido's solution does not work for you.

Use Google and search for it, there are KBA's on how to use it.

Re: Migration from Informix to PostgreSQL

former_member11696 — Wed, 10 Aug 2022 14:56:51 GMT

What happens if you replace the connection in CR Designer?

If it works without the Field Mapping UI popup then ReplaceConnection should work for you.

If the Field MApping UI pop's up then only option is to fix each report manually.