Database Portability

[Former Member]

How to port reports across multiple RDBMS -- we develop products which have to deployed with various databases (Oracle, SQL Server, etc.). We may not afford to develop and maintain multiple copies of same reports b/c of the SQL differences (concatenation operator, substring, string length, etc.). What is the solution?

[Former Member]

There could be a couple of solutions but in your case, I think the best solution might be to use JavaBeans for the report. With this solution, you control the code in the JavaBean so you can connect to any datasource you need to support and query for the data your need and generate a ResultSet that can be read by the report.

-MJ

[Former Member]

Sure this is possible but it will force us to add a java programmer in the normal report design process where report experts (Report Design/Query Writing Experts) develop and test reports. This will increase the cost.

There is another maintenance issue, whenever there is a change in the report,designers will change and test the report in designer and the programmer will have to change the query in his bean accordingly. This will introduce additional testing and a risk of error if the programmer made a mistake in doing the same change in his version.

We needed the SQL portability in the same physical RPT with the same query that is used to test the report.

THANKS GOD WE FOUND A SOLUTION THROUGH A LESS EXPOSED FEATURE OF CRYSTAL -- that is SQL Expression (even without EXpression Builder). One may write syntax like fn{CONCAT(..., ...)} and the runtime component will replace this code piece with the SQL expression suitable to the underlying database at runtime. This is a superb less documented features. I found no documentation to tell me that the translation is done at runtime, instead a misleading statement in the documentation and forums tells that you must need the underlying database functions to use this feature.

Shahid

Manager IS

Sidat Hyder Morshed Associates Ltd. (Formerly Member of E&Y)