disqlite3_bind_params_names.dpr

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

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

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

program DISQLite3_Bind_Params_Names;

{$APPTYPE CONSOLE}

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

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

  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. The actual SQL contains '?'
          parameters which we will bind to variables later on. }
        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, RandomString(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_check(sqlite3_finalize(Stmt), DB);
          end;

      finally
        WriteLn('Commiting transaction ...');
        sqlite3_exec_fast(DB, 'COMMIT TRANSACTION');
      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;

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

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