Database Operations

CREATE an entity set or relationship with fields with a blob

You can use DC to create an entity set or relationship with fields that will hold a blob. However, DC does not recognize the field type "blob"; therefore, define everything you need using DC, but in place of a "blob", use any other field type.

The following rules must be considered:

a) Blobs fields cannot be virtual fields;

b) The length of a blob field is ignored by Zim;

c) A blob field can be indexed but it does not make much sense as it only returns the size of the blob;

d) It is not recommended not to set a blob field as required;

Although an entity set or relationship with fields containing a blob field is a normal table in all aspects, it is recommended that it would only contain the bare minimum fields to allow basic operations, like:

a) a primary key to directly retrieve and update records;

b) the blob field itself.

After the definition in DC is complete, from the Zim prompt, change the field type you want to "blob" this way:

change 1 Fields where OwnerName="MyEnt" and FieldName="MyBlob" let Type="blob"
zomrecreate MyEnt

where MyEnt is the name of the entity set being created, MyBlob is a blob field and MyKey is a primary key to MyEnt.

ADD records with blobs

The data source for a blob field is always assumed to be an operating system file, like these examples:

add 1 MyEnt let MyKey=1 MyBlob="c:\\adir\\bdir\\image.jpg"

add 1 MyEnt let MyKey=2 MyBlob="c:\\adir\\bdir\\budget.xls"

add 1 MyEnt let MyKey=3 MyBlob="c:\\adir\\bdir\\lastparty.avi"

add 1 MyEnt let MyKey=4 MyBlob=$null

Evidently, the full file path of the file can be provided using an expression or a variable that, when converted to a blob, results in a character expression with a valid operating system file name.

Note that the last statement does not fill the blob field with data but rather defines it with a null value.

LIST records

The result of a blob field is always its size as seen when listing the records:

list all MyEnt

MyKey        MyBlob
1               123456
2                 35000
3            12345678
4  

The numbers 123456, 35000 and 12345678 are the physical sizes of the files added in the previous ADD statements. The last line has no size because the blob field is null.

FIND records and retrieve a blob

A record can be retrieved in the usual way but the contents of the blob field can be unloaded to an operating system file for later use using a special function called $fromblob, like this example:

find MyEnt where MyKey=3 evaluate (let Variable=$fromblob(MyBlob,"c:\\afile.avi"))

find Customers CustMyEnt MyEnt where CC=3 evaluate (let Variable=$fromblob(MyBlob,"c:\\afile.avi"))

In the first example, it finds the record number 3 and then unloads the blob field into the specified file.

In the second example, Customer Code number 3 is retrieved and then the relationship is established to MyEnt in order to retrieve the corresponding image stored in the blob field.

CHANGE blob records

Records containing a blob field can be changed in the usual way with the difference that if the blob field is touched, certain actions are taken depending on the situation presented in this table:

Examples:

change 1 MyEnt where MyKey=1 let MyBlob=$null

change 1 MyEnt where MyKey=2 let MyBlob="c:\\anotherfile.doc"

DELETE blob records

The DELETE operation is performed in two steps:

1) the record describing the blob is deleted;

2) the blob itself is removed if it is not $Null.

Example:

delete MyEnt where MyKey=3