Delphi Inspiration – Delphi Components and Software Applications

DISQLite3 example for sqlite3_get_table()

Anonymous editing of this Wiki page is disabled to hamper vandalism. To edit, you must first log in using the button in the left column.


An updated version of this project is distributed with the DISQLite3 package!

{ DISQLite3 example showing the use of sqlite3_get_table().
 
  CAUTION: sqlite3_get_table() is a concenience function only. If the query
  returns lots of data, sqlite3_get_table() will consume huge amounts of memory!
 
  Developers are advised to stay far, far away from sqlite3_get_table() and
  use sqlite3_prepare(), sqlite3_step(), and sqlite3_finalize() instead.
 
  This demo is not meant to encourage the use of sqlite3_get_table(). It was
  written to answer a support question which explicitly asked for an example.
 
  Visit the DISQLite3 Internet site for latest information and updates:
 
    http://www.yunqa.de/delphi/
 
  Copyright (c) 2005-2007 Ralf Junker, The Delphi Inspiration <delphi@yunqa.de>
 
------------------------------------------------------------------------------ }
 
program DISQLite3_sqlite3_get_table;
 
{$APPTYPE CONSOLE}
{$I DI.inc}
{$I DISQLite3.inc}
 
uses
  SysUtils, DISQLite3Api;
 
const
  DB_FILENAME = 'test.db3';
var
  DB: sqlite3;
  e: PAnsiChar;
  r, c: Integer;
  Table_Data: PPAnsiCharArray;
  Table_Row_Count, Table_Column_Count: Integer;
begin
  try
    try
      { Create a new database with simple test table and data. }
      DeleteFile(DB_FILENAME);
      sqlite3_open(DB_FILENAME, @DB);
      sqlite3_exec_fast(DB, 'CREATE TEMP TABLE test (a, b, c)');
 
      sqlite3_exec_fast(DB, 'INSERT INTO test VALUES (1,2,3)');
      sqlite3_exec_fast(DB, 'INSERT INTO test VALUES (4,5,6)');
      sqlite3_exec_fast(DB, 'INSERT INTO test VALUES (7,8,9)');
 
      { Run a query using sqlite3_get_table. }
      if sqlite3_get_table(
        DB, // The databae handle.
        'SELECT * FROM test', // SQL query to execute.
        Pointer(@Table_Data), // Write data result to array of PAnsiChar here.
        @Table_Row_Count, // Write number of result rows here.
        @Table_Column_Count, // Write number of result columns here.
        @e) // Write error message here on failure.
      = SQLITE_OK then
        begin
          { Query successful? Write result to console. }
 
          { Columnn Names are first. }
          Write('|');
          for c := 0 to Table_Column_Count - 1 do
            begin
              Write(Table_Data[c], '|');
            end;
          WriteLn;
 
          { Write horizontal line as data separator. }
          Write('-');
          for c := 0 to Table_Column_Count - 1 do
            begin
              Write('--');
            end;
          WriteLn;
 
          { Row data follows next. }
          for r := 1 to Table_Row_Count do
            begin
              Write('|');
              for c := 0 to Table_Column_Count - 1 do
                begin
                  Write(Table_Data[r * Table_Column_Count + c], '|');
                end;
              WriteLn;
            end;
 
          { Free allocated table data. }
          sqlite3_free_table(Table_Data);
        end
      else
        begin
          { Failure? Output error message. }
          WriteLn(e);
          sqlite3_free(e);
        end;
 
      sqlite3_check(sqlite3_close(DB), DB);
 
    except
      on e: Exception do
        WriteLn(e.Message);
    end;
 
  finally
    WriteLn;
    WriteLn('Done - Press ENTER to Exit');
    ReadLn;
  end;
end.
wiki/sqlite3/sqlite3_get_table.txt · Last modified: 2009/10/28 19:28 (external edit)