FirebirdSQL logo
 New In Firebird 5.0Changes to the Firebird API and ODS 

RDB$BLOB_UTIL package

Adriano dos Santos Fernandes

Tracker ticket: #281

This package provides procedures and functions to manipulate BLOBs in a way that standard Firebird functions, like BLOB_APPEND and SUBSTRING, cannot do or are very slow.

These routines operate on binary data directly, even for text BLOBs.

Package routines

Function NEW_BLOB

RDB$BLOB_UTIL.NEW_BLOB creates a new BLOB SUB_TYPE BINARY.It returns a BLOB suitable for data appending, similar to BLOB_APPEND.

The advantage over BLOB_APPEND is that it’s possible to set custom SEGMENTED and TEMP_STORAGE options.

BLOB_APPEND always creates BLOBs in temporary storage, which may not always be the best approach if the created BLOB is going to be stored in a permanent table, as this will require a copy operation.

The BLOB returned from this function, even when TEMP_STORAGE = FALSE, may be used with BLOB_APPEND for appending data.

Input parameters

  • SEGMENTED type BOOLEAN NOT NULL

  • TEMP_STORAGE type BOOLEAN NOT NULL

Return type

BLOB SUB_TYPE BINARY NOT NULL

Function OPEN_BLOB

RDB$BLOB_UTIL.OPEN_BLOB opens an existing BLOB for reading.It returns a handle (an integer bound to the transaction) suitable for use with other functions of this package, like SEEK, READ_DATA and CLOSE_HANDLE.

Handles which are not explicitly closed are closed automatically when the transaction ends.

Input parameter

  • BLOB type BLOB NOT NULL

Return type

INTEGER NOT NULL

Function IS_WRITABLE

RDB$BLOB_UTIL.IS_WRITABLE returns TRUE when a BLOB is suitable for data appending using BLOB_APPEND without copying.

Input parameter

  • BLOB type BLOB NOT NULL

Return type:

BOOLEAN NOT NULL

Function READ_DATA

RDB$BLOB_UTIL.READ_DATA reads chunks of data of a BLOB handle opened with RDB$BLOB_UTIL.OPEN_BLOB.When the BLOB is fully read and there is no more data, it returns NULL.

If LENGTH is passed with a positive number, it returns a VARBINARY with its maximum length.

If LENGTH is NULL it returns just a segment of the BLOB with a maximum length of 32765.

Input parameters

  • HANDLE type INTEGER NOT NULL

  • LENGTH type INTEGER

Return type

VARBINARY(32765)

Function SEEK

RDB$BLOB_UTIL.SEEK sets the position for the next READ_DATA, it returns the new position.

MODE may be 0 (from the start), 1 (from current position) or 2 (from end).

When MODE is 2, OFFSET should be zero or negative.

Input parameters

  • HANDLE type INTEGER NOT NULL

  • MODE type INTEGER NOT NULL

  • OFFSET type INTEGER NOT NULL

Return type

INTEGER NOT NULL

Note

SEEK only works on stream blobs.Attempting to seek on a segmented blob results in error “invalid BLOB type for operation”.

Procedure CANCEL_BLOB

RDB$BLOB_UTIL.CANCEL_BLOB immediately releases a temporary BLOB, like one created with BLOB_APPEND.

If the same BLOB is used after cancel, an “invalid blob id” error will be raised.

Input parameter

  • BLOB type BLOB

Procedure CLOSE_HANDLE

RDB$BLOB_UTIL.CLOSE_HANDLE closes a BLOB handle opened with RDB$BLOB_UTIL.OPEN_BLOB.

Handles which are not explicitly closed are closed automatically when the transaction ends.

Input parameter

  • HANDLE type INTEGER NOT NULL

Examples

Create a BLOB in temporary space and return it in EXECUTE BLOCK
execute block returns (b blob)
as
begin
    -- Create a BLOB handle in the temporary space.
    b = rdb$blob_util.new_blob(false, true);

    -- Add chunks of data.
    b = blob_append(b, '12345');
    b = blob_append(b, '67');

    suspend;
end
Open a BLOB and return chunks of it with EXECUTE BLOCK
execute block returns (s varchar(10))
as
    declare b blob = '1234567';
    declare bhandle integer;
begin
    -- Open the BLOB and get a BLOB handle.
    bhandle = rdb$blob_util.open_blob(b);

    -- Get chunks of data as string and return.

    s = rdb$blob_util.read_data(bhandle, 3);
    suspend;

    s = rdb$blob_util.read_data(bhandle, 3);
    suspend;

    s = rdb$blob_util.read_data(bhandle, 3);
    suspend;

    -- Here EOF is found, so it returns NULL.
    s = rdb$blob_util.read_data(bhandle, 3);
    suspend;

    -- Close the BLOB handle.
    execute procedure rdb$blob_util.close_handle(bhandle);
end
Seek in a blob
set term !;

execute block returns (s varchar(10))
as
    declare b blob;
    declare bhandle integer;
begin
    -- Create a stream BLOB handle.
    b = rdb$blob_util.new_blob(false, true);

    -- Add data.
    b = blob_append(b, '0123456789');

    -- Open the BLOB.
    bhandle = rdb$blob_util.open_blob(b);

    -- Seek to 5 since the start.
    rdb$blob_util.seek(bhandle, 0, 5);
    s = rdb$blob_util.read_data(bhandle, 3);
    suspend;

    -- Seek to 2 since the start.
    rdb$blob_util.seek(bhandle, 0, 2);
    s = rdb$blob_util.read_data(bhandle, 3);
    suspend;

    -- Advance 2.
    rdb$blob_util.seek(bhandle, 1, 2);
    s = rdb$blob_util.read_data(bhandle, 3);
    suspend;

    -- Seek to -1 since the end.
    rdb$blob_util.seek(bhandle, 2, -1);
    s = rdb$blob_util.read_data(bhandle, 3);
    suspend;
end!

set term ;!
Check if blobs are writable
create table t(b blob);

set term !;

execute block returns (bool boolean)
as
    declare b blob;
begin
    b = blob_append(null, 'writable');
    bool = rdb$blob_util.is_writable(b);
    suspend;

    insert into t (b) values ('not writable') returning b into b;
    bool = rdb$blob_util.is_writable(b);
    suspend;
end!

set term ;!