Binary Large Objects (BLOBs)¶

MS SQL Server¶

There are special considerations when using MS SQL Server. If you use embedded SQL and access LOB columns, you have to specify a server side cursor in your call to SQL_PREPARE_STMT. MS SQL Server enforces cursor limitations on LOB columns and transactions.

MySQL & MariaDB¶

By default, the max_allowed_packet size attribute is set to 16MB for MySQL 8.x and MariaDB 10.x.

max_allowed_packet=16M

To use the improved LOB commands, you must edit the my.ini file and set this attribute to at least 32MB.

max_allowed_packet=32M

PostgreSQL¶

The BLOB implementation in PostgreSQL uses the Large Object feature. When a BLOB is created, and an OID (object identifier) is saved in a column in your table and the BLOB is stored separately. Physically an OID is an unsigned 32 bit integer that is a reference to a binary file object.

The contents referenced by an OID cannot be read using any SQL functions or directives. In order to read the contents of the BLOB file, one has to read the OID from a database record, open the BLOB file, do the reading and then close it.

The OID field has to be nullable. A NULL value means an empty BLOB. If you write something into that BLOB using LOB_WRITE or LOB_APPEND, the driver automatically creates a new LOB object and writes its OID into the corresponding database field.

There are several significant things that differ in the PostgreSQL BLOB implementation from all other drivers:

• BLOB columns cannot be converted from regular text columns like VARCHAR etc.

• You cannot read the data stored within BLOBs using regular SAVE_RECORD / UPDATE_RECORD calls. You can only manipulate raw OIDs themselves.

• All BLOB related calls must happen within a transaction even if no modifications are performed. In each BLOB API call the driver checks if there is a transaction in effect. If not, it starts it and commits it after the call.

MERTECH_WARNING_MESSAGE DISABLED
Open "CUSTOMER.INT" as CUSTOMER
Move CUSTOMER.File_Number to iFile
Structure_Start iFile "MDSPGSQL
    Move 13 to iField // CUSTOMER.COMMENTS, note this is the last column in the SQL table
    Delete_Field iFile iField
    Create_Field iFile At iField
    Set_Attribute DF_FIELD_NAME of iFile iField to "COMMENTS
    Set_Attribute DF_FIELD_TYPE of iFile iField to DF_ASCII
    Set_Attribute DF_FIELD_NATIVE_TYPE of iFile iField to ePgSQL_OID
    Set_Attribute DF_FIELD_LENGTH of iFile iField to 8016
    Set_Attribute DF_FIELD_NULL of iFile iField to 1
Structure_End iFile DF_STRUCTEND_OPT_NONE "." 0
Close CUSTOMER

Procedure LoadImage
    Integer iImageLen
    Integer iOldVal iChunk iSize ISoFar iChunksize
    Move 2097152 to ChunkSize // 2 MB chunks
    SQL_GET_LOB_LENGTH salesp.icture to iImageLen
    If (iImageLen > 0) Begin
        Get_Argument_Size to iOldVal
        Set_Argument_Size iImageLen
        String sChunk SBitmap
        UChar[] ucFileAs Array
        Address pStr
        //The Repeat loop is shown below to demonstrate how chunking works. This loop could //have been replaced with a call to SQL_GET_LOB salesp.picture sBitmap
        Repeat
            SQL_GET_LOB_CHUNK salesp.picture to sChunk OFFSET (iChunkSize \* iChunk) Length iChunkSize
            Move (sBitmap + sChuck) to sBitmap
            Increment iChunk
            Add iChunkSize to iSoFar
        Until (iSoFar > iImageLen
        //Code follows to convert the image from sBitmap to the type expected by the image control
    end
End_Procedure