How can I create a new SQLite file and table at runtime using FieldDefs?

Question

I'm using Delphi Seattle to create a brand new table in a brand new SQLite file and using only FieldDefs and non-visual code. I can create a table using the ExecSQL ('CREATE TABLE....' ) syntax but not as shown below (I get 'No such table 'MyTable' which is raised when I execute the CreateDataSet call). I'd like some solution that allows me to work with FieldDefs. This code is modelled on the example here. I notice though, that there is note regarding CreateDataSet that it only applies to TFDMemTable. Is there a runtime way of creating an SQLite table without using ExecSQL?

procedure Test;
const
  MyDBFile = 'c:\scratch\hope.db';
var
  Connection : TFDConnection;
  DriverLink : TFDPhysSQLiteDriverLink;
  Table : TFDTable;
begin
  DeleteFile( MyDBFile );
  DriverLink := TFDPhysSQLiteDriverLink.Create( nil );
  Connection := TFDConnection.Create( nil );
  try
    Connection.Params.Values['DriverID'] := 'SQLite';
    Connection.Params.Values['Database'] := MyDBFile;
    Connection.Connected := True;

    Table := TFDTable.Create( nil );
    try
      Table.TableName := 'MyTable';
      Table.Connection := Connection;
      Table.FieldDefs.Add( 'one', ftString, 20 );
      Table.FieldDefs.Add( 'two', ftString, 20 );
      Table.CreateDataSet;

      // I would add records here....
    finally
      Table.Free;
    end;
  finally
    Connection.Free;
    DriverLink.Free;
  end;
end;

Answer 1

CreateDataSet is usually a local operation for initializing a client-side dataset into an empty state. If TClientDataSet is anything to go by, afaik it cannot be used create a server-side table.

To create an actual server table, I would expect to have to construct the DDL SQL to create the table and then execute it using ExecSQL on the (client-side) dataset, as you have already tried.

update

The following seems to satisfy your requirement to do everything in code, though using a TFDTable component, which doesn't surface FieldDefs, so I've used code-created TFields instead. Tested in D10 Seattle.

procedure TForm3.CreateDatabaseAndTable;
const
  DBName = 'd:\delphi\code\sqlite\atest.sqlite';
var
  AField : TField;
begin
  if FileExists(DBName) then
    DeleteFile(DBName);

  AField := TLargeIntField.Create(Self);
  AField.Name := 'IDField';
  AField.FieldName := 'ID';
  AField.DataSet := FDTable1;

  AField := TWideStringField.Create(Self);
  AField.Size := 80;
  AField.Name := 'NameField';
  AField.FieldName := 'Name';
  AField.DataSet := FDTable1;

  FDConnection1.Params.Values['database'] := DBName;
  FDConnection1.Connected:= True;

  FDTable1.TableName := 'MyTable';
  FDTable1.CreateTable(False, [tpTable]);
  FDTable1.Open();
  FDTable1.InsertRecord([1, 'First']);
  FDConnection1.Commit;


  FDConnection1.Connected:= False;
end;

I expect that someone a bit more familiar than I am could do similar using a TFDMemTable's FieldDefs if it were correctly connected to a server-side component (FDCommand?) via an FDTableAdaptor.

Fwiw, I've used a LargeInt ID field and WideString Name field because trying to use Sqlite with D7 a while back, I had no end of trouble trying to use Integer and string fields.

Btw, you if you know the structure you require in advance of deployment, you might find that you get more predictable/robust results if you simply copy an empty database + table into place, rather than try and create the table in situ. Ymmv, of course.