Add new FireDAC topic about blob streaming in XE7

Blob (binary large object) streaming is a stream wrapping DB API provided by FireDAC.

Note: All BLOB stream operations must be performed in a transaction (requirement of most DBMS API's).

Note: Because FireDAC has direct access to the stream containing data the memory usage is kept to a minimum when working with the underlying DBMS API (avoid multiple caching).

How was it done before and why was it disadvantageous:

1. The value was assigned to a parameter using TFDParam.LoadFromStream etc, and then stored in TFDParam.Value.
2. Then FireDAC stores the value in a buffer, binded to a low-level DB API.
3. The DB API may buffer the value internally before transferring to DB.

Result: memory requirement - 3-4 x BLOB value size, 1.5-2 times more slow, cannot use chunks.

Usage

The binary data stored in a Blob parameter can now be read directly from streams. There are two ways to handle this:

• External stream - The stream is provided by the application, FireDAC becomes responsible for handling the instance.

Note: External streams are supported for all DB's.

FDQuery1.SQL.Text := 'INSERT INTO tab (blobdata) VALUES (:blobdata)';
FDQuery1.Params[0].DataType := ftBlob;
FDQuery1.Params[0].AsStream := TFileStream.Create('data.bin', fmOpenRead);  // FireDAC takes ownership of the object
FDQuery1.ExecSQL;

• Internal stream - The stream is provided by the application, FireDAC receives only a copy of the stream.

Notes:

1. Internal streams are not supported for some DB's, if they have no API for streaming.
2. Internal streams usage is error prone and must be done very carefully. Release reference as early as possible.
3. Internal streams may not support Seek/Position/Size operations, depending on the DB.

FDQuery1.SQL.Text := 'INSERT INTO tab (blobdata) VALUES (:blobdata)';
FDQuery1.Params[0].DataType := ftStream;
FDQuery1.Params[0].StreamMode := smOpenWrite;
FDQuery1.ExecSQL;
oStr := TFileStream.Create('data.bin', fmOpenRead);
try
  FDQuery1.Params[0].AsStream.CopyFrom(oStr, -1);  // FireDAC receives a copy of the stream
finally
  oStr.Free;  // The user is responsible for freeing the original stream
end;

Supported drivers

Driver	External Streaming	Internal Streaming
DB2	Supported.	Supported.
Informix	Supported.	Supported.
InterBase	Supported.	Not supported.
Firebird	Supported.	Not supported.
MySQL	Supported. Note: Set the Datatype to ftBlob.	Not supported.
ODBC	Supported.	Supported.
Oracle	Supported.	Supported. Note: Requires special initialization of a BLOB/CLOB field: INSERT INTO tab (blobdata) VALUES (EMPTY_BLOB()) RETURNING blobdata {into :blobdata}
PostgreSQL	Supported. Note: Set the OidAsBlob connection parameter to Yes.	Supported. Note: Set the OidAsBlob connection parameter to Yes.
SQLite	Supported.	Supported.

• SQL Server FILESTREAM support is a very special case of BLOB streaming.

Example

Support for Blob real streaming, without buffering on client side. This includes the ability for an application to get an internal stream reference.

FDQuery.SQL.Text := 'select :p = BLOBField from tab where id = :id';
FDQuery.Params[0].DataType := ftStream;
FDQuery.Params[0].StreamMode := smOpenRead;
FDQuery.Params[1].AsInteger := 123;
FDQuery.OpenOrExecute;

// TFDParam.AsStream returns reference to internal low-level stream
FDQuery.Params[0].AsStream.Read(Buffer, Length(Buffer));

Samples

• Object Pascal/Database/FireDAC/Samples/Comp Layer/TFDQuery/BlobStreams
• Object Pascal/Database/FireDAC/Samples/DBMS Specific/MSSQL/FileStream