Database Portability

[Former Member]

Post Author: shahid

CA Forum: Data Connectivity and SQL

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?

Shahid Siddiqui

Manager ISSidat Hyder Morshed Associates (Formerly E&Y Pakistan)

[Former Member]

Post Author: shahid

CA Forum: Data Connectivity and SQL

I found a very clean solution for that through SQL Expression: The SQL Expression already has the capability of creating the correct function according to the current RDBMS at runtime. This is a simple, superb solution for porting SQL containing expressions that are incompatible (concatenation, LENGTH , LTRIM, RTRIM, SUBSTRING functions). I talked about this and Crystal has done that already -

---

even in the RPTS created through SQL query (instead of the adding tables and fields from the designer) may exploit this feature even they don't have the SQL Expression node.Thanks to all who gave their time to help us.....

[Former Member]

Post Author: shahid

CA Forum: Data Connectivity and SQL

I'm sorry if any of my comments have hurt anyone, the sentences were not meant to hurt someone but i think i didn't show care while writing those lines. Everyone is respectable no matter she is a junior report writer or a senior consultant like you. I wanted to tell that we may not write reports by programming, instead we want it to be done through Crystal. I do have a strong background in report writing/SQL myself but since i'm new to Crystal world -- it is always better to take opinions from others at some place new for you. Since their may be something provided by the tool or some good practice they might knew.Yes views are an alternate but it has an overhead when at runtime the RDBMS prepares the whole view in memory (until the view is materialized or covered by a large index).Besides for simple things like a concatenation of two columns, it may be an overkill to use a view. This may require hundreds of views for large RDBMS found in our large financial applications. Stored procedures may be a better alternatives specially because it takes only the data filtered by the parameters. But there is also a shortcoming (that would be in views as well) since we have to support multiple RDBMS it also needs to keep maintaining multiple copies of stored procedures. If during some modification, someone forgot to maintain any of this copy, it will be another problem. There is also other alternatives through database that i will sure try. I mentioned that if Crystal wanted it could introduce Expression Builder for database protability -- if it could generate the sql for expression fields at runtime (when it knows what is the current database).

[Former Member]

Post Author: synapsevampire

CA Forum: Data Connectivity and SQL

I guess you get what you pay for. I command a higher rate than the majority of Java coders (though I did some Javva coding in the past).

So as a high paid Java coder, why are you asking a lowly Crystal forum for advice on how to implement this?

If you'd truly like to keep costs low, leverage higher paid database developers (of which I am one), who also understand Crystal very well, preferably with rudimentary application development experience (after all, the language is unimportant, it's all just screen hanging...).

A series of well designed reusable Vews will allow you to make changes that impact numerous reports without having to embed the SQL in the client side (such as using the Crystal GUI, or by using command objects, or creating SQL within the Java front end).

A competent database developer might address this nicely for you, dear.

btw, SQL Expressions are for creating fields which are embedded within the existing SQL which is generated by using the Crystal GUI to ad tables. You might be thinking of passing the entire SQL to the GUI, or passing a Command Object, either of which can work.

-k

[Former Member]

Post Author: shahid

CA Forum: Data Connectivity and SQL

Would some friends comments on my solutions?May SQL Expression Builder do the job? Please note that we will need to save RPTs without database/connection info to keep it portable.

[Former Member]

Post Author: shahid

CA Forum: Data Connectivity and SQL

Dear Kai MolvigI'm too thankful for your prompt responses and care - it amuses me how helpful people may be!

[Former Member]

Post Author: shahid

CA Forum: Data Connectivity and SQL

Sure dear, it is a generic problem but i wanted to know whether Crystal has provided some solution for that OR the rich user community has devised some pattern for that. Crystal may provide a very clean solution for that through their expression builder and generating final SQL at runtime.Anyway, i have found two ways for that (Please remember we may not afford to involve java/.Net programmers for every report -- it has a cost factor so reports will be designed through Crystal GUI by low paid Report Developers not programmers) -- Either find some third party tool for database migration (i have heard that .rptINSPECTOR by Software Forces claims to do that) or call the reports through a custom report viewer (JSP or ASPX web page) that may get the SQL query of the RPT at runtime, parse it and modify it according to the currently connected RDBMS. How about it?

[Former Member]

Post Author: synapsevampire

CA Forum: Data Connectivity and SQL

Consider generating the recordset within the front end, and passing it to the report.

That way you can code based on the backend.

Your annoyance is a common one, and there is no clean way around this within Crystal itself, much as there isn't within code.

You can use the Crustal GUI to generate the report (SQL) and then issue a Database->Verify Database when you change back ends.

If you wrote an app to do this you'd have to alter the SQL based on the back end as well, it's just a generic problem and not related to Crystal alone..

-k

[Former Member]

Post Author: shahid

CA Forum: Data Connectivity and SQL

It's simple: i'm developing reports with Oracle backend, one of my WHERE conditions needs concatenation, i use Oracle || operator for concatenation. Reports are running fine. Now, i want to run the same report with SQL server, the || operator will fail, rather it will require + operator. This is just an example -- what the summary is -- there are a few unavoidable SQL functions/Operators that are non-standard and every RDBMS has its own version. What is the best way to tackle this problem. If some friend says use standard SQL, its ok but it may not be done always, sometimes you have to use something that is not available in standard, if some friend says use multiple copies of the RPTs for multiple RDBMS, it will decrease the productivity and there always be a high risk that when there is some change to the RPT, the developer may forget to make that change in every copy. And the list goes on. I have found a few ways but I want hear from you people as i am new to Crystal world.The product being used may not be critical but i'm mentioning it here: Crystal Report Developer Version XI release 2. We are running reports from java and .Net applications.Shahid Manager IS Sidat Hyder Morshed Pakistan/UAE (Formerly E&Y Pakistan)

[Former Member]

Post Author: synapsevampire

CA Forum: Data Connectivity and SQL

Try posting your Crystal version and edition, and how you are executing the reports (from a programming language, or?).

-k