{ 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/delphi/

  Copyright (c) 2005-2009 Ralf Junker, The Delphi Inspiration <delphi@yunqa.de>

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

program DISQLite3_Master_Detail_Query;

{$I DI.inc}
{$I DISQLite3.inc}

{$APPTYPE CONSOLE}

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

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

var
  DB: TDISQLite3Database;
  MasterStmt, DetailStmt: TDISQLite3Statement;
begin
  { Disable FPU exceptions. No need to restore, setting is process specific. }
  Set8087CW($133F);

  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.

