我正在使用Delphi Seattle在一个全新的SQLite文件中创建一个全新的表，并且只使用FieldDefs和非可视代码。 我可以使用ExecSQL（'CREATE TABLE ....'）语法创建一个表，但不是如下所示（我得到'没有这样的表'MyTable'，当我执行CreateDataSet调用时会引发它）。 我想要一些允许我使用FieldDefs的解决方案。 此代码以此处的示例为模型。 我注意到，有关于CreateDataSet的注释，它只适用于TFDMemTable。 是否有运行方式在不使用ExecSQL的情况下创建SQLite表？
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;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;最满意答案
CreateDataSet通常是用于将客户端数据集初始化为空状态的本地操作。 如果TClientDataSet是任何东西，afaik它不能用于创建服务器端表。
要创建一个实际的服务器表，我希望必须构建DDL SQL来创建表，然后在（客户端）数据集上使用ExecSQL执行它，就像您已经尝试过的那样。
更新
以下似乎满足了你在代码中做所有事情的要求，尽管使用了TFDTable组件，它没有表现出FieldDefs，所以我使用了代码创建的TField代替。 在D10西雅图测试。
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;如果通过FDTableAdaptor正确连接到服务器端组件（FDCommand？），我希望有人比我更熟悉使用TFDMemTable的FieldDef。
Fwiw，我已经使用了LargeInt ID字段和WideString Name字段，因为尝试使用Sqlite和D7，我尝试使用Integer和字符串字段时没有遇到任何麻烦。
顺便说一下，如果您在部署之前了解了所需的结构，那么如果只是将空数据库+表复制到位，您可能会发现可以获得更可预测/更强大的结果，而不是尝试在原位创建表。 Ymmv，当然。
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.
更多推荐
发布评论