Using a SQLite Database in a Web Page

Whereas the demonstration on the preceding page uses Node.js to access a SQLite database stored on the server, this example shows you how to create a database in memory and use it to practise your SQL skills. The JavaScript file sqlite_v3.8.7.4.js is stored in the lib folder. We reuse the arrays of data from our Lazarus BufDataset demonstration of sorting and filtering.

Type your own SQL statement into the edit box then press the button or select one of the statements in the combo box.

SQLiteWebPage.html

If your school security system has blocked the demonstration, click here to try it on a separate page.

Smart Pascal Code of Unit

unit Form1;

interface

uses 
  SmartCL.System, SmartCL.Graphics, SmartCL.Components, SmartCL.Forms, 
  SmartCL.Fonts, SmartCL.Borders, SmartCL.Application, System.SQLite, System.streams,
  SmartCL.Controls.Memo, SmartCL.Controls.Panel, SmartCL.controls.Combobox,
  SmartCL.Controls.EditBox, SmartCL.Controls.Button, SmartCL.Controls.Label,
  SmartCL.Controls.ScrollBox, System.Colors;

type
  TForm1 = class(TW3Form)
    procedure W3ComboBox1Click(Sender: TObject);
    procedure W3Button1Click(Sender: TObject);
  private
    {$I 'Form1:intf'}
    const  CountriesAndCapitals: array[1..78] of string =
       ['England', 'London', 'Scotland', 'Edinburgh', 'Northern Ireland', 'Belfast',
        'Wales', 'Cardiff', 'Ireland', 'Dublin', 'Germany', 'Berlin',
        'France', 'Paris', 'Greece', 'Athens', 'Finland', 'Helsinki',
        'Hungary', 'Budapest', 'Italy', 'Rome', 'Liechtenstein', 'Vaduz',
        'Lithuania', 'Vilnius', 'Luxembourg', 'Luxembourg', 'Poland', 'Warsaw',
        'Portugal', 'Lisbon', 'Romania', 'Bucharest', 'Russia', 'Moscow',
        'Serbia', 'Belgrade', 'Slovakia', 'Bratislava', 'Slovenia', 'Ljubljana',
        'Spain', 'Madrid', 'Sweden', 'Stockholm', 'Switzerland', 'Bern',
        'Norway', 'Oslo', 'Austria', 'Vienna', 'Belgium', 'Brussels',
        'Bosnia and Herzegovina', 'Sarajevo', 'Bulgaria', 'Sofia', 'Netherlands', 'Amsterdam',
        'Czech Republic', 'Prague', 'Moldova', 'Chisinau', 'Latvia', 'Riga',
        'Iceland', 'Reykjavik', 'Malta', 'Valletta', 'Macedonia', 'Skopje',
        'Montenegro', 'Podgorica', 'Turkey', 'Ankara', 'Estonia', 'Tallinn'];
    const  Areas: array[1..39] of integer = [130395, 78387, 14148, 20732, 70280, 357021,
                                        547030, 131940, 338424, 93030, 301230, 160,
                                        65200, 2586, 312685, 92391, 23839, 17075400,
                                        88361, 48845, 20273, 505782, 449964, 41290,
                                        385252, 83858, 30510, 51129, 110910, 41526,
                                        78866, 33843, 64589, 103001, 316, 25713,
                                        13812, 783562, 45226];
    const SQL_Statements: array[0..5] of string =
      ['SELECT Name, Area AS "Area (sq km)", Capital FROM Countries ORDER BY Name',
       'SELECT * FROM Countries ORDER BY Name',
       'SELECT Name, Area AS "Area (sq km)" FROM Countries ORDER BY Area',
       'SELECT Name, Area AS "Area (sq km)" FROM Countries WHERE Area > 300000 ORDER BY Area',
       'SELECT Capital, Name AS "Country" FROM Countries ORDER BY Capital',
       'SELECT * FROM Countries WHERE Name = "England" OR Name = "Scotland"'];
    Europe: TSQLiteDatabase;
    ResultsPanel: TW3Panel;
  protected
    procedure InitializeForm; override;
    procedure InitializeObject; override;
    procedure Display(SQL: string);
  end;

implementation

procedure TForm1.Display(SQL: string);
begin
  var Results := Europe.Exec(SQL);
  var strPanel := "<table border='5'><tr align = 'left'>";
  for var i := 0 to Results.Columns.Length - 1 do
    strPanel += "<th>" + results.Columns[i] + "</th>";
  strPanel += "</tr>";
  for var i := 0 to Results.Values.Length - 1 do
    begin
      strPanel += "<tr>";
      for var j := 0 to Results.Columns.Length - 1 do
        if  LeftStr(results.Columns[j], 4) = "Area" then
          strPanel += "<td align='right'>" + Results.Values[i].values[j].toString() + "&nbsp;&nbsp;</td>"
        else
          strPanel += "<td>" + Results.Values[i].values[j].toString() + "</td>";
      strPanel += "</tr>";
    end;
  strPanel += "</table>";
  ResultsPanel.InnerHTML := strPanel;
end;

procedure TForm1.W3Button1Click(Sender: TObject);
begin
  Display(W3EditBox1.Text);
end;

procedure TForm1.W3ComboBox1Click(Sender: TObject);
begin
  Display(SQL_Statements[W3ComboBox1.SelectedIndex]);
end;

procedure TForm1.InitializeForm;
var
  strSQL: string;
begin
  inherited;
  for var i := 0 to SQL_Statements.Length - 1 do
    W3ComboBox1.Add(SQL_Statements[i]);

  Europe := TSQLiteDatabase.Create;
  Europe.Run('CREATE TABLE Countries (Name VARCHAR(15) NOT NULL PRIMARY KEY,' +
             'Area INT NOT NULL, Capital VARCHAR(15))');
  for var i := 1 to 39 do
    begin
      strSQL := 'INSERT INTO COUNTRIES VALUES (' +
                '"' + CountriesAndCapitals[i * 2 - 1] + '", ' +
                IntToStr(Areas[i]) +
                ', "' +  CountriesAndCapitals[i * 2] + '")';
      Europe.Run(strSQL);
    end;
  Display(SQL_Statements[2]);
  W3ComboBox1.SelectedIndex := 2;
end;

procedure TForm1.InitializeObject;
begin
  inherited;
  {$I 'Form1:impl'}
  W3Label1.Font.Size := 16;
  W3Label1.Font.Weight := 'bold';
  ResultsPanel := TW3Panel.Create(W3ScrollBox1.Content);
  ResultsPanel.SetBounds(0, 0, 300, 660);
  ResultsPanel.Color := clLightYellow;
end;
 
initialization
  Forms.RegisterForm({$I %FILE%}, TForm1);
end.    

XML Code of Form

<SMART>
  <Form version="2" subversion="2">
    <Created>2015-11-21T14:08:33.189</Created>
    <Modified>2015-11-22T14:54:49.292</Modified>
    <object type="TW3Form">
      <Caption>W3Form</Caption>
      <Name>Form1</Name>
      <object type="TW3Panel">
        <Width>544</Width>
        <Top>8</Top>
        <Left>8</Left>
        <Height>320</Height>
        <Name>W3Panel1</Name>
        <object type="TW3ComboBox">
          <Width>528</Width>
          <Top>80</Top>
          <Left>8</Left>
          <Height>32</Height>
          <Name>W3ComboBox1</Name>
          <OnClick>W3ComboBox1Click</OnClick>
        </object>
        <object type="TW3EditBox">
          <Value></Value>
          <Text>SELECT * FROM Countries ORDER BY Area</Text>
          <Range></Range>
          <Width>392</Width>
          <Top>40</Top>
          <Left>8</Left>
          <Height>32</Height>
          <Name>W3EditBox1</Name>
        </object>
        <object type="TW3Button">
          <Caption>Execute</Caption>
          <Width>128</Width>
          <Top>40</Top>
          <Left>408</Left>
          <Height>32</Height>
          <Name>W3Button1</Name>
          <OnClick>W3Button1Click</OnClick>
        </object>
        <object type="TW3Scrollbox">
          <Width>336</Width>
          <Top>120</Top>
          <Left>104</Left>
          <Height>184</Height>
          <Name>W3Scrollbox1</Name>
        </object>
        <object type="TW3Label">
          <Caption>SQLite Demonstration</Caption>
          <Width>248</Width>
          <Top>8</Top>
          <Left>176</Left>
          <Height>32</Height>
          <Name>W3Label1</Name>
        </object>
      </object>
    </object>
  </Form>
</SMART>
Programming - a skill for life!

How to use datasets and databases in Smart Pascal applications