How to query Firebird SQL Dialect

Question

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).

Not 100% sure if this can be checked through SQL, but I don't have access to a Firebird install right now. As far as I know `MON$DATABASE` also exists in dialect 1 databases, if it doesn't this would indicate your database has ODS 10 or 11.0 (`MON$DATABASE` was introduced in ODS 11.1) — Mark Rotteveel, Mar 21 '18 at 11:08
@MarkRotteveel That could be my way out. The old code does an ODS version check, if this is lower than 112 a whole new (sql dialect 3) database is created and all data copied into it. — Jan Doggen, Mar 21 '18 at 11:15

Victoria · Answer 1 · 2018-03-21T20:02:55.490

2

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;

edited Mar 21 '18 at 20:02

answered Mar 21 '18 at 14:12

Victoria

7,822
2
21
44

Are you sure this is the **database** dialect and not the **connection** dialect? – Mark Rotteveel Mar 21 '18 at 14:38
@Mark, `db_sql_dialect` queries `isc_info_db_sql_dialect` on behind. – Victoria Mar 21 '18 at 14:53
Ok, but I guess `FDConnection1.Params.Add('SQLDialect=1');` will set the connection dialect. – Mark Rotteveel Mar 21 '18 at 14:57
@Mark, that's what I'm saying _"To specify it in connection definition"_. I was just trying to cover both topics, obtaining the dialect and setting it in connection definition (because it was mentioned in the question). – Victoria Mar 21 '18 at 15:00
1

Ah, now I understand, sorry :) – Mark Rotteveel Mar 21 '18 at 15:01
1

@Mark, no worry. My bad, I should have been more specific. Updated.. :) – Victoria Mar 21 '18 at 20:34

Mark Rotteveel · Answer 2 · 2018-03-22T11:06:14.620

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.

Or one of ODS 10.1 i guess, which were introduced with FB 1.5. Also, since TS is novice in FB, it is worth mentioned that the mapping between ODS-versions and FB version is bottom-up compatibility, not strict one-to-one isomorphism. As a particular example FB 2.5 can work with ODS 9 (IB5) databases, while can introduce minor faults into them. That means, in particular, one can not detect FB version and then translate it into specific ODS, but only into Max Possible ODS — Arioch 'The, Mar 29 '18 at 20:49
Can conenction dialect be different from DB dialect? it would make no sense IMHO. In particular, that perhaps would enable running Entity Framework over Dialect1 DB in recent Q ? — Arioch 'The, Mar 29 '18 at 20:53
@Arioch'The I keep forgetting the rules, so I'd need to test it first. By default the connection dialect is 0, which means "use dialect of the database", IIRC you can connect with dialect 3 to a dialect 1, but I keep forgetting the exact behavior. In any case your comment would probably be more relevant on that other question than here. — Mark Rotteveel, Mar 30 '18 at 08:29

How to query Firebird SQL Dialect

2 Answers2

How to get SQL dialect of the connected Firebird database?

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