wiki:sqlite3:disqlite3_blobs_and_streams
Delphi 12 Athens Updates Available!
To download, click your product: DIContainers, DIConverters, DICreole, DIFileFinder, DIGoogleReader, DIHtmlLabel, DIHtmlParser, DIMime, DIRegEx, DISQLite3, DITidy, DIUcl, DIUnicode, DIXml, YuBrotli, YuImage, YuNetSurf, YuOpenSSL, YuPcre2, YuPdf, YuStemmer, YuXmlSec, YuZip.
To download, click your product: DIContainers, DIConverters, DICreole, DIFileFinder, DIGoogleReader, DIHtmlLabel, DIHtmlParser, DIMime, DIRegEx, DISQLite3, DITidy, DIUcl, DIUnicode, DIXml, YuBrotli, YuImage, YuNetSurf, YuOpenSSL, YuPcre2, YuPdf, YuStemmer, YuXmlSec, YuZip.
Differences
This shows you the differences between two versions of the page.
— | wiki:sqlite3:disqlite3_blobs_and_streams [2020/08/28 11:42] (current) – created - external edit 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== DISQLite3: BLOB and TStream example ====== | ||
+ | {{page>: | ||
+ | {{page>: | ||
+ | |||
+ | <WRAP tip>An updated version of this project is distributed with the DISQLite3 package.</ | ||
+ | |||
+ | <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:// | ||
+ | |||
+ | Copyright (c) 2005-2017 Ralf Junker, The Delphi Inspiration < | ||
+ | |||
+ | ------------------------------------------------------------------------------ } | ||
+ | |||
+ | program DISQLite3_Blob_and_Streams; | ||
+ | |||
+ | {$APPTYPE CONSOLE} | ||
+ | {$I DI.inc} | ||
+ | {$I DISQLite3.inc} | ||
+ | |||
+ | uses | ||
+ | SysUtils, Classes, DISQLite3Api, | ||
+ | |||
+ | var | ||
+ | DB: TDISQLite3Database; | ||
+ | |||
+ | { Use the " | ||
+ | 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 " | ||
+ | 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(' | ||
+ | try | ||
+ | { Optimize memory useage for TCustomMemoryStream. } | ||
+ | if AStream is TCustomMemoryStream then | ||
+ | begin | ||
+ | { If AStream descends from TCustomMemoryStream, | ||
+ | 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, | ||
+ | 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, | ||
+ | AStream.Read(p^, | ||
+ | { 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, | ||
+ | end; | ||
+ | Stmt.Step; | ||
+ | Result := DB.LastInsertRowID; | ||
+ | finally | ||
+ | Stmt.Free; | ||
+ | end; | ||
+ | end; | ||
+ | |||
+ | // | ||
+ | |||
+ | {$IFNDEF SQLITE_OMIT_INCRBLOB} | ||
+ | |||
+ | { The " | ||
+ | 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 " | ||
+ | makes it a little less flexible to work with. | ||
+ | |||
+ | The " | ||
+ | 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 " | ||
+ | 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(' | ||
+ | try | ||
+ | Stmt.Bind_ZeroBlob(1, | ||
+ | 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. | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | Result, // RowID | ||
+ | 1); // 1 = Read and Write access, 0 = Read only. | ||
+ | try | ||
+ | BlobStream.CopyFrom(AStream, | ||
+ | finally | ||
+ | BlobStream.Free; | ||
+ | end; | ||
+ | end; | ||
+ | |||
+ | {$ENDIF SQLITE_OMIT_INCRBLOB} | ||
+ | |||
+ | // | ||
+ | |||
+ | { Read a BLOB back to a stream using the " | ||
+ | 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' | ||
+ | Stmt := DB.Prepare(' | ||
+ | try | ||
+ | Stmt.Bind_Int64(1, | ||
+ | if Stmt.Step = SQLITE_ROW then | ||
+ | AStream.Write(Stmt.column_blob(0)^, | ||
+ | finally | ||
+ | Stmt.Free; | ||
+ | end; | ||
+ | end; | ||
+ | |||
+ | // | ||
+ | |||
+ | {$IFNDEF SQLITE_OMIT_INCRBLOB} | ||
+ | |||
+ | { Read a BLOB back to a stream using the " | ||
+ | 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' | ||
+ | BlobStream := TDISQLite3IncrBlobStream.Create( | ||
+ | DB, // The database connection. | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ARowID, // RowID | ||
+ | 0); // 1 = Read and Write access, 0 = Read only. | ||
+ | try | ||
+ | AStream.CopyFrom(BlobStream, | ||
+ | finally | ||
+ | BlobStream.Free; | ||
+ | end; | ||
+ | end; | ||
+ | |||
+ | {$ENDIF SQLITE_OMIT_INCRBLOB} | ||
+ | |||
+ | // | ||
+ | |||
+ | const | ||
+ | DATABASE_NAME = ' | ||
+ | var | ||
+ | Stream: TStream; | ||
+ | ID_Traditional{$IFNDEF SQLITE_OMIT_INCRBLOB}, | ||
+ | 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(' | ||
+ | |||
+ | // | ||
+ | // Insert TStream into a database BLOB. | ||
+ | // | ||
+ | |||
+ | Stream := TFileStream.Create(ParamStr(0), | ||
+ | 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 " | ||
+ | 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)) + ' | ||
+ | ExtractFileExt(ParamStr(0)), | ||
+ | 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 " | ||
+ | Stream := TFileStream.Create( | ||
+ | ExtractFileName(ParamStr(0)) + ' | ||
+ | ExtractFileExt(ParamStr(0)), | ||
+ | try | ||
+ | Read_Stream_from_Blob_Incremental(Stream, | ||
+ | finally | ||
+ | Stream.Free; | ||
+ | end; | ||
+ | {$ENDIF SQLITE_OMIT_INCRBLOB} | ||
+ | |||
+ | finally | ||
+ | DB.Free; | ||
+ | end; | ||
+ | |||
+ | WriteLn; | ||
+ | WriteLn(' | ||
+ | ReadLn; | ||
+ | |||
+ | except | ||
+ | on e: Exception do | ||
+ | begin | ||
+ | WriteLn(e.Message); | ||
+ | ReadLn; | ||
+ | end; | ||
+ | end; | ||
+ | end. | ||
+ | </ |