4

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;
Brian Frost
  • 13,334
  • 11
  • 80
  • 154
  • 1
    What's wrong with calling CreateTable instead of CreateDataSet? And more importantly, what's wrong with doing it properly with DDL and `ExecSQL`? You'd be done by now, and you'd use far less code. – Ken White Mar 12 '16 at 01:17
  • CreateTable gives the same error Ken. As you suggest, ExecSQL has to be my route, but given that I don't know the number and type of my fields until runtime, I wanted to use the built in list capability of FieldDefs rather than have to put together a rather messy string. Seems I'll have to! – Brian Frost Mar 12 '16 at 09:39
  • I would agree to using SQL *IF* we were talking of a proper DBMS... but that is not the case here. SQLite is *hideous* and in my opinion it can't be used for data handling. Alas, pretty much every mobile OS has it in some form, so we are obliged to use it, unless we're willing to make apps bigger. Sigh. – Andrea Raimondi Jul 26 '16 at 07:27
  • Pretty comprehensive article on SQLite and FireDAC from Embarcadero here: http://docwiki.embarcadero.com/RADStudio/Rio/en/Using_SQLite_with_FireDAC – Toby Nov 27 '19 at 21:23

3 Answers3

3

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.

MartynA
  • 30,454
  • 4
  • 32
  • 73
  • Thanks MartynA, yes I don't know the number and type of my fields until runtime unfortunately. In the 'old' days with DBF and Paradox tables, it was possible to use CreateTable etc... – Brian Frost Mar 12 '16 at 09:40
  • Yes, very useful thanks, and I've accepted it because it does answer the original post. In the meantime I've gone over to using DDL generated from my data requirements though, since as others have (and you) pointed out that this is the 'proper' way to use SQL. – Brian Frost Mar 13 '16 at 09:27
1

I would NEVER dream of creating database tables using fielddefs because you wind up having tables without a proper primary key, indexes and referential integrity. The resulting tables are totally "dumbed down".

Whenever you have a "where" clause in a query the database would do a full table scan to find the records matching the query. So your database slows down (and CPU use increases) with size. That's just bad design.

Regards, Arthur

Arthur Hoornweg
  • 11
  • 1
0

You can use the app SQLite Expert Professional, create SQLite database.

And using FDConnection connect to the database. And use it. Method to database SQLite, the same way that MartynA have said.

Begin
  FDConnection1.Connected:=false;
  FDConnection1.Params.Clear;
  FDConnection1.Params.Database:='D:\SQLiteDatabase.db';
  FDConnection1.ConnectionDefName:='SQLite_Demo';
  FDConnection1.DriverName:='SQLite';
  FDConnection1.Connected:=true;
  FDTable1.Open;
End;
A.K
  • 106
  • 1
  • 11