Convert Firebird database SQL syntax to Postgres database at runtime

Question:

I have a system developed in Delphi 7 that is already stable using the Firebird database, and some customers have requested that they be able to use Postgres as a database on that same system.

As in some SELECT'S, my system uses some Firebird reserved words, I decided to create an intermediate component that at runtime, changes these words, or changes the syntax so that the system continues to work, as it is today with Firebird.

However, I would like to know if there is any framework, component or even DLL that does this. So that I don't have to reinvent the wheel, studying both syntaxes for each and creating checks to do so. Preferably in Delphi 7 (being a DLL, it can be a higher version).

Example:

--FIREBIRD
EXECUTE BLOCK 
AS 
BEGIN 
  IF 
  ( EXISTS 
    ( SELECT 1 FROM TB_FNC_SEC 
       WHERE FD_FNC = 1
         AND FD_SEC = 'LOC'
         AND FD_KEY = 'FD_FNC'
    ) 
  ) THEN 
    UPDATE TB_FNC_SEC SET 
      FD_VAL = '0'
     WHERE FD_FNC = 1
       AND FD_SEC = 'LOC'
       AND FD_KEY = 'FD_FNC';
  ELSE 
     INSERT INTO TB_FNC_SEC ( 
       FD_FNC,FD_SEC,FD_KEY,FD_VAL 
     ) VALUES ( 
      1,'LOC','FD_FNC','0') ;
END 

--POSTGRES
DO
$$ --INICIO
BEGIN 
  IF 
  ( EXISTS 
    ( SELECT 1 FROM TB_FNC_SEC 
       WHERE FD_FNC = 1
         AND FD_SEC = 'LOC'
         AND FD_KEY = 'FD_FNC'
    ) 
  ) THEN 
    UPDATE TB_FNC_SEC SET 
      FD_VAL = '0'
     WHERE FD_FNC = 1
       AND FD_SEC = 'LOC'
       AND FD_KEY = 'FD_FNC';
  ELSE 
     INSERT INTO TB_FNC_SEC ( 
       FD_FNC, FD_SEC, FD_KEY,FD_VAL 
     ) VALUES ( 
      1, 'LOC','FD_FNC','0') ;

$$ 

Thank you in advance for your help.

Answer:

Boy, complicated this situation, because depending on the size of the system you'll have to do a lot, about a third party component or DLL I've never heard about!

So what can we do?

Simple and functional approach, Add Memos or StringList with both codes, and you make the call depending on the Clients in question.

Declare Global Variables in the main DataModule of your application:

_PossuiFirebird e _PossuiPostgres

For each specific customer, set the variables to True .

if (_PossuiFirebird = True) then
begin
  ExecSql(MemoFirebird);
end
else if (_PossuiPostgres = True) then
begin
  ExecSql(MemoPostgres)
end;

With this structure you can also choose not to use Memos but the code blocks themselves using the variables to separate each case.

if (_PossuiFirebird = True) then
begin
  //Execução de Sql Firebird
  //...
  //Execução de Sql Firebird
end
else if (_PossuiPostgres = True) then
begin
  //Execução de Sql Postgres
  //...
  //Execução de Sql Postgres
end;

Sometimes we get to this point and there are challenges, changing Banks or letting customers down, and we agree that the last option is not viable!

Today I have a big application that uses Firebird, and my problem is that some customers use Firebird 2 or lower, others are always up to date! My biggest problem in this case was the Update or Insert which is only supported in the more modern versions, instead of using 1 single procedure avoiding long codes I am forced to keep Update and Insert separate in 2 blocks of code resrsrsrsrs.