Convert SQL syntax from Firebird database to Postgres database at runtime

Question

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

As with some SELECT’S, my system uses some reserved Firebird words, I decided to create an intermediate component that at runtime, change these words, or change 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 need to reinvent the wheel, studying both syntax of each and creating checks for it. Preferably in Delphi 7 (being DLL, may be 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') ;

$$ 

Thanks in advance for the help.

Answer 1

Boy complicated this situation because depending on the size of the system you will have to do a lot, about a third component or DLL ever 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 Customers in question.

Declare Global Variables in the Main Datamodule of your application:

_PossuiFirebird e _PossuiPostgres

For each of your clients in specific define the variables as 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 that same point and comes the challenges, change Bank or leave customers in hand, and we agree that last option is unfeasible!

Today I have a great application that uses Firebird, and my problem is that some customers use Firebird 2 or lower still, already others are always updated! My biggest problem in this case was the Update or Insert which is supported only in the more modern versions, instead of using 1 single procedure avoiding long codes I am obliged to keep Update and Insert separated into 2 blocks of resrsrsrs codes.

Answer 2

I’m putting as an answer because I don’t have enough reputation to comment hehehe put to Victor’s question:

"In the WHERE clause of the Firebird UPDATE you have AND FD_SEC = 'LOC' and in Postgre you have AND FD_SEC = 'LOCALIZA'. Is this difference intentional? If so, why is one LOC and the other LOCALIZA? - Victor Stafusa 24/11 at 20:13 "

Haste in typing would not be the correct answer, in the 80’s was created the SQL Padra-o by ANSI and ISO, a very interesting attitude, to prevent the developers of DBMS did not create their own sql languages as Oracle was developing, there have been several patterns but each DBMS ends up using a pattern different from the other, but there is nothing that obliges them to use the same or the most current pattern.

but to solve your problem Junior’s answer is the best way !!