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 and MoveTo. The Locate method is no longer present in Version 3.0 of Smart Mobile Studio and we have revised our code. 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
      AgeTable.First;
      var Found := False;
      repeat        
        if AgeTable.Fields.FieldByName('forename').asString = aCommand then
          begin
            ShowRecord;
            Found := True;
          end;
        AgeTable.Next;          
      until AgeTable.EOF or Found = True;
      if Found = False then Console.WriteLn(#13#10 + aCommand + ' not found.');
      Console.WriteLn(#13#10 + 'Please press Execute then enter a name.');
    end;
end;

end.