{ 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

  Copyright (c) 2005-2021 Ralf Junker, Yunqa <delphi@yunqa.de>

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

program DISQLite3_sqlite3_get_table;

{$APPTYPE CONSOLE}
{$I DICompilers.inc}
{$I DISQLite3.inc}

{$IFDEF SQLITE_OMIT_GET_TABLE}
!!! This project requires the sqlite3_get_table() API which unavailable in DISQLite3 Personal. !!!
!!! To compile, install DISQLite3 Pro, available from www.yunqa.de/delphi/                     !!!
{$ENDIF SQLITE_OMIT_GET_TABLE}

uses
  {$IFDEF FastMM}{$IFDEF COMPILER_6_UP}FastMM4, {$ENDIF}{$ENDIF}
  DISystemCompat, SysUtils, DISQLite3Api;

{$R ..\Yunqa.res}

const
  DB_FILENAME = 'test.db3';
var
  DB: sqlite3_ptr;
  e: PAnsiChar;
  r, c: Integer;
  Table_Data: PPUtf8CharArray;
  Table_Row_Count, Table_Column_Count: Integer;
begin
  try
    try
      { Initialize the DISQLite3 library prior to using any other DISQLite3
        functionality. See also sqlite3_shutdown() below.}
      sqlite3_initialize;
      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.
          @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 put into the first row. }
            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;

            { Data follows in the next rows. }
            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);

      finally
        { Deallocate any resources that were allocated by
          sqlite3_initialize() above. }
        sqlite3_shutdown;
      end;

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

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

