Binary Data in the DBMS

Since BBj 5.0, the BASIS DBMS can store binary data as BLOBs (Binary Large Objects). Storing binary data as a BLOB instead of as a CHARACTER field allows for ODBC and JDBC applications to interpret the data correctly. BLOB data can also be larger than CHARACTER data.

Notes on Using BLOBs

  1. Do not use BLOBs as any part of a key or index. The file system will allow it, but the results are unpredictable. The native data format for BLOB values is not sortable, which means that iterating on a key with a BLOB value will not come back in any particular order.

  2. Always use BLOB for storing binary data of any size if a third party ODBC or JDBC application will access the data.

  3. Allow for length_of_BLOB + 4 bytes for each BLOB field in a record. A BLOB consists of a 4-byte length indicator plus the bytes.

  4. Make the BLOB only as big as necessary. Most BBj file types, with the exception of JKEYED files, do not support true variable-length records. Each record in the file uses the maximum record length, even if the BLOB data only takes up a portion of the record.

Example

Copy
Binary Data Example
rem /**
rem  * This sample code reads the contents of a binary file such as a PDF, PNG, etc.
rem  * and stores it as a column value in a record in a VKEYED file using SQL.
rem  * It uses a combination of Java, the JDBC API, and BBj in order to elegantly 
rem  * handle the binary data. 
rem  */

use java.io.FileInputStream
use java.io.FileOutputStream
use org.apache.commons.io.IOUtils

db$ = "ChileCompany"
user$ = "admin"
pwd$ = "admin123"
sourceFile$ = "/path/to/my/document.pdf"
destFile$ = "/path/to/my/document-copy.pdf"

REM If this is larger than 65K, it must be an enhanced format database.
REM Enhanced format databases can store very large files.
maxBlobSize = 65000

REM Create a connection to the DB using the JDBC API
con! = BBjAPI().getJDBCConnection(db$, user$, pwd$)

REM Create the table but if it fails, we assume it is already present so skip ahead
stmt! = con!.prepareStatement("create table blob_test (id integer primary key, blob_val blob(" + str(maxBlobSize) + ")) vkeyed", err=skipCreate)
stmt!.execute(err=skipCreate)
stmt!.close()

skipCreate:
REM Clear out the table from any previous runs for this test
stmt! = con!.prepareStatement("delete from blob_test")
stmt!.execute()
stmt!.close()

REM Prepare the INSERT statement using a parameter which will be set
REM in the next call.
stmt! = con!.prepareStatement("insert into blob_test values (1, ?)")

REM Create a Java FileInputStream to read the file, then convert the
REM stream to a Java byte array.
in! = new FileInputStream(sourceFile$)
stmt!.setBytes(1, IOUtils.toByteArray(in!))
stmt!.execute()
stmt!.close()

REM Perform a SELECT query on the table to read the record we just added
stmt! = con!.prepareStatement("select * from blob_test")
rs! = stmt!.executeQuery()
if rs!.next() then
    REM Create a Java FileOutputStream to write the binary data
    out! = new FileOutputStream(destFile$)
    
    REM Get a byte array containing the value from the database
    bytes! = rs!.getBytes("BLOB_VAL")
    
    REM Write the byte array to the output stream
    out!.write(bytes!)
    out!.close()
endif

REM Close the JDBC connection
stmt!.close()
con!.close()

See Also

The BASIS DBMS – New 5.0 Features

String Templates - BBj