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 /**
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()