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:

• TFDParam.Value;
• TFDParam.AsBlob;
• TFDParam.LoadFromStream, etc.

The BLOB streaming API includes:

• TFDParam.AsStream;
• TFDParam.AsStreams.

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 ftOraBlob, ftBlob, ftMemo, ftWideMemo. 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 Commit, Unprepare 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 dtHBlob, dtBlob, dtHMemo 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.

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