{ DISQLite3 example project showing how to execute SQL statements against
  a database.

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

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

{$APPTYPE CONSOLE}

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

function New_SQL_Insert_Statement(const ACount: Integer = 1): Utf8String;
var
  c: Integer;
begin
  Result := '';
  for c := 1 to ACount do
    begin
      { Create a new INSERT statement which adds random records to "DemoTable". }
      Result := Result +
        'INSERT INTO RandomTable (RandomText,RandomInt) ' +
        'VALUES (''' + RandomString8(8) + ''',' + Utf8String(IntToStr(Random(MaxInt))) + '); ';
    end;
end;

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

const
  INSERT_COUNT = 1000;

var
  SQL8: Utf8String = ''; // Assign empty string to satisfy FastMM.
begin
  WriteLn('SQLite Version ', sqlite3_libversion); WriteLn;

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

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

        { DISQLite3 offers various ways how to execute SQL: }

        { 1. Execute a single SQL statement using the default sqlite3_exec
             function. This function does not raise any exceptions, so we wrap
             it with sqlite3_check. }
        Sql8 := New_SQL_Insert_Statement;
        WriteLn(SQL8); WriteLn;
        sqlite3_check(sqlite3_exec(DB, PUtf8Char(SQL8), nil, nil, nil), DB);

        { 2. sqlite3_exec executes multiple SQL statements by default. The
             different SQL statements must be separated by a ';' semicolon. }
        SQL8 := New_SQL_Insert_Statement(2);
        WriteLn(SQL8); WriteLn;
        sqlite3_check(sqlite3_exec(DB, PUtf8Char(SQL8), nil, nil, nil), DB);

        { 3. Execute a single SQL statement using the Delphi-specific
             sqlite3_exec_fast function. This function has less options, less
             overhead and is therefore more convenient than 1. Also, it raises an
             exception in case of errors, so we don't need to wrap with
             sqlite3_check. }
        SQL8 := New_SQL_Insert_Statement;
        WriteLn(SQL8); WriteLn;
        sqlite3_exec_fast(DB, SQL8);

        { 4. sqlite3_exec_fast also executes multiple SQL statements by default.
             Again, the different SQL statements must be separated by a ';'
             semicolon. }
        SQL8 := New_SQL_Insert_Statement(2);
        WriteLn(SQL8); WriteLn;
        sqlite3_exec_fast(DB, SQL8);

        { 5. To speed up many concurrent inserts, execute multiple SQL statements
             within a TRANSACTION. We use try .. excet for safety: In case of
             errors, the database will roll back to its initial state. }
        SQL8 := New_SQL_Insert_Statement(INSERT_COUNT);
        WriteLn(INSERT_COUNT, ' random INSERTS'); WriteLn;
        sqlite3_exec_fast(DB, 'BEGIN TRANSACTION');
        try
          sqlite3_exec_fast(DB, SQL8);
          sqlite3_exec_fast(DB, 'COMMIT TRANSACTION');
        except
          sqlite3_exec_fast(DB, 'ROLLBACK TRANSACTION');
        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.

