mORMot Open Source

gclaxton

Member

Registered: 2011-05-23

Posts: 6

Display SQLite3 table-column in a Delphi7 TListView

I would like to take the following unit (DrivesData) and display the drive column in a TListView. I've never worked with the (Synopse) SQLite3 code before so I'm hoping someone could give me a little push in the right direction.

Just add the DrivesData unit to the uses clause then run and it will create the "drives.sqlite" database file with a list of drives 'A' to 'Z'.

unit DrivesData;

interface

uses
  Windows, SQLite3, SynCommons, SQLite3Commons, Classes;

type
  TDrives = class(TSQLRecord)
  private
    { Private declarations }
    FDrive: RawUTF8;
  protected
    { Protected declarations }
    FDrivesModel: TSQLModel;
  public
    { Public declarations }
    constructor Create(); override;
    destructor Destroy(); override;

    function GetCount(): Integer;
    procedure GetItems(var Items: TStringList);
  published
    { Published declarations }
    property Drive: RawUTF8 read FDrive write FDrive;
  end;

var
  DriveRecord: TDrives;
  GlobalClient: TSQLRestClientURI;

implementation

//uses
//  SysUtils;

function CreateDrivesModel(): TSQLModel;
begin
  Result := TSQLModel.Create([TDrives], 'root');
end;

{ TDrives }
constructor TDrives.Create();
var
  X: AnsiChar;
begin
  inherited Create();

  FDrivesModel := CreateDrivesModel();
  GlobalClient := TSQLRestClientDB.Create(FDrivesModel, CreateDrivesModel(), 'drives.sqlite', TSQLRestServerDB);

  TSQLRestClientDB(GlobalClient).Server.DB.Execute(
    'CREATE TABLE IF NOT EXISTS drives ' +
    '(id INTEGER PRIMARY KEY, drive TEXT NOT NULL UNIQUE COLLATE NOCASE);');

  for X := 'A' to 'Z' do
  begin
    TSQLRestClientDB(GlobalClient).Server.DB.Execute(
      'INSERT OR IGNORE INTO drives (drive) VALUES ("' + StringToUTF8(X) + ':")');
  end;
end;

destructor TDrives.Destroy();
begin
  if Assigned(FDrivesModel) then
    FDrivesModel.Free();

  inherited Destroy();
end;

function TDrives.GetCount(): Integer;
var
  table: TSQLTable;
begin
  Result := -1;
  table := GlobalClient.ExecuteList([TDrives], 'SELECT COUNT(*) as CNT FROM drives');
  try
    if (table.RowCount > 0) then
      Result := table.GetAsInteger(1, 0);
  finally
    table.Free();
  end;
end;

procedure TDrives.GetItems(var Items: TStringList);
var
  table: TSQLTable;
begin
  table := GlobalClient.List([TDrives], 'COUNT(*)', 'drive');
  if (table <> nil) then
  try
    ;
  finally
    table.Free();
  end;
end;

initialization
  GlobalClient := nil;
  DriveRecord := TDrives.Create();

finalization
  if Assigned(GlobalClient) then
    GlobalClient.Free();

  if Assigned(DriveRecord) then
    DriveRecord.Free();

end.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,269

Website

Re: Display SQLite3 table-column in a Delphi7 TListView

But I'm afraid you are missing some points of the framework:

for instance you're mixing record level and MVC application level: a TSQLRecord maps a DB table and you should not declare MVC TSQLModel and TSQLRest inside this class;
and you're missing the ORM approach, you don't need to write all those SQL code (the CREATE TABLE and the INSERT): the framework will write it for you, with no error, and the exact expected column type (with collations)!
You should better use a TSQLRestClientDB class instead of TSQLRestServerDB, even if you are still working locally. So you'll get a lot more features with no performance penalty.

You are using a Char type in your code. Our framework is UTF-8 oriented, so you should use AnsiChar instead, or use StringToUtf8() function to ensure correctness (at least with Unicode version of Delphi).

I'll recommend that you take a look at the sample code source code and the provided documentation (especially the SAD document, in the general presentation in the first pages, including the SynFile main demo).

To retrieve some data, then display it in the VCL (e.g. in a TListBox), take a look at the TSQLTableJSON class. There are some code sample in the SAD document (take a look at the keyword index, at the beginning of the document, if you're a bit lost).

gclaxton

Member

Registered: 2011-05-23

Posts: 6

Re: Display SQLite3 table-column in a Delphi7 TListView

Not sure if you noticed or not, I change a lot of the code per your suggestions.

gclaxton

Member

Registered: 2011-05-23

Posts: 6

Re: Display SQLite3 table-column in a Delphi7 TListView

Solution:

class procedure TSQLDrives.GetDriveList(Items: TStrings);
var
  table: TSQLTableJSON;
  X, FieldIndex: Integer;
begin
  table := TSQLRestClientDB(GlobalClient).ExecuteList([TSQLDrives], 'SELECT * FROM drives');
  if (table <> nil) then
  try
    FieldIndex := table.FieldIndex('drive');
    if (FieldIndex >= 0) then
      for X := 1 to table.RowCount do
        Items.Add(UTF8ToString(table.GetU(X, FieldIndex)));
  finally
    table.Free();
  end;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,269

Website

Re: Display SQLite3 table-column in a Delphi7 TListView

You still initialize a Client per TSQLRecord instance, which is NOT the right way to do it.

See samples 01 to 04 in order to guess how to implement it. You need to separate each TSQLRecord to the MVC Model+DB layers.

Here is perhaps some attempt to code your sample, IMHO in a more correct way:
(written in the forum so I'm not sure it will compile )

unit DrivesData;

interface

uses
  Windows, SQLite3, SynCommons, SQLite3Commons, Classes;

type
  TDrives = class(TSQLRecord)
  private
    FDrive: RawUTF8;
  published
    property Drive: RawUTF8 read FDrive write FDrive;
  end;

  TMyClient = class(TSQLRestClientDB)
  public
    procedure FillDrives(aList: TStrings);
  end;

var
  DrivesModel: TSQLModel;
  GlobalClient: TMyClient;

implementation

procedure InitDrives;
var X: AnsiChar;
     D: TDrives;
begin
  DrivesModel := TSQLModel.Create([TDrives], 'root');
  GlobalClient := TMyClient.Create(DrivesModel, nil, 'drives.sqlite', TSQLRestServerDB);
  GlobalClient.CreateMissingTables(0);
  if GlobalClient.TableRowCount(TDrives)=0 then
  begin
    D := TDrives.Create;
    try
      for X := 'A' to 'Z' do 
      begin
        D.Drive := X;
        GlobalClient.Add(D,true);
      end;
    finally
      D.Free;
    end;
  end;
end;

procedure TMyClient.FillDrives(aList: TStrings);
begin
  aList.BeginUpdate;
  try
    aList.Clear;
    with TSQLDrives.CreateAndFillPrepare(GlobalClient,'') do
    try
      while FillOne do
        aList.Add(UTF8ToString(Drive));
    finally
      Free;
    end;
  finally
    aList.EndUpdate;
  end;
end;

initialization
  InitDrives;

finalization
  GlobalClient.Free;
  DrivesModel.Free;
end.

Note that there is no SQL code any more in this sample code. All is done by the ORM, just from the TSQLDrives record layout. CreateMissingTables() will create the missing tables in the DB for you... Even the current row count is retrieved by the high-level TSQLRest.TableRowCount method.

I use the TSQLRecord.FillPrepare or CreateAndfillPrepare + "while Rec.FillOne do" for the code to be a bit more ORM oriented (no SELECT to write).

In fact, FillPrepare/FillOne will use an internal temporary TSQLTableJSON to retrieve the table content, then FillOne will map each row to the current TSQLRecord instance, so that you could access to the "drive" column via the Drive field.

You should have even written the FillDrives method as such, using the TSQLRest.OneFieldValues method:

procedure TMyClient.FillDrives(aList: TStrings);
var Data: TRawUTF8DynArray;
     i: integer;
begin
  aList.BeginUpdate;
  try
    aList.Clear;
    GlobalClients.OneFieldValues(TSQLDrives,'drive','',Data);
    for i := 0 to high(Data) do
      aList.Add(UTF8ToString(Data[i]));
  finally
    aList.EndUpdate;
  end;
end;

Or, at least, in order to populate your TSQLTableJSON instance:

table := GlobalClient.MultiFieldValues(TSQLDrives,'drive');

... and not any SELECT statement by hand

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,269

Website

Re: Display SQLite3 table-column in a Delphi7 TListView

I forgot to mention this even higher-level method:

procedure TMyClient.FillDrives(aList: TStrings);
begin
  GlobalClients.OneFieldValues(TSQLDrives,'drive','',aList);
end;

All your query done in one line!!!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,269

Website

Re: Display SQLite3 table-column in a Delphi7 TListView

I've used your sample code in order to illustrate the latest version of the "ORM is not DB" chapter in the SAD document of the framework documentation.

You could find there some reference about the framework use.

Thanks for your interest!