DISQLite3: BLOB and TStream example

{ This DISQLite3 example demonstrates different methods to save and load
  TStream data to and from database BLOBs. The advantages of the different
  methods described.
 
  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_Blob_and_Streams;
 
{$APPTYPE CONSOLE}
{$I DI.inc}
{$I DISQLite3.inc}
 
uses
  SysUtils, Classes, DISQLite3Api, DISQLite3Database;
 
var
  DB: TDISQLite3Database;
 
  { Use the "traditional" method to write the BLOB's contents to a new database
    row. This method binds BLOBs to a prepared statement, just like with
    integers and strings. It allows to bind multiple BLOBs alongside with other
    field types in the same INSERT or UPDATE statement, which can be both
    convenient and fast.
 
    The "traditional" method requires all BLOB data in a contiguous block of
    memory. This is no problem with small streams and files, but can stress the
    system for huge data loads. It should therefore be used with caution if the
    size of the BLOBs is unknown. }
function Write_Stream_to_Blob_Traditional(const AStream: TStream): Int64;
var
  l: Integer;
  p: Pointer;
  Stmt: TDISQLite3Statement;
begin
  Stmt := DB.Prepare('INSERT INTO Blobs (b) VALUES (?)');
  try
    { Optimize memory useage for TCustomMemoryStream. }
    if AStream is TCustomMemoryStream then
      begin
        { If AStream descends from TCustomMemoryStream, we can bind the stream's
          memory directly. Also let the DISQLite3 engine know that the memory
          is static and does not change during the runtime of this function. }
        with AStream as TCustomMemoryStream do
          Stmt.Bind_Blob(1, Memory, Size, SQLITE_STATIC);
      end
    else
      begin
        { If AStream does not descend from TCustomMemoryStream,
          we need to read its data into memory first. }
        l := AStream.Size;
        GetMem(p, l);
        AStream.Seek(0, soFromBeginning);
        AStream.Read(p^, l);
        { Bind the in-memory data. The sqlite3_Destroy_Mem parameter tells
          DISQLite3 to free the memory as soon as the database engine is done
          with it. }
        Stmt.Bind_Blob(1, p, l, sqlite3_Destroy_Mem);
      end;
    Stmt.Step;
    Result := DB.LastInsertRowID;
  finally
    Stmt.Free;
  end;
end;
 
//------------------------------------------------------------------------------
 
{$IFNDEF SQLITE_OMIT_INCRBLOB}
 
{ The "incremental" method can handle BLOBs and files of any size because it
  transfers them in small chunks at a time only. It is the preferred method for
  BLOBs of unknown or very large size, and works equally well with small BLOBs.
 
  The "incremental" method can only handle on a single BLOB at a time, which
  makes it a little less flexible to work with.
 
  The "incremental" method is not available in DISQLite3 Personal. }
function Write_Stream_to_Blob_Incremental(const AStream: TStream): Int64;
var
  BlobStream: TDISQLite3IncrBlobStream;
  Stmt: TDISQLite3Statement;
begin
  { First create a prepared statement, bind a ZeroBlob, and call Step to insert
    it. A ZeroBlob is an "empty" BLOB of a particular size which is not yet
    filled with data. We need to insert it here as a plcaholder into which we
    can incrementally write the stream data in step two below. }
  Stmt := DB.Prepare('INSERT INTO Blobs (b) VALUES (?)');
  try
    Stmt.Bind_ZeroBlob(1, AStream.Size);
    Stmt.Step;
    Result := DB.LastInsertRowID;
  finally
    Stmt.Free;
  end;
 
  { Create an incremental BLOB stream and write the stream data to it. The
    CopyFrom method does the main work. }
  BlobStream := TDISQLite3IncrBlobStream.Create(
    DB, // The database connection.
    'main', // Database which contains the BLOB table.
    'Blobs', // BLOB table.
    'b', // BLOB column.
    Result, // RowID
    1); // 1 = Read and Write access, 0 = Read only.
  try
    BlobStream.CopyFrom(AStream, 0);
  finally
    BlobStream.Free;
  end;
end;
 
{$ENDIF SQLITE_OMIT_INCRBLOB}
 
//------------------------------------------------------------------------------
 
{ Read a BLOB back to a stream using the "traditional" method. See the comments
  on Write_Stream_to_Blob_Traditional for a discussion of its when to use it. }
procedure Read_Stream_from_Blob_Traditional(
  const AStream: TStream; const ARowID: Int64);
var
  Stmt: TDISQLite3Statement;
begin
  { Create a prepared statement, step into the first result row, and write the
    column's BLOB data to the stream. }
  Stmt := DB.Prepare('SELECT b FROM Blobs WHERE RowID = ?');
  try
    Stmt.Bind_Int64(1, ARowID);
    if Stmt.Step = SQLITE_ROW then
      AStream.Write(Stmt.column_blob(0)^, Stmt.Column_Bytes(0));
  finally
    Stmt.Free;
  end;
end;
 
//------------------------------------------------------------------------------
 
{$IFNDEF SQLITE_OMIT_INCRBLOB}
 
{ Read a BLOB back to a stream using the "incremental" method. See the comments
  on Write_Stream_to_Blob_Incremental for a discussion of its advantages. }
procedure Read_Stream_from_Blob_Incremental(
  const AStream: TStream; const ARowID: Int64);
var
  BlobStream: TDISQLite3IncrBlobStream;
begin
  { Instead of a prepared statement, directly create the BLOB stream and call
    the dest stream's CopyFrom method to read the BLOB data. }
  BlobStream := TDISQLite3IncrBlobStream.Create(
    DB, // The database connection.
    'main', // Database which contains the BLOB table.
    'Blobs', // BLOB table.
    'b', // BLOB column.
    ARowID, // RowID
    0); // 1 = Read and Write access, 0 = Read only.
  try
    AStream.CopyFrom(BlobStream, 0);
  finally
    BlobStream.Free;
  end;
end;
 
{$ENDIF SQLITE_OMIT_INCRBLOB}
 
//------------------------------------------------------------------------------
 
const
  DATABASE_NAME = 'BLOBs.db3';
var
  Stream: TStream;
  ID_Traditional{$IFNDEF SQLITE_OMIT_INCRBLOB}, ID_Incremental{$ENDIF}: Int64;
begin
  try
    DB := TDISQLite3Database.Create(nil);
    try
      { Create a new, empty database and a table with a simple BLOB field. }
      DB.DatabaseName := DATABASE_NAME;
      DB.CreateDatabase;
      DB.Execute('CREATE TABLE Blobs(i INTEGER PRIMARY KEY, b BLOB)');
 
      //------------------------------------------------------------------------
      // Insert TStream into a database BLOB.
      //------------------------------------------------------------------------
 
      Stream := TFileStream.Create(ParamStr(0), fmOpenRead or fmShareDenyWrite);
      try
        { Add the same stream twice, using both the traditional and the new
          incremental method to write BLOBs. Even though the methods are
          different, the BLOBs written are same and can be read back using
          either of the two methods. }
        ID_Traditional := Write_Stream_to_Blob_Traditional(Stream);
        {$IFNDEF SQLITE_OMIT_INCRBLOB}
        {  The "incremental" method is not available in DISQLite3 Personal. }
        ID_Incremental := Write_Stream_to_Blob_Incremental(Stream);
        {$ENDIF SQLITE_OMIT_INCRBLOB}
      finally
        Stream.Free;
      end;
 
      //------------------------------------------------------------------------
      // Extract BLOBs to a TStream.
      //------------------------------------------------------------------------
 
      { To demonstrate that the two BLOB methods can be used interchangingly,
        we read the data back with the other method than the one used for
        writing. }
 
      Stream := TFileStream.Create(
        ExtractFileName(ParamStr(0)) + '_trad' +
        ExtractFileExt(ParamStr(0)), fmCreate);
      try
        Read_Stream_from_Blob_Traditional(Stream,
          {$IFNDEF SQLITE_OMIT_INCRBLOB}
          ID_Incremental
          {$ELSE SQLITE_OMIT_INCRBLOB}
          ID_Traditional
          {$ENDIF SQLITE_OMIT_INCRBLOB});
      finally
        Stream.Free;
      end;
 
      {$IFNDEF SQLITE_OMIT_INCRBLOB}
      { The "incremental" method is not available in DISQLite3 Personal. }
      Stream := TFileStream.Create(
        ExtractFileName(ParamStr(0)) + '_incr' +
        ExtractFileExt(ParamStr(0)), fmCreate);
      try
        Read_Stream_from_Blob_Incremental(Stream, ID_Traditional);
      finally
        Stream.Free;
      end;
      {$ENDIF SQLITE_OMIT_INCRBLOB}
 
    finally
      DB.Free;
    end;
 
    WriteLn;
    WriteLn('Done - Press ENTER to Exit');
    ReadLn;
 
  except
    on e: Exception do
      begin
        WriteLn(e.Message);
        ReadLn;
      end;
  end;
end.