{ DISQLite3 example project showing how to prepare an SQL statement and execute
  it multiple times to achieve very fast inserts.

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

{$APPTYPE CONSOLE}

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

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

const
  INSERT_COUNT = 20000;

var
  i: Integer;
  Stmt: TDISQLite3StatementHandle;
  ParamCount: Integer;
  ParamRandomText: Integer;
  ParamRandomInt: Integer;
  TickCount: Cardinal;
begin
  WriteLn('SQLite Version ', sqlite3_libversion);
  Randomize;

  { 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
        Create_Demo_Tables;

        TickCount := GetTickCount;

        { We are inserting quite a few new records, to wap the inserts in a
          transaction to increase performance. }
        WriteLn('Begining transaction ...');
        sqlite3_exec_fast(DB, 'BEGIN TRANSACTION');
        try

          WriteLn('Preparing SQL statement ...');
          { Prepare an SQL statement for later use. Note the SQL ':Name' parameters.
            We use them to bind variable values to the statement further below. }
          sqlite3_check(sqlite3_prepare(
            DB, // Handle of the Demo.db3 database file.
            'INSERT INTO RandomTable (RandomText, RandomInt) VALUES (:Random_Text, :Random_Int);', // The SQL statement
            -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);

          if Assigned(Stmt) then
            try
              { Print details about SQL parameters to the console. }

              { Retrieve the number of parameters in the SQL. }
              ParamCount := sqlite3_bind_parameter_count(Stmt);

              { Print each parameter name to the console. }
              WriteLn('Number of parameters in SQL: ', ParamCount);
              for i := 1 to ParamCount do
                WriteLn(i, ' - ', sqlite3_bind_parameter_name(Stmt, i));

              { Retrieve the index of the parameter names. Zero indicates failure. }
              ParamRandomText := sqlite3_bind_parameter_index(Stmt, ':Random_Text');
              if ParamRandomText = 0 then
                WriteLn('Parm :Random_Text does not exist.');

              ParamRandomInt := sqlite3_bind_parameter_index(Stmt, ':Random_Int');
              if ParamRandomInt = 0 then
                WriteLn('Parm :Random_Int does not exist.');

              { Loop inserting new random data. }
              WriteLn('Inserting data ...');
              for i := 1 to INSERT_COUNT do
                begin
                  { Bind new values to the parameters. }
                  sqlite3_check(sqlite3_bind_str(Stmt, ParamRandomText, RandomString8(8)), DB);
                  sqlite3_check(sqlite3_bind_int(Stmt, ParamRandomInt, Random(MaxInt)), DB);

                  { Execute the statement. Since we are inserting new data, no rows
                    of data will be returned and we do not need any further checks. }
                  sqlite3_check(sqlite3_step(Stmt), DB);

                  { Reset the statement after execution to prepare for another run
                    of the same statement, but with different parameters assigned. }
                  sqlite3_check(sqlite3_reset(Stmt), DB);

                  { Give some feedback. }
                  if i mod (INSERT_COUNT div 10) = 0 then WriteLn(i, ' inserted');
                end;

              WriteLn(INSERT_COUNT, ' inserted');
            finally
              sqlite3_finalize(Stmt);
            end;

          { If there were no errors, commit the transaction to make the
            record insertions permanent. }
          WriteLn('Commiting transaction ...');
          sqlite3_exec_fast(DB, 'COMMIT TRANSACTION');

        except
          { This block will only be executed after an error occured: Rollback
            the transaction to restore the initial state of the database. }
          on e: Exception do
            begin
              WriteLn('Error: ', e.Message);
              WriteLn('Rolling back transaction ...');
              sqlite3_exec_fast(DB, 'ROLLBACK TRANSACTION');
            end;
        end;

        TickCount := GetTickCount - TickCount;
        if TickCount = 0 then TickCount := 1;
        WriteLn;
        WriteLn('Time: ', TickCount, ' ms');
        WriteLn(Round(INSERT_COUNT / (TickCount / 1000)), ' inserts / sec');

      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.

