Binary Large Objects (BLOBs)

BLOBs are very large, variable length, binary or character data objects. BLOBS are typically used to store documents, pictures, or other variably structured (or unstructured) items.


Related terms, LOB (large object) and CLOB (character large object in Oracle), are used to refer to large objects consisting entirely of text data.

When a DataFlex text field is too large for the native type, the Mertech drivers automatically use the BLOB data type during the migration. For example in MS SQL Server the driver automatically switches from varchar (<string length>) to varchar (max) when database limitations are hit.

As long as the size of the object fits in a regular DataFlex string, the object is bound as any regular TEXT column. The Mertech drivers transparently handle this, defaulting to non-BLOB data types if possible.


Mertech suggests using non-BLOB data types, whenever possible, if designing database tables outside of Mertech’s tools. For example, when using available SQL modeling tools to manage your database structure.

BLOBs can either be stored inside the database or in the file system. Storing BLOB data in the database offers a number of advantages:

  • Security measures used to control access to the database can also be used to control access to BLOBs.

  • Backup routines used to backup the database can be used to capture BLOB data as well, simplifying maintenance.

  • Transaction control, if required, is built into the database.

  • Search operations can be performed against formatted text-based data contained within BLOB fields—for example, Microsoft Word or Microsoft Excel documents.

The file system may be a better storage choice for BLOBs when:

  • The object requires significant overhead to process, e.g., streaming video.

  • The cost of storage space is a consideration.

  • The object will be modified frequently. The file system may handle fragmentation better than the database server.

For all databases, there is a slight performance penalty fetching BLOB data, because BLOB data is not stored in the same internal page as the rest of the record.

MySQL & MariaDB

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


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



The MySQL Configuration Wizard places the my.ini file in the MySQL server installation directory.

To access a BLOB column you need to use the f2s_lob_get and f2s_lob_set commands. The Dataflex type used to hold data is a uChar[] variable. You can use the f2s_log_get_length to determine the size of the data so that you can preallocate a buffer for it. The f2s_lob_set_null command will let you reset a BLOB column on a record to null. Note that these commands should be used just like a move command, meaning that you need to use a reread/unlock or another type of transaction along with a saverecord.

When dealing with BLOB column in embedded SQL, no special handling or commands are necessary. Work these columns as you would any other column. Because they are treated in this way, if a row has an especially large BLOB value, the driver will try to allocate enough memory to hold the entire value.