Datasets and Databases

We have demonstrated access to a MySQL database by PHP and in the following pages we demonstrate the use of Node.js with Firebird and MySQL on a Raspberry Pi. We begin with a short demonstration of TW3Dataset for which the developer Jon Lennart Aasenden wrote on his website an introduction and an update. We used the version of TW3Dataset within Version 2.2 RC 2 of Smart Mobile Studio.

This demonstration is of a minimal table of forenames and ages in a Smart Pascal console application. It uses the routines CreateDataset, Append, First, Next, Last, MoveTo and Locate. The data is hard coded here, but we show you on a later page how to retrieve data for a dataset from a Firebird database on a Raspberry Pi.

If you see no display at school, the security system might have blocked it. You can try instead this direct link to the program running on its own page.

The Code

unit Unit1;

interface

uses
  System.Types, System.Lists, SmartCL.System, SmartCL.Scroll, SmartCL.Console,
  SmartCL.Components, SmartCL.Application, SmartCL.ConsoleApp, System.Memory,
  System.Dataset;

type
  TApplication = class(TW3CustomConsoleApplication)
  private
    AgeTable: TW3Dataset;
    DisplayedRecordByNumber := False;
  protected
    procedure ApplicationStarting; override;
    procedure PopulateConsole; override;
    procedure ProcessCommand(aCommand: string); override;
    procedure ShowRecord;
  end;

implementation

procedure TApplication.ShowRecord;
begin
  Console.WriteLn(AgeTable.Fields.FieldByName('forename').asString + ': ' +
                  IntToStr(AgeTable.Fields.FieldByName('age').asInteger));
end;

procedure TApplication.ApplicationStarting;
begin
  AgeTable := TW3Dataset.Create;
  AgeTable.fieldDefs.Add("age", ftInteger);
  AgeTable.fieldDefs.add("forename", ftString);
  AgeTable.CreateDataset;

  AgeTable.Append;
  AgeTable.Fields.FieldByName('forename').asString := 'Greg';
  AgeTable.Fields.FieldByName('age').asInteger := 28;
  AgeTable.post;

  AgeTable.Append;
  AgeTable.Fields.FieldByName('forename').asString := 'Jessica';
  AgeTable.Fields.FieldByName('age').asInteger := 29;
  AgeTable.Post;

  AgeTable.Append;
  AgeTable.Fields.FieldByName('forename').asString := 'Mo';
  AgeTable.Fields.FieldByName('age').asInteger := 32;
  AgeTable.post;

  inherited;
  Header.Title.Caption := 'Dataset Console Demo';
end;

procedure TApplication.PopulateConsole;
begin
  AgeTable.First;
  ShowRecord;
  AgeTable.Next;
  ShowRecord;
  AgeTable.Last;
  ShowRecord;
  Console.WriteLn(#13#10 + 'Please press Execute then enter a record number from 1 to 3');
end;

procedure TApplication.ProcessCommand(aCommand: string);
var
  RecNo: integer;
begin
  if not DisplayedRecordByNumber then
    begin
      RecNo := StrToInt(aCommand) - 1;
      if (RecNo < 0) or (RecNo > 2) then
         Console.WriteLn(#13#10 + 'Please press Execute then enter a record number from 1 to 3')
      else
        begin
          AgeTable.MoveTo(RecNo); // zero-based
          ShowRecord;
          DisplayedRecordByNumber := True;
          Console.WriteLn(#13#10 + 'Please press Execute then enter a name.')
        end;
    end
  else
    begin
      if AgeTable.Locate(['forename'],[aCommand]) then
        ShowRecord
      else
        Console.WriteLn(#13#10 + aCommand + ' not found.');
      Console.WriteLn(#13#10 + 'Please press Execute then enter a name.');
    end;
end;

end.