{ DISQLite3 example project showing how to prepare an SQL statement and execute
  it multiple times to retrieve all data from a table.

  This demo uses the table created by the Create_Table demo. Compile and execute
  the Create_Table demo at least once before running the project.

  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_Prepare_SQL;

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

uses
  {$IFDEF FastMM}FastMM4, {$ENDIF}SysUtils, DISQLite3Api,
  DISQLite3_Demos_Common in '..\DISQLite3_Common_Units\DISQLite3_Demos_Common.pas';

{$R ..\Yunqa.res}

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

var
  ColumnCount: Integer;
  i, e: Integer;
  Stmt: sqlite3_stmt_ptr;
begin
  { Disable all FPU and SSE exceptions. }
  Set8087CW($133F); {$IFDEF CPUX64}SetMXCSR($1F80); {$ENDIF CPUX64}

  WriteLn('SQLite Version ', sqlite3_libversion); WriteLn;
  try
    Open_Demo_Database;
    try
      { Initialize the DISQLite3 library prior to using any other DISQLite3
        functionality. See also sqlite3_shutdown() below.}
      sqlite3_initialize;
      try
        { Here we prepare an SQL statment for later use. }
        sqlite3_check(sqlite3_prepare_v2(
          DB, // Handle of the Demo.db3 database file.
          'SELECT * FROM People;', // The select statement to retrive all table data
          -1, // Length of SQL statement, pass -1 to autodetect
          @Stmt, // Variable for the prepared SQL statement
          nil), // Variable to store beginning of next SQL statement or nil if not needed.
          DB);

        { Did our SQL query compile into a prepared SQL statement? }
        if Assigned(Stmt) then
          try
            { sqlite3_step executes the prepared statement. }
            e := sqlite3_check(sqlite3_step(Stmt), DB);
            { Check if the query returnd at least one row of data. }
            if e = SQLITE_ROW then
              begin
                { If we have data rows, store the number of columns returned. }
                ColumnCount := sqlite3_column_count(Stmt);

                { First write a single line with the column names. }
                for i := 0 to ColumnCount - 1 do
                  begin
                    if i > 0 then Write('|');
                    Write(sqlite3_column_name(Stmt, i));
                  end;
                WriteLn; WriteLn;

                { Next iterate all data rows and columns, retrieve the data and
                  write it to the console. }
                repeat

                  for i := 0 to ColumnCount - 1 do
                    begin
                      if i > 0 then Write('|');
                      { We optimize the data output according to the column type. }
                      case sqlite3_column_type(Stmt, i) of
                        SQLITE_INTEGER:
                          Write(sqlite3_column_int64(Stmt, i));
                        SQLITE_FLOAT:
                          Write(sqlite3_column_double(Stmt, i));
                        SQLITE_TEXT:
                          Write(sqlite3_column_str(Stmt, i));
                        SQLITE_BLOB:
                          Write('BLOB'); // Write BLOB indicator.
                        SQLITE_NULL:
                          Write('NULL'); // Write NULL indicator.
                      end;
                    end;

                  WriteLn;

                  { Retrieve next row of data and continue until no more rows found. }
                  e := sqlite3_check(sqlite3_step(Stmt), DB);
                until e <> SQLITE_ROW;

              end;
          finally
            sqlite3_finalize(Stmt);
          end;

      finally
        Close_Demo_Database;
      end;

    finally
      { Deallocate any resources that were allocated by
        sqlite3_initialize() above. }
      sqlite3_shutdown;
    end;

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

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

