CODEWITHSUNDEEP
delphifirebirdfiredacdelphi-10.2-tokyo
Jan Doggen
Jan Doggen

Reputation: 9096

How to query Firebird SQL Dialect

I'm trying to query the SQL dialect of a Firebird database (using the embedded driver):

procedure TFrmFireDACEmbed.BtnGetDBDialectClick(Sender: TObject);
var
   lFDConnection : TFDConnection;
   lDriverLink   : TFDPhysFBDriverLink;
   l             : Integer;
begin
   if not DlgOpen.Execute then Exit;

   lDriverLink   := TFDPhysFBDriverLink.Create(nil);
   lFDConnection := TFDConnection.Create(nil);
   try
      lDriverLink.DriverID := 'FBEmbedded';
      lDriverLink.VendorLib := 'fbembed.dll';  // 32-bits embedded
      lFDConnection.DriverName := S_FD_FBId;
      lFDConnection.Params.Database := DlgOpen.FileName;
      lFDConnection.Params.Add('Server=127.0.0.1');
      lFDConnection.Params.UserName := 'SYSDBA';
      lFDConnection.Params.Password := 'masterkey';
      lFDConnection.LoginPrompt := False;
      lFDConnection.Open;
      l := lFDConnection.Params.IndexOf('SQLDialect');
      if l <> -1 then
         ShowMessage(lFDConnection.Params[l])
      else
         ShowMessage('SQLDialect not found');
   finally
      lFDConnection.Close;
      lFDConnection.Free;
      lDriverLink.Free;
   end;
end;

But the lFDConnection.Params only contains DriverID, Database, Server, User_Name, Password.

The inspector shows:

(nil, $2F22820, #$D#$A, nil, 0, ',', '"', '=', [soWriteBOM,soTrailingLineBreak,soUseLocale], (('DriverID=FB', nil), ('Database=D:\Temp\KLANTEN.GDB', nil), ('Server=127.0.0.1', nil), ('User_Name=SYSDBA', nil), ('Password=masterkey', nil), ('', nil), ('', nil), ('', nil)), 5, 8, False, dupIgnore, False, (FireDAC.Stan.Def.TFDDefinition.ParamsChanged,$2F5C4F0), (FireDAC.Stan.Def.TFDDefinition.ParamsChanging,$2F5C4F0), False, TFDConnectionDef($2F5C534) as IFDStanDefinition)

And lFDConnection.Params.SQLDialect is not recognized by the compiler.

Digging though the system tables I found that for a dialect 3 db the query

select mon$sql_dialect from mon$database

will return 3, but for an older version mon$database does not exist.

How can I retrieve the SQL dialect for any dialect?

The intention is to rewrite old code that used 'under the hood' functions like isc_attach_database, isc_database_info (which had to be dynamically linked, GetProcAddress, etc).

Upvotes: 4

Views: 2382

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 108941

I'm not familiar with Delphi, so my answer is from a generic Firebird perspective.

The MON$DATABASE table exists both in dialect 1 and dialect 3 databases, provided that database is at least ODS 11.1 (Firebird 2.1, which introduced the monitoring pseudo-tables). If you don't have a MON$DATABASE table then the database is ODS 10.0 (InterBase 6/Firebird 1.x) or ODS 11.0 (Firebird 2.0).

In other words, if you don't have a MON$DATABASE, you could backup and restore the database to upgrade it and then check MON$DATABASE.

Otherwise, there is a trick you can use to determine if a database is dialect 1 or dialect 3. The trick is to execute the following query:

select 1/2 from rdb$database

If the result is 0 (integer), then your database is dialect 3, if the result is 0.500000 (double precision), the database is dialect 1. In theory preparing the query should be enough, the value would be described as DOUBLE PRECISION in dialect 1 and as BIGINT (or DECIMAL(18,0)) in dialect 3.

This is because this is one of the differences between dialect 1 and dialect 3: integer division in dialect 1 is done as floating point division.

Be aware: it is possible to explicitly set a connection dialect and override the default of using the database dialect. This should produce a warning on connect, but not all drivers/components communicate that warning.

Alternatively, you could query for isc_info_db_sql_dialect using isc_database_info (or check if your component has something that does this for you, see the answer by Victoria), or use gstat -h to check the database header page directly.

Upvotes: 2

Victoria
Victoria

Reputation: 7912

How to get SQL dialect of the connected Firebird database?

You can replace your legacy code by something like this:

uses
  FireDAC.Phys.IBWrapper;

procedure TForm1.Button1Click(Sender: TObject);
var
  SQLDialect: Integer;
  IBDatabase: TIBDatabase;
begin
  IBDatabase := TObject(FDConnection1.CliObj) as TIBDatabase;
  SQLDialect := IBDatabase.db_sql_dialect;
end;

How to specify SQL dialect for Firebird database connection at runtime?

You can specify connection definition parameter for SQL dialect by:

FDConnection1.Params.Add('SQLDialect=1');

Or by typecasting to specific Firebird DBMS connection definition class like this:

TFDPhysFBConnectionDefParams(FDConnection1.Params).SQLDialect := 1;

Upvotes: 2

Related Questions