DISQLite3: sqlite3_get_table() example

{ 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:
 
    https://www.yunqa.de/delphi/
 
  Copyright (c) 2005-2017 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.