Support for Blob Streaming in FireDAC

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;

Usage

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.

Internal Streams

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.

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.

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.

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.