Chapter 11. System Packages

System packages provide utility stored functions and stored functions.

List of System Packages
Section 11.1, “RDB$BLOB_UTIL”

Utilities for blob manipulation

Section 11.2, “RDB$PROFILER”

Profiler

Section 11.3, “RDB$TIME_ZONE_UTIL”

Time zone utilities

11.1. RDB$BLOB_UTIL

Package of functions and procedures for blob manipulation

11.1.1. 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.

11.1.2. 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.

11.1.3. 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.

11.1.4. 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 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).

11.1.5. 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

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.

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

11.1.6. 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

11.1.7. 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

11.1.8. 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 ;!