{ How to reuse a prepared SELECT statement with DISQLite3. The statement is
  prepared just once, but is used for different queries by binding different
  values to it.

  Run the DISQLite3_Create_Table demo first to create the required table.

  Visit the DISQLite3 Internet site for latest information and updates:

    http: //www.yunqa.de/delphi/

  Copyright(c)2005 - 2008 Ralf Junker, the Delphi Inspiration < Delphi@yunqa.de >

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

program DISQLite3_SELECT_with_bind;

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

{$APPTYPE CONSOLE}

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

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

{ This procedure just prints out the results returned from a prepared
  statement. After all results are printed, the statement is reset. }
procedure ExecutePreparedStatement(const Stmt: TDISQLite3StatementHandle);
var
  i: Integer;
begin
  while sqlite3_check(sqlite3_step(Stmt)) = SQLITE_ROW do
    begin
      for i := 0 to sqlite3_column_count(Stmt) - 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;
    end;

  { Reset the statement immediately after it has been used. This clears any
    pending database locks and prepares the statement for another round of
    sqlite3_step(). }
  sqlite3_check(sqlite3_reset(Stmt));
end;

var
  Stmt: TDISQLite3StatementHandle;
begin
  WriteLn('SQLite Version ', sqlite3_libversion); WriteLn;

  { Disable FPU exceptions. No need to restore, setting is process specific. }
  Set8087CW($133F);

  try
    { Initialize the DISQLite3 library prior to using any other DISQLite3
      functionality. See also sqlite3_shutdown() below.}
    sqlite3_initialize;
    try
      Open_Demo_Database;
      try
        { Pepare an SQL statment for later use. Notice that this statement is not
          finalized until just before the database closes. This allows to reuse
          it during the application lifetime without repreparing. Since preparing
          SQL statements is quite time consuming, reusing statements speeds up
          performance, especially when running it many times. }
        sqlite3_check(sqlite3_prepare_v2(
          DB, // Handle of the Demo.db3 database file.
          'SELECT FirstName, LastName FROM People WHERE FirstName = ?',
          -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);
        try

          { Bind a value to the 1st parameter of the prepared statement. This
            effectively replaces the ? parameter with the string value 'Albert'. }
          sqlite3_bind_str(Stmt, 1, 'Albert');
          ExecutePreparedStatement(Stmt);

          { Do like above, but use the string value 'Leonardo' this time. }
          sqlite3_bind_str(Stmt, 1, 'Leonardo');
          ExecutePreparedStatement(Stmt);

        finally
          { All prepared statements must eventually be finalized
            before closing the database. }
          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.

