BLOBs (binary large object) are very large, variable length, binary or character data objects. BLOBS are typically documents (such as .pdf, .doc) or pictures (such as .jpeg, .gif, .bmp).
NOTE: Related terms, LOB (large object) and CLOB (character large object in Oracle), are used to refer to large objects consisting entirely of text data.
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. Storage on disks used in Web server farms is typically less expensive than on database SANs.
- 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.
Additionally, 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.
Driver Support
When a VDF 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.
New Commands
Mertech added new commands to allow you to take advantage of reading and writing BLOB types either to the database or to the file system.
LOB_APPEND
Append the existing field buffer value into the assigned LOB column, preserving the existing LOB column data.
LOB_LENGTH
Retrieve the total length from a given LOB column
LOB_WRITE
Write the existing field buffer value into the assigned LOB column, overwriting the existing LOB column data.
LOB_READ
Read a chunk of data from a LOB column moving the read value into the assigned file field buffer. Since the chunk data is going to be moved into a file field buffer, the actual read value length cannot exceed the field buffer length.
LOB_LOADFROMFILE
Load an external file into a LOB column. When using LOB_LOADFROMFILE with the Oracle driver, the input file must be available from the Oracle database, because the load task will be done by the database. To use this command you must supply the alias for the created directory object.
LOB_ERASE
Erase a portion of data from a LOB column. A DF_TEXT field is erased by filling it with blanks. A DF_BINARY field is erased by filling it with 0x0.
LOB_TRUNCATE
Truncate data from a LOB column to a given length.
FLUSH_LOB_BUFFER (MySQL only)
Send the data that was loaded into an LOB column to the database. This is necessary due to MySQL’s architecture.
An example of using LOB commands
// Assumes the 13th field of customel is type TEXT or BINARY and named comments
Local Integer iLength
open "customel.int" as customer
clear customer
find gt customer by index.1
// Delete any lob data in the field, and fill it up with the contents of a file
Lock
LOB_TRUNCATE of customer 13 to 0
LOB_LOADFROMFILE of customer 13 FROM "." "document.pdf"
Unlock
Find Eq customer by Recnum
Direct_Output 'Binary: document-copy.pdf'
// Retrieve the chunk length and the first chunk of data into the field buffer
LOB_LENGTH of customer 13 to iLength
LOB_READ OF customer 13 First iLength
Write customel.comments
// Loop through and retrieve the following chunks and write them to a file
While (iLength > 0)
LOB_READ of customer 13 Next iLength
Write customer.comments
Loop
For more details on LOB handling and the commands, please consult Flex2SQL v11 Programmers Guide.
Mertech: Mar 29, 2024 8:53:00 AM
Riaz Merchant