Support for Blob Streaming in FireDAC

Go Up to Working with Commands (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;
• 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:

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

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.
• 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:

FDConnection1.StartTransaction;
try
  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
    // Database receives a copy of the original stream
    {$IFDEF AUTOREFCOUNT}
      oInt := FDQuery.Params[0].AsStream;
      oInt.CopyFrom(oStr, -1);
      oInt := nil;   
    {$ELSE}
      FDQuery1.Params[0].AsStream.CopyFrom(oStr, -1);
    {$ENDIF}
  finally
    // The user is responsible for freeing the original stream 
    oStr.Free;
  end;
  FDQuery1.CloseStreams;
  FDConnection1.Commit;
except
  FDConnection1.Rollback;
  raise;
end;

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.

create table FSTab (
  id int IDENTITY(1,1) NOT NULL Primary Key,
  name varchar(100) NOT NULL,
  data varbinary(max) filestream NULL,
  rowguid uniqueidentifier NOT NULL rowguidcol unique DEFAULT (newid())
)

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.

select :p = data.PathName() from FSTab where id = :id

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:

FDConnection1.StartTransaction;
try
  FDQuery.SQL.Text := 'select :p = data.PathName() from FSTab where id = :id';
  FDQuery.Params[0].DataType := ftStream;
  FDQuery.Params[0].FDDataType := dtHBFile;
  FDQuery.Params[0].ParamType := ptOutput;
  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));
  FDConnection1.Commit;
except
  FDConnection1.Rollback;
  raise;
end;

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: INSERT INTO tab (blobdata) VALUES (EMPTY_BLOB()) RETURNING blobdata {into :blobdata}
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.

See Also

• FireDAC
• Using Streams
• Using File Streams

Samples

• FireDAC TFDQuery Blobs Sample
• FireDAC TFDQuery BlobStreams Sample
• Object Pascal/Database/FireDAC/Samples/DBMS Specific/MSSQL/FileStream