disqlite3_execute_sql.dpr

来自「DELPHI 访问SQLITE3 数据库的VCL控件」· DPR 代码 · 共 109 行

{ 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-2007 Ralf Junker, The Delphi Inspiration <[e-mail]>

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

program DISQLite3_Execute_SQL;

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

{$APPTYPE CONSOLE}

uses
  SysUtils, DISQLite3Api,
  DISQLite3_Demos_Common in '..\DISQLite3_Common_Units\DISQLite3_Demos_Common.pas';

function New_SQL_Insert_Statement(const ACount: Integer = 1): AnsiString;
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 (''' + RandomString(8) + ''',' + IntToStr(Random(MaxInt)) + '); ';
    end;
end;

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

const
  INSERT_COUNT = 1000;

var
  SQL: AnsiString = ''; // Assign empty string to satisfy FastMM.
begin
  WriteLn('SQLite Version ', sqlite3_libversion); WriteLn;
  Randomize;
  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. }
      SQL := New_SQL_Insert_Statement;
      WriteLn(SQL); WriteLn;
      sqlite3_check(sqlite3_exec(DB, PAnsiChar(SQL), nil, nil, nil), DB);

      { 2. sqlite3_exec executes multiple SQL statements by default. The
           different SQL statements must be separated by a ';' semicolon. }
      SQL := New_SQL_Insert_Statement(2);
      WriteLn(SQL); WriteLn;
      sqlite3_check(sqlite3_exec(DB, PAnsiChar(SQL), 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 mor convenient than 1. Also, it raises an
           exception in case of errors, so we don't need to wrap with
           sqlite3_check. }
      SQL := New_SQL_Insert_Statement;
      WriteLn(SQL); WriteLn;
      sqlite3_exec_fast(DB, SQL);

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

      { 5. To speed up many concurrent inserts, execute multiple SQL statements
           within a TRANSACTION. We use try .. finally for safety in case of
           errors. }
      SQL := New_SQL_Insert_Statement(INSERT_COUNT);
      WriteLn(INSERT_COUNT, ' random INSERTS'); WriteLn;
      sqlite3_exec_fast(DB, 'BEGIN TRANSACTION');
      try
        sqlite3_exec_fast(DB, SQL);
      finally
        sqlite3_exec_fast(DB, 'COMMIT TRANSACTION');
      end;

    finally
      Close_Demo_Database;
    end;

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

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