Delphi Interbase Sql Conversion to Sql Server and Oracle

Question

hello, i have a delphi application which uses database interbase / firebird. To consult and write data I use the InterBase components palette (IBTable, IBQuery, IBDataset). I'm performing the conversion of my system to sqlserver / Oracle but i have thousands of queries that are assembled at runtime with SQL Instructions Specific of database InterBase/Firebird. Anyone know any component or tool that makes Parse commands Interbase -> SQL Server or Interbase-> Oracle ?

what i need its something like:

Var
  Parser: TParser;
  OutputSql: String;
Begin
   Parser := TParser.Create();
   Parser.Text := 'SELECT FIRST 10 CITYNAME FROM TBCITY';

   if Firebird then
      OutPutSql := Parser.ParseTo('SQLSERVER');

   if Oracle then
      OutPutSql := Parser.ParseTo('ORACLE');

   ComponentAccess.Sql.Text := OutPutSql;
   ...

The Result Of:

Parser.ParseTo('SQLSERVER');

Will Be

'SELECT TOP 10 CITYNAME FROM TBCITY'

And

Parser.ParseTo('ORACLE');

Will Be

'SELECT CITYNAME FROM TBCITY WHERE ROWNUM <= 10'

Answer 1

1) AFAIK, libraries like AnyDAC, have SQL abstraction syntax. May be you can use this feature in your SQL command text.

2) If you are assembling your SQL at runtime, then why not just code like that:

if Firebird then
  SQL.Add(...)
else if Oracle then
  SQL.Add(...)
...

Answer 2

I have used kbmMW from Components4Developers and it has an abstracted set of queries that provide macros and the like to enable /easier/ cross database work but it's mainly for client/server use. Devart also do a good set of cross database components - we use their SQL Server set. However, each project I've done I've ended up writing a specific set of SQL scripts for each database. Obviously there is a fair bit of common ground for simple select stuff but the feature sets of the different databases are often too different to make it easily workable.

I end up with something similar to @oodesigner's response except that we use $ifdef and define my SQL strings in a separate const unit.

{$ifdef USE_MSSQL}
  QUERY_ONE = 'select blah blah blah...';
{$else}
  QUERY_ONE = 'select nah nah nah...';
{$endif}

Then in the main unit a simple assignment

SQL.Text := QUERY_ONE;

or

SQL.Text := Format(QUERY_TWO, [some_very_carefully_quoted_stuff_or_use_params]);

Don't know of anything that would automate or parse it. And the problem with that is that you still have to go through and check every single query because it's too easy to get things wrong when converting.

Answer 3

We have implemented that in AnyDAC. You may use LIMIT escape function:

ADQuery1.Sql.Text := 'SELECT {LIMIT(10)} CITYNAME FROM TBCITY';

AnyDAC will automatically translate that into target DBMS syntax.