Convert the SQL syntax of the Firebird database to Postgres database at runtime

Question

Convert the SQL syntax of the Firebird database to Postgres database at runtime

Navigation

#1 by (2 votes)
#2 by (-1 votes)

3

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

As in some SELECT'S, my system uses some reserved Firebird words, I decided to create an intermediate component that at run time, change these words, or change the syntax so the system continues to function, 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 I do not have to reinvent the wheel, studying both syntaxes of each one and creating checks for it. Preferably in Delphi 7 (being DLL, it may be higher).

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 for the help right away.

sql delphi postgresql firebird

asked by anonymous 24.11.2015 / 21:08

2 answers

Help with onitemclick listview JTree: Why does it lose focus when I edit a node?

score 2 · 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 never heard of!

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 DataModule of your application:

_PossuiFirebird e _PossuiPostgres

For each client of yours, 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 rather the 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 come to this point and come the challenges, change the Bank or leave the clients in hand, and we agree that last option is impractical!

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

score -1 · Answer 2

I'm putting in the answer because I do not have enough reputation for comment. But I have to ask Victor's question:

"In the WHERE clause of firebird UPDATE has AND FD_SEC = 'LOC' and in Postgre has AND FD_SEC = 'LOCALIZA'. / 11 at 8:13 p.m. "

Speed typing would not be the correct answer, in the 1980s the SQL standard was created by ANSI and ISO, a very interesting attitude, to prevent DBMS developers from creating their own sql languages as Oracle was developing, there arose several standards but each DBMS ends up using a different pattern from the other, but there is nothing that forces them to use the same standard or the most current one.

But to solve your problem, Junior's answer is the best way !!