Delphi Inspiration

Components and Applications

User Tools

Site Tools


wiki:sqlite3:disqlite3_blobs_and_streams

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

wiki:sqlite3:disqlite3_blobs_and_streams [2016/01/22 15:09] (current)
Line 1: Line 1:
 +====== DISQLite3: BLOB and TStream example ======
  
 +{{page>:​products:​sqlite3:​header}}
 +{{page>:​wiki-header}}
 +
 +<WRAP tip>An updated version of this project is distributed with the DISQLite3 package.</​WRAP>​
 +
 +<code delphi>
 +{ 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:
 +
 +    http://​www.yunqa.de/​delphi/​
 +
 +  Copyright (c) 2005-2007 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.
 +</​code>​
wiki/sqlite3/disqlite3_blobs_and_streams.txt · Last modified: 2016/01/22 15:09 (external edit)