1

Is there are way to use the TFDTable.CreateTable method in Delphi to automatically create the trigger and generator for an autoinc field?

Using this as an example

Create table with firedac without SQL Script

I have tried the following:

Table := TFDTable.Create(nil);
try
  Table.Connection := MyConnection;
  Table.TableName := 'Vehicles';
  Table.FieldDefs.Add('VehIdx', ftAutoInc, 0, True);
  Table.FieldDefs.Add('PIN', ftString, 15, False);
  Table.FieldDefs.Add('VehID', ftString, 15, False);
  Table.FieldDefs.Add('VehName', ftString, 50, False);
  Table.AddIndex('pkVehiclesIdx', 'VehIdx', '', [soPrimary]);
  Table.CreateTable(True);
finally
  Table.Free;
end;

But I get "Generator GEN_VEHICLES not found".

I'm using Firebird.

I know that I can create the trigger and generator myself. But it seems that FireDAC should be able to do it. I'm wondering how to make it do that?

OK, I have found out how to do it. Here's the complete code:

  procedure CreateVehiclesTable;
  var
    Table: TFDTable;
  begin
    Table := TFDTable.Create(nil);
    try
      Table.Connection := FrmMain.ConLocal;
      Table.TableName := 'Vehicles';
      Table.FieldDefs.Add('VehIdx', ftAutoInc, 0, False);
      Table.FieldDefs.Add('PIN', ftString, 15, False);
      Table.FieldDefs.Add('VehID', ftString, 15, False);
      Table.FieldDefs.Add('VehName', ftString, 50, False);
      Table.OptionsIntf.UpdateOptions.GeneratorName := 'GEN_VEHICLES';
      Table.AddIndex('pkVehiclesIdx', 'VehIdx', '', [soPrimary]);
      Table.CreateTable(False, [tpTable, tpPrimaryKey, tpGenerators, tpTriggers]);
    finally
      Table.Free;
    end;

Manually setting the generator name makes everything work correctly. The trigger and generator are created and the VEHIDX field increments automatically as it should.

Tim
  • 91
  • 2
  • 6
  • On which line, exactly? Use the debugger and call-stack to identify exactly where the error is occurring. Anyway, regarding your q, what's stopping you writing your own `TFDTable`-descendant to do this? – MartynA Sep 12 '18 at 07:37
  • I think this is not a Delphi/Firedac question. As I can understand your question you want to use the automatic key generation service. This is completely independent from the clients. This is a RDBMS question. There are 3 main key generation strategies out there (sequence,identity,table) and all RDBMS implementation have made its choice. So you have to administer the RDBMS to use the key generation service. (create the counter seq/idt/table and row) You can find a lot of usable information about that in the Java EJB/JPA documentations in the chapter discus the @GeneratedValue annotation. – SOLID Developper Sep 12 '18 at 07:49
  • What database system are you connecting against? Firebird or something else? – NineBerry Sep 12 '18 at 09:54

0 Answers0