disqlite3_prepare_sql.dpr

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

{ DISQLite3 example project showing how to prepare an SQL statement and execute
  it multiple times to retrieve all data from a table.

  This demo uses the table created by the Create_Table demo. Compile and execute
  the Create_Table demo at least once before running the project.

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

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

{$APPTYPE CONSOLE}

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

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

var
  ColumnCount: Integer;
  i, e: Integer;
  Stmt: TDISQLite3StatementHandle;
begin
  WriteLn('SQLite Version ', sqlite3_libversion); WriteLn;

  try
    Open_Demo_Database;
    try
      { Here we prepare an SQL statment for later use. }
      sqlite3_check(sqlite3_prepare(
        DB, // Handle of the Demo.db3 database file.
        'SELECT * FROM People;', // The select statement to retrive all table data
        -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);

      { Did our SQL query compile into a prepared SQL statement? }
      if Assigned(Stmt) then
        try
          { sqlite3_step executes the prepared statement. }
          e := sqlite3_check(sqlite3_step(Stmt), DB);
          { Check if the query returnd at least one row of data. }
          if e = SQLITE_ROW then
            begin
              { If we have data rows, store the number of columns returned. }
              ColumnCount := sqlite3_column_count(Stmt);

              { First write a single line with the column names. }
              for i := 0 to ColumnCount - 1 do
                begin
                  if i > 0 then Write('|');
                  Write(sqlite3_column_name(Stmt, i));
                end;
              WriteLn; WriteLn;

              { Next iterate all data rows and columns, retrieve the data and
                write it to the console. }
              repeat

                for i := 0 to ColumnCount - 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'); // Don't write BLOB data.
                      SQLITE_NULL:
                        Write('NULL'); // Write NULL indicator.
                    end;
                  end;

                WriteLn;

                { Retrieve next row of data and continue until no more rows found. }
                e := sqlite3_check(sqlite3_step(Stmt), DB);
              until e <> SQLITE_ROW;

            end;
        finally
          sqlite3_check(sqlite3_finalize(Stmt), DB);
        end;

    finally
      Close_Demo_Database;
    end;

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

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