{ DISQLite3 to show how to run a master detail query on a single database
  connection.

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de

  Copyright (c) 2005-2021 Ralf Junker, Yunqa <delphi@yunqa.de>

------------------------------------------------------------------------------ }

program DISQLite3_Master_Detail_Query;

{$APPTYPE CONSOLE}
{$I DICompilers.inc}
{$I DISQLite3.inc}

uses
  {$IFDEF FastMM}{$IFDEF COMPILER_6_UP}FastMM4, {$ENDIF}{$ENDIF}
  SysUtils, DISQLite3Api, DISQLite3Database;

{$R ..\Yunqa.res}

//------------------------------------------------------------------------------

var
  DB: TDISQLite3Database;
  MasterStmt, DetailStmt: TDISQLite3Statement;
begin
  { Disable all FPU and SSE exceptions. }
  Set8087CW($133F); {$IFDEF CPUX64}SetMXCSR($1F80); {$ENDIF CPUX64}

  try
    DB := TDISQLite3Database.Create(nil);
    try
      { Create tables and fill with example data. }

      DB.DatabaseName := 'test.db3';
      DB.CreateDatabase;

      DB.Execute('CREATE TABLE Countries (ID INTEGER PRIMARY KEY, Name TEXT);');
      DB.Execute('INSERT INTO Countries (ID, Name) VALUES (1, ''France'');');
      DB.Execute('INSERT INTO Countries (ID, Name) VALUES (2, ''Germany'');');
      DB.Execute('INSERT INTO Countries (ID, Name) VALUES (3, ''Italy'');');

      DB.Execute('CREATE TABLE Cities (ID INTEGER PRIMARY KEY, CountryID INTEGER, Name TEXT);');
      DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (1, ''Paris'');');
      DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (1, ''Marseille'');');
      DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (1, ''Lyon'');');

      DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (2, ''Berlin'');');
      DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (2, ''Hamburg'');');
      DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (2, ''Munich'');');

      DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (3, ''Rome'');');
      DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (3, ''Milan'');');
      DB.Execute('INSERT INTO Cities (CountryID, Name) VALUES (3, ''Naples'');');

      { Create master and detail statements and loop through results. }

      MasterStmt := DB.Prepare('SELECT ID, Name FROM Countries;');
      DetailStmt := DB.Prepare('SELECT Name FROM Cities WHERE CountryID = ?;');
      try

        while MasterStmt.Step = SQLITE_ROW do
          begin
            WriteLn(MasterStmt.Column_Str(1));

            try
              DetailStmt.Bind_Int(1, MasterStmt.Column_Int(0));
              while DetailStmt.Step = SQLITE_ROW do
                WriteLn('  ', DetailStmt.Column_Str(0));
            finally
              DetailStmt.Reset;
            end;

          end;

      finally
        DetailStmt.Free;
        MasterStmt.Free;
      end;

    finally
      DB.Free;
    end;

  except
    on e: Exception do
      WriteLn(e.Message);
  end;

  WriteLn;
  WriteLn('Done - Press ENTER to Exit');
  ReadLn;
end.

