How to open Firebird 3 Database in FireDAC Delphi 11 (64Bit)

[Blavatsky 0]

Hello,
Can somebody please tell me
1) How to open Firebird 3 database in FireDAC Delphi 11 (eg Database.name "FB3TRANSLT.FDB" >>table.name "Dictionary" ?

2) what should the FDQuery.Strings property look like? Select*From "Dictionary" does not work apparently like it did for my Paradox 7project.

cheers
thanks in advance

if I put this in FDQuery.SQL.Strings

SELECT
  "Index2",
  "French",
  "FrenchUTF",
  "FrenchMod",
  "English",
  "Latin",
  "Greek",
  "Spanish",
  "German",
  "Chinese",
  "Arabic",
  "Russian",
  "Italian",
  "Pasigraphie",
  "PasiImage",
  "Notes"
 FROM "dictionary"
 WHERE "Index2" = :INDEX2

I found a TIP
Datamodule must be formed before MainForm
goto Project>>>view Source

 Application.CreateForm(TDataModule1, DataModule1); {DM1 created FIRST}
  Application.CreateForm(TForm1, Form1);   {MainForm created after DM1}

Can someone help implement the correct changes and tell me where to add the changes ....
kind regards
in advance

Edited August 15, 2023 by Blavatsky

[Blavatsky 0]

I found this today which looks helpful

https://soonsantos.medium.com/connect-fdquery-to-dbgrid-programatically-delphi-4aab394c6d51

[Stano 140]

You have all this on the website I gave you a link to. (https://docwiki.embarcadero.com) You just have to do a little searching. You'll have to dig around in the FireDAC section.  Those " don't have anything to do there.

[Serge_G 85]

1) Drop a FDConnection  and set properties first one should be FBDriver 

short response

long response

eventually, you can change connection properties at runtime .

As sample here what I use to connect my database 

With ConnexionBase.Params as TFDPhysFBConnectionDefParams do
     begin
        UserName:=parametres.utilisateur;
        Password:=parametres.motdepasse;
        Server:=IP; // Ip addresse 
        Database:=Parametres.nombase;
        SQLDialect:=3;
     end;
  try
    ConnexionBase.Connected:=True;
    result:=true;
  except
    result:=false;
  end;

 

 

2) Don't use " "  except if you need case-sensitive column names.

 Avoid * in your SQL. (If you use Flamerobin it's easy to generate SQL text)

 

your SQL should be
 

SELECT Index2,French,FrenchUTF,FrenchMod,English,Latin,Greek,Spanish,German,Chinese,Arabic,Russian,Italian,Pasigraphie,PasiImage,Notes
 FROM dictionary
 WHERE Index2 = :INDEX2 

Note if you have this FDQuery you can code this 
 

FDQuery1.open('',[valueof parameter]);   

instead of

FDQUery1.Close;
// no need to change SQL Text every time
FDquery1.ParamByName('Index2').asInteger:=xxx  // value of parameter
fdquery1.Open;

 

A tip : set  FdConnection.ConnectedStrorageUsage.auRuntime to false  

 

 

[Stano 140]

5 minutes ago, Serge_G said:

FDQUery1.Close;
// no need to change SQL Text every time
FDquery1.ParamByName('Index2').asInteger:=xxx  // value of parameter
fdquery1.Open;

Only:

FDquery1.ParamByName('Index2').asInteger:=xxx // value of parameter

fdquery1.Refresh;

 

Don't forget to study the transactions. It's essential!

Edited August 15, 2023 by Stano

[Serge_G 85]

I am not a Refresh fan

[Stano 140]

It's not off the top of my head. It saves resources and time. It works perfectly for me. I use it just in this case.

[Blavatsky 0]

Thanks Serge_G
Posting in French creates more problems for me. French Delphi vs English Delphi Software mismatch.
Thanks for your kind help though.
I notice when looking at the Params for FDConnection that while my project is 64 Bit the FireDAC platform is 32 Bit ( Strange that the Embarcadero people did not fix this in Delphi11.1 ).
This kind of mismatch alone is a nightmare IMHO.
Maybe it means my Version of Delphi 11.1 is a DUDD when it comes to Firebird 64 Bit. ???
As for some of the other params. I will need english equivalents eg.
what is english version of :-

UserName:=parametres.utilisateur; ? params.user ?
Password:=parametres.motdepasse ? params.password ?
Server:=IP; // Ip addresse I know that one
Database:=Parametres.nombase; ? params.databasename ?
I could guess but as a newbie I would need exact param names .....
What are the params for ? FDConnection, TFDPhysFBDriverLink1

 where does this code go >>> ? as an event ?, as a procedure ?
 
 is this a good replica of what you are asking me to do in an English version of Delphi 11.1 ?
....
  with DataModule1.FDac_Con1.Params as TFDPhysFBDriverLink1
      begin
      Database=FB3_TRANSLIT.FDB
      User_Name=SYSDBA
      Password=masterkey
      Server:=IP; // IP address
      CharacterSet=UTF8
      DriverID=FB
      SQLDialect:=3;
    end;
      try
       Datamodule1.FDac_Con1.Connected:=True;
      result:=true;
    except
    result:=false;
  end;

###############################################
Forgive my ignorance but I do not know what "value of parameter" means ?
nor do I know the significance of  "xxx"  ?

It sounds simple, but, I have never come across it before.

FDQUery1.Close;
// no need to change SQL Text every time
FDquery1.ParamByName('Index2').asInteger:=xxx  // value of parameter
fdquery1.Open;

Edited August 15, 2023 by Blavatsky

[Blavatsky 0]

Hi again,
Serge_G I tried those settings in the FireDAC Connections settings editor and it does not work for my 64 Bit VCL APP.
Error
[FireDAC][Phys][FB]l/O error during "CreateFile (open)" operation for file "FB3_TRANSLIT.FDB" Error while trying to open file
The system cannot find the file specified..
I am guessing your APP is 32 Bit.. Am I right ?

Edited August 15, 2023 by Blavatsky

[Stano 140]

No!

This means that you have the wrong DB path. Or name?

[Roger Cigol 88]

The use of a 64 bit application talking to a 64 bit database is the one time I've found where the IDE being a 32bit app is a real pain. What it means is you can't use the same database settings for design time as you use for run time (and doing so is a really convenient feature when you develop 32 bit apps). 

I work on a large PostgreSQL project and PostgreSQL is only available as a 64bit database (and has been so for a long while - you have to go back several versions to get a 32 bit version). I just accept that I can't use the design time connectivity of FireDAC and just do all my debugging at runtime. It's not so convenient but I've got used to it!.

Be sympathetic to the Embarcadero Team - changing the IDE to 64bit must happen but they need to make sure that there are 64bit versions (or equivalents ) for all the sub components (which come from many many places : just look at the IDE menu item: Help | About | Acknowledgements). It's not quite as straightforward job as you think. You also have the issue that it "breaks" all the design time features for the folk out there using FireDAC with 32 bit apps talking to 32 bit databases. These may well be used to seeing all the design time connection functionality.

[Stano 140]

Do you have any particular reason for a 64 bit application? Even the 32 bit app works with 64 bit FB!

[Blavatsky 0]

THANKS Roger and Stano,
I do agree there are some nightmares for 64bit users mainly DB users.
THE ODBC for 64bit DB cannot be accessed from FIREDAC in Delphi 11.1 ONLY the 32 bit version is called.
So, giving ODBC as an option for 64  bit platform is like dangling a carrot in front of a donkey, sorry, no wish to offend.
It would be nice if Embarcadero gave the programmer a choice of 32 bit FIREDAC or 64 bit FIREDAC.


All iPhones, androids, iPads, iWatches use 64bit. So, to answer your question STAN, the envoronment for FMX 64 bit needs FireDAC 64 bit, but, I think you already knew that.
Patience is a virtue. If I am given any powers in the future to help make these sorts of corrections then rest assured 64Bit FireDAC will at the TOP OF THE LIST.

[Serge_G 85]

My desktop FMX apps are 32/64 bits windows and Linux, my firebirds install are 64 bits (default FB3 on port 3050, FB4 on 3054)

And I don't have any problem with that.
Even if I can also read server FB Database on Android, I tried to have full a Firebird  embedded on Android without success.

18 hours ago, Blavatsky said:

where does this code go >>> ? as an event ?, as a procedure ?

This code is extracted from a procedure in my Datamodule

 

18 hours ago, Blavatsky said:

is this a good replica of what you are asking me to do in an English version of Delphi 11.1 ?

Yes

 

18 hours ago, Blavatsky said:

Maybe it means my Version of Delphi 11.1 is a DUDD when it comes to Firebird 64 Bit. ???

No

[Blavatsky 0]

Well,
Some good news.

I had some help and now I have two projects up and running in 64-bit on Win-10 (64-bit) computer.
1) SQLite3
which was pretty simple to set up.
and,
2) My Dictionary Firebird3 project is now running with DATA populating the BDGrid.

I have open and close BitBtns for the FDConnection and FDQuery.

Another useful thing/s I discovered
A) in the Project file make sure the Datamodule statement is before the form statement
B) When fiddling around with ODBC you need to make TWO Firebird3 USER DSN's with similar names, just have one end with 32 and the other end with 64 or something to tell them apart. This means you have to make a 32-Bit Firebird User DSN in the 32-Bit ODBC editor ( type ODBC in the Windows RUN window and it should appear). Then, do the same in the 64-Bit ODBC Editor for a Firebird ODBC Driver.
So now , when 32-bit FireDAC calls the 32-bit ODBC Editor for FDConnection, BOTH drivers, 32-bit and 64-bit Firebird drivers can be seen and accessed.
Got this from Devart website.

 

Edited August 21, 2023 by Blavatsky

[Serge_G 85]

But why did you use ODBC when you can use the Firedac Driver ?