By coincidence, I know a bit about this. I wrote some Java code back in
'01 to read and write passenger name records (PNRs) generated by airline
reservation systems into BLOB/CLOB (binary large object and character
large object) columns in Oracle. And I used them a few times in my
Access/Oracle systems.
I like having LOB-type columns available so you can keep all your
information in the database, rather than parts in the database and parts
in files/documents residing on the filesystem. The tradeoff is it's
relatively hard to search/retrieve/manipulate/update the data in a LOB
column. And they can task the capacity of your database, depending on
the size of the objects and what kind of db server you're using. If
Oracle, you may want to separate the BLOB tables into their own
fast-growing tablespace.
I don't know much about indexing in LOBs, other than it can create
monstrous indexes, if that's a concern.
So it's one of those art/science things: LOB columns may be the right
choice or they may not.
If it's not a secret: What's your situation? What back-end are you
using? How many and how big and what kind of large objects will you be
storing? Who needs to extract the data? What for? Lots of reads? Lots
of updates?
Oracle has some decent docs on the use of LOBs:
http://docs.oracle.com/cd/B10501_01/appdev.920/a96591/adl01int.htm#117849
indexing LOBs:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_tables.htm#i1012913
My Java code to write binary data to a BLOB column
======================================================================
import javax.swing.*;
import java.io.*;
import java.sql.*;
import oracle.jdbc.driver.*;
public class WriteBlobToDb {
Connection conn;
Statement stmt;
ResultSet rs;
int bufferSize;
public WriteBlobToDb() throws SQLException, ClassNotFoundException
{
//Load driver and make connection
Class.forName("oracle.jdbc.driver.OracleDriver");
conn =
DriverManager.getConnection("jdbc:oracle:thin:@workflow01.corp.da.com:1521:WKFD","DA","DA");
stmt = conn.createStatement();
}
public void sendToDb() {
String pathname, name;
int amount = 0;
OutputStream out = null;
BufferedInputStream in = null;
//Use a JFileChooser to let the user select the file to be
//read and written to the media table
JFileChooser chooser = new JFileChooser("C:\\");
chooser.setDialogTitle("Choose file to write to BLOB field");
int returnVal = chooser.showOpenDialog(null);
if(returnVal == JFileChooser.APPROVE_OPTION) {
pathname = chooser.getSelectedFile().getAbsolutePath();
name = chooser.getSelectedFile().getName();
chooser = null;
} else {
System.out.println("No file selected. Program terminating.");
return;
}
try {
//Since BLOB is written with stream, disable autocommit
conn.setAutoCommit(false);
//insert a row into the BLOB table, use the empty_blob()
//construct for the BLOB field. empty_blob() creates the
//BLOB locator
String OrderID = "BBC-12346";
stmt.executeUpdate("DELETE FROM TT_ORDER_XML WHERE ORDER_ID = '"
+ OrderID + "' ");
System.out.println("Deleted row for Order " + OrderID);
//stmt.executeUpdate("INSERT INTO TX_ORDER_DELIVERY_ITEM VALUES
(1,9)");
//System.out.println("Inserted item for Delivery");
stmt.executeUpdate("INSERT INTO TT_ORDER_XML VALUES (0,'" +
OrderID + "','XOL','retrieve',SYSDATE,'test', '', empty_blob())");
System.out.println("Inserted new row for Order " + OrderID);
rs = stmt.executeQuery("SELECT ORDER_XML_BLOB FROM TT_ORDER_XML
WHERE ORDER_ID = '" + OrderID + "'");
if (rs.next()) {
//Get the BLOB locator
Blob blob = rs.getBlob(1);
//Get the output stream which will be used to send
//data to the table. Use Oracle extension because
//JDBC 2.0 does not support writing data to BLOB
out = ((oracle.sql.BLOB)blob).getBinaryOutputStream();
//Let driver compute buffer size for writing to BLOB
bufferSize = ((oracle.sql.BLOB)blob).getBufferSize();
//Create a buffered stream to read from the file
in = new BufferedInputStream(new FileInputStream(pathname),
bufferSize);
//Create a byte buffer and start reading from the file
byte[] b = new byte[bufferSize];
int count = in.read(b, 0, bufferSize);
//write the bytes using the OutputStream
//loop until all bytes are written to the table
while (count != -1) {
out.write(b, 0, count);
amount += count;
count = in.read(b, 0, bufferSize);
}
System.out.println("Processed " + amount + " bytes. Finished.");
//Close the Input and Output Streams
out.close();
out = null;
in.close();
in = null;
//commit the changes
conn.commit();
}
} catch (Exception e) {
e.printStackTrace();
try { conn.rollback(); } catch (Exception ignored) {}
} finally {
//if an exception occurred, the streams may not have been closed
//so close them here if needed
if (out != null) try { out.close(); } catch (Exception ignored) {}
if (in != null) try { in.close(); } catch (Exception ignored) {}
}
}
public static void main(String[] args) {
try {
WriteBlobToDb w = new WriteBlobToDb();
w.sendToDb();
} catch (Exception e) {
e.printStackTrace();
} finally {
System.exit(0);
}
}
}
==========================================================================
My VB code to read Oracle data from a BLOB column using ODBC table links
in MS Access.
When you create an ODBC link to an Oracle table in MS Access, the Oracle
BLOB column shows up as an OLEObject data type. This code extracts
binary data from that column and writes it to a file. You can store and
write images, executables, whatever you want.
Public Function writeFile(destFileName As String, destFileDesc As
String) As Boolean
On Error GoTo errWriteBLOB
writeFile = False
DoCmd.Hourglass True
'VARS
Dim DestFile As Integer
Dim lngOffset As Long
Dim lngTotalSize As Long
Dim strChunk() As Byte
Dim BlockSize As Long
Set db = CurrentDb()
'CODE FILE
cSQL = "SELECT BLOBDATA "
cSQL = cSQL & "FROM ADMIN_BLOB "
cSQL = cSQL & "WHERE BLOBDESC = '" & destFileDesc & "';"
Set rs = db.OpenRecordset(cSQL)
If rs.RecordCount <> 1 Then
MsgBox "Error retrieving new application. Please contact
Admin.", , sysTitle
rs.Close
Set rs = Nothing
Exit Function
End If
v = SysCmd(acSysCmdInitMeter, "installing...", 3)
v = SysCmd(acSysCmdUpdateMeter, 1)
'WRITE DATA TO FILE
DestFile = FreeFile
lngTotalSize = rs("BLOBDATA").FieldSize
lngOffset = 0
BlockSize = 32
Open destFileName For Binary As DestFile
Do While lngOffset < (lngTotalSize - 1)
strChunk = rs("BLOBDATA").GetChunk(lngOffset, BlockSize)
Put DestFile, , strChunk
lngOffset = lngOffset + BlockSize
Loop
Close DestFile
'CLOSE OBJECT
rs.Close
Set rs = Nothing
v = SysCmd(acSysCmdUpdateMeter, 3)
writeFile = True
exitWriteBLOB:
v = SysCmd(acSysCmdRemoveMeter)
DoCmd.Hourglass False
Exit Function
errWriteBLOB:
writeFile = False
MsgBox "Error " & Err.Number & " occurred when extracting the
software: " & Err.Description, , sysTitle
Resume exitWriteBLOB
End Function
==================================================================================
Feel free to use any of this code.