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

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.
Always use BLOB for storing binary data of any size if a third party ODBC or JDBC application will access the data.
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.
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

rem ' 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()

Binary Data in the DBMS

Notes on Using BLOBs

Example

See Also