Delphi. FireDAC. BLOB

Example from project

shows writing in parent table and blob table

Theory from Embarcadero site

Embarcadero

The BLOB streaming technique implements BLOB parameter values streaming to / from a database. This allows to transfer a BLOB value “by reference” in contrast to transferring a BLOB value “by value”. The pros of this technique are:

  • The client side memory usage is minimized, comparing to the “by value” requirement for additional memory usage equal to 3-4 time of the BLOB value size;
  • The performance is 1.5-2 times better, comparing to the “by value” performance;
  • Ability to update a BLOB value “by chunks”, comparing to “by value” which allows to update a BLOB only in full.

The cons are:

  • The data type / encoding transformation for most databases is not performed;
  • It is supported only for command parameters, not for columns.

The APIs transferring a BLOB value “by value” are:

The BLOB streaming API includes:

Usage

FireDAC offers two kinds of BLOB streaming – External streams and Internal streams.

External Streams

An external stream is provided by the application to FireDAC (external to FireDAC). FireDAC will read / write this stream. External streams are supported for all DB’s.

To use external streaming, the application should:

  • Optionally set the parameter DataType to one of the BLOB data types, such as ftOraBlobftBlobftMemoftWideMemo. If it is not set, then the next assignment to AsStream property will implicitly set the parameter data type to ftStream.
  • Optionally set the parameter ParamType. This defines the stream transfer mode:
    • ptInput – the stream will be read and written to a DB BLOB value.
    • ptOutput – a DB BLOB value will be read and written to stream.
  • Assign a stream reference to parameter AsStream property. In this case, FireDAC becomes the owner of the stream reference. The object will be released after a query unpreparing, or after the next value assignment. Alternatively applications may use the parameter SetStream method to control the ownership. The stream will be used from the current stream position.
  • Execute the SQL command.

For example:

Internal Streams – Doesn’t work to mysql!

An internal stream is provided by FireDAC to the application (internal to FireDAC). The application will read / write this stream. An internal stream is a thin object wrapper for a DBMS BLOB streaming API. For that reason, the internal streams may be not supported for a DB if it has no API for BLOB streaming, or may have a limited functionality, such as non functional Seek / Position / Size methods if the DB has no API for these operations. See “Supported Drivers” for details.

The internal stream operations require:

  • Firstly to execute the SQL command. The internal stream may be accessible only after execution.
  • To be performed inside of an explicit transaction. This is the requirement for most DB APIs.
  • To release a reference to the internal stream as early as possible. This is because some operations, such as CommitUnprepare or CloseStreams may release the internal stream object.
  • Use explicit variables for internal streams on compilers that use Automatic Reference Counting (see the code below).

To use the internal streaming, the application should:

  • Start a transaction;
  • Set the parameter DataType to ftStream.
  • Set the parameter StreamMode to the required internal stream mode:
    • smOpenRead – to read the DB BLOB value;
    • smOpenWrite – to write the DB BLOB value;
    • smOpenReadWrite – to read and write the DB BLOB value.
  • Optionally set the parameter ParamType to the required parameter mode. Note that in contrast to external streaming, ParamType influences the internal stream reference initialization, and only the parameter StreamMode influences the stream opening mode.
  • Optionally set the parameter FDDataType to one of the BLOB data types, such as dtHBlobdtBlobdtHMemo or dtWideHMemo. The value by default is ftBlob or ftOraBlob, when FDDataType is not set.
  • Execute the SQL command. This will return the internal stream reference;
  • Read / write the internal stream reference;
  • Optionally call the dataset or command CloseStreams method to flush DB API buffers and close internal streams. This is mandatory for ODBC-based, InterBase and Firebird drivers, for other drivers it does nothing.
  • Finish the transaction.

For example:

SQL Server FILESTREAM

The SQL Sever FILESTREAM support is a special case of the internal streaming. Please read the SQL Server FILESTREAM documentation for server setup details.

For the table DDL, “data” is a FILESTREAM column and “rowguid” is a rowguidcol column. A table with a FILESTREAM column must have a rowguidcol column to identify streams.


For the SQL command fetching the data, the “data” column is excluded from the SELECT list, because that will transfer the FILESTREAM content “by value”, instead of using FILESTREAM streaming. Use instead a SQL command with a parameter, whose value will be set to “data.PathName()” on the server side.


 

To use FILESTREAM streaming in FireDAC

  1. Set the following parameters:
    • DataType to one of the following values:
      • ftBlob (to work with external stream or to transfer a BLOB value “by value”);
      • ftStream (to return an internal FILESTREAM stream);
    • FDDataType to dtHBFile;
    • ParamType to ptOutput;
    • StreamMode to one of the following values:
      • smOpenWrite (to write to FILESTREAM);
      • smOpenRead (to read from FILESTREAM);
      • smOpenReadWrite (to read/write from FILESTREAM);
  2. Start a transaction.
  3. Execute a SQL command returning a PathName() into the parameter, which will perform the BLOB streaming. Above settings are the special sign that the application is going to use internal streaming for this parameter.
  4. Finish the transaction.

For example:

123

Supported Drivers

Driver External Streaming Internal Streaming
Advantage Supported. Supported. Requires a call to CloseStreams. Set size to Full stream length before writing to a stream.
DataSnap Supported. Supported.
DB2 Supported. Supported. Requires a call to CloseStreams.
Informix Supported. Supported. Requires a call to CloseStreams.
InterBase Supported. Supported. Requires a call to CloseStreams.
Firebird Supported. Supported. Requires a call to CloseStreams.
MS Access Supported. Supported. Requires a call to CloseStreams.
MS SQL Server Supported. Supported. Requires a call to CloseStreams for non FILESTREAM data types. The FILESTREAM requires special handling.
MySQL Supported. Set the Datatype to ftBlob. Not supported.
ODBC Supported. Supported. Requires a call to CloseStreams.
Oracle Supported. Set the Datatype to ftOraBlob ot ftOraClob. Supported. Set the Datatype to ftOraBlob ot ftOraClob. Requires special initialization of a BLOB/CLOB field on insertion:

PostgreSQL Supported. Supported. Set the OidAsBlob connection parameter to Yes.
SQLite Supported. Not supported.
SQL Anywhere Supported. Supported. Requires a call to CloseStreams.
Teradata Database Supported. Supported. Requires a call to CloseStreams.
This entry was posted in Delphi. Bookmark the permalink.