Differences

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

@@ 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:
+    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 = 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
+); // 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>