Binary large objects in KCML databases

Binary Large Objects or BLOBs are streams of binary data of arbitrary size. They can be stored in KCML databases where they are not held inline in the rows directly but instead are referenced via a BLOB Identifier or BID which is stored in the column. A BID is a 6 byte opaque token which is used internally to retrieve the actual data but is not exposed outside the database. Instead the FLD in the database row is used to hold the SYM of an associated string variable that will be filled in on a read or read from in a write. Thus the column width is always 6 and should have the numeric FLD datatype BLOBSYM.

There can be more than one BLOB column per row (up to a maximum of 8) but one BLOB cannot be associated with more than one row or more than one column in a row. BLOB columns cannot be used in a an index. BLOBs were introduced in KCML 6.00 with KDB type 7 tables and are also supported for Oracle and PostgreSQL tables.

While the BID column can generally be considered opaque, a BID of ALL(00), the result of packing from a SYM of zero, has special meaning as it represents a NULL BLOB. This is the default value for a BLOBSYM column. Setting the field to ALL(00) before a read will not fetch the BLOB for the column and is recommended for performance reasons if the BLOB contents are not required. However setting it to ALL(00) before a KI_WRITE will write a zero length NULL BLOB in the database. Such a BLOB occupies no space. When read back a NULL BLOB will result in the associated string being set to zero length. You can also write a NULL BLOB by setting the column to the SYM of a zero length string. A BID of ALL(00) on a KI_REWRITE ignores the column and its contents are left unchanged.

When BLOBs are retrieved they are automatically REDIMed to exactly the right size thus it is essential to ensure that the bound variable was the right size when the data was inserted. Often the best way to do this is to use REDIM LET which will fix the size of the variable to exactly the size of the left hand side of the LET. If bound against a string array and the BLOB is a different size to the size of the array then the array will be REDIMed to have only one element of the right length.

The contents of a BLOBSYM column in the database row buffer are unaffected by a database operation.

The packing format used for a BLOBSYM is checked in each database call and invalid values such as ALL(FF) or ALL(20) will be treated as ALL(00). Using an out of scope buffer will provoke a KE_BADBUFFER error.

Using BLOBs

When creating the table with CREATE TABLE use a datatype of BLOB as in:

 CREATE TABLE Movies (FILM VARCHAR(30), RELDATE DATE, STAR VARCHAR(30), TRAILER BLOB)

There is a predefined FLD datatype of BLOBSYM to represent the BLOB column in the database row. The above CREATE TABLE corresponds to a row definition of

DEFRECORD Movies
	FLD SKIP(1)
	FLD Film = "CHAR(30)"
	FLD Reldate = "DATE"
	FLD Star$ = "CHAR(30)"
	FLD Trailer = "BLOBSYM"
END RECORD

To access a trailer clip we might bind the BLOB this way:

DIM clip$0,row$_Movies
FLD(row$.Trailer) = SYM(clip$)
CALL KI_READ h, "The Matrix", 1, SYM(row$) TO s

The database is unaware of what the BLOB actually represents and the database designer may need other columns to store that information. In the example above the BLOB might have been MPEG data but the database just sees binary bytes and if passed to an application by the data binding, it is up to the programmer to invoke an appropriate media player.

CLOB character BLOBs

BLOBs are binary objects with a particular length. Trailing spaces must not be added or stripped. However sometimes the data stored in the BLOB is character oriented, for example an XML document, and the normal KCML space handling is appropriate. This is possible by using the SQL data type of CLOB which tells KCML to strip trailing spaces from the bound variable before an insert and to pad the variable with trailing spaces appropriately on a read operation if the data is smaller than the size of the variable. The variable will still be REDIMed to exactly the right size if it was too small for the CLOB.

BLOBs and KI_WRITE_RAW

You can update a table with BLOBs using KI_WRITE_RAW provided the buffer is sized for only 1 row.

Testing for BLOBs

BLOB columns are always included in the query sent to the database, provided they are in the view for the row buffer. KCML will make extra round trips to the database to get the size and contents of each bound BLOB in turn which can be expensive. If you are not normally interested in the blob contents and only need to know if a BLOB exists then you should set the BLOBSYM column to zero before the read and check its value afterwards. If the column value is still zero then there was no BLOB associated with that column but if it has the special marker value of _KDB_BLOB_FOUND then there was a BLOB of a non-zero size. You will need to rebind and reread the row to get the BLOB contents.

Binding BLOBs to variables

For compatibility an earlier form of binding is still supported. In this BLOBs are managed by binding them to string variables, ideally scalar string variables, using KI_BIND_COL. For instance

DIM clip$0
CALL KI_BIND_COL h, "TRAILER", SYM(clip$) TO s
CALL KI_READ h, "The Matrix", 1, SYM(row$) TO s

Bound columns will be updated by KI_WRITE, KI_REWRITE and KI_WRITE_PTR. The bound variables will be updated by KI_FETCH, KI_READ, KI_READ_NEXT and KI_READ_PTR.

Compatibility notes

BLOBs were introduced with KDB type 7 tables in KCML 6.00. They were supported for Oracle tables with the koracle8 library in KCML 6.60. The distinction between BLOBs and CLOBs was made when the CLOB type was introduced in KCML 6.60. Prior to that all BLOBs had CLOB semantics. The KI_BLOB_GET and KI_BLOB_SET CALLs for accessing BLOB data directly using the BID were dropped in KCML 6.60 as they were not atomic actions.

The use of KI_BIND_COL and the field type of BID were deprecated as of KCML 6.68 and FLDs of type BLOBSYM are to be preferred. However if this binding is used then it takes precedence so the contents of BLOB columns are not inspected.