>> 2. Are the blobs still working with this? The build script checks for
>> oracle classes because the oracle.BLOB is needed in MMOracle.java to
>> insert and update blobs.
> I will have to look into this,.. but i think that it should be solved
> with the jdbc api if it can be done
Maybe it can in the newest jsbc api, but the JDBC 2.0 didn't have the
right tools for oracle.
Here is some info I found about blob's in oracle when I wrote the code in
MMORacle
BLOBs and binary files. Difference MySQL and Oracle
BLOBs (Binary Large Objects)
MySQL supports several different data types to handle BLOBs, e.g. TINYBLOB,
BLOB, MEDIUMBLOB and LONGBLOB. The difference is the maximum size of the BLOB
and the amount of bytes used per record.
Oracle 8 and 9 support LOBs (Large Objects) with either character data (CLOB)
or binary data (BLOB), The difference lying in the way the data is translated
to/from different character sets. BLOBs are never translated.
MySQL uses a string interface to BLOBs which is simple to use, you insert
data
as a string and you retrieve it as a string.
Oracle has a special LOB interface which makes is possible to read or modify
only parts of the LOB. This makes the SQL interface much more complex to deal
with, and Oracle also requires that data is inserted using a hex
representation
rather than a binary one. There is an Oracle SQL function, RAWTOHEX, which
converts from binary to hex but if the binary data contains a null character
(ASCII 0) then the Oracle driver barfs and the SQL expression fails.
There are size restrictions on Oracle SQL statements as well, the longest SQL
expression is only about 64 kb which means you can only insert about 32 kb
using SQL. I think. But you can always write the data using the LOB interface
***********************************
WRITING DATA USING A LOB
First you need a reference to the LOB. Then you write the data to the LOB,
which buffers it. Finally, you update the LOB column with the modified LOB,
like this:
//Use oracle.sql.BLOB because java.sql.Blob lacks setBytes()
//JDBC3 java.sql.Blob adds the method setBytes(int,byte[])
//Oracle JDBC uses the method putBytes(int,byte[])
//Execute a select query. The ResultSet is rs
oracle.sql.BLOB dbBlob = (oracle.sql.BLOB)rs.getBlob(1);
//update blob
ps = cxn.prepareStatement(sqlSetBlob);
ps.setString(2,SomeValue);
dbBlob.putBytes(1,binaryArray);
// You can't do this:
// ps.setBinaryStream(1,new
BinaryInputStream(binaryArray),binaryArray.length);
// You must do it like this:
// ps.setBlob(1,dbBlob);
// Which is weird because you CAN do this:
// InputStream is = rs.getBinaryStream(1);
// Note that if the column were declared LONGVARBINARY then
// setBinaryStream() would work.
ps.setBlob(1,dbBlob);
cxn.commit();
***********************************
INSERTING ROWS THAT CONTAIN LOB COLUMNS
This is trickier than doing updates because you can't write LOB data until
you have a reference to the LOB, and you can't get a reference to the LOB
until after you insert the row. So you start like this.
String
sqlNewRow =
"INSERT INTO BLOB_TABLE (BLOB_ID,BLOB_DATA) " +
"VALUES (?,EMPTY_BLOB())",
sqlLockRow =
"SELECT BLOB_DATA FROM BLOB_TABLE " +
"WHERE BLOB_ID = ? FOR UPDATE",
sqlSetBlob =
"UPDATE BLOB_TABLE " +
"SET BLOB_DATA = ? " +
"WHERE BLOB_ID = ?";
Connection cxn = DriverManager.getConnection(
connect_string,username_string,password_string);
cxn.setAutoCommit(false);
//make new row
PreparedStatement ps = cxn.prepareStatement(sqlNewRow);
ps.setString(1,SomeValue);
ps.executeUpdate();
//lock new row
ps = cxn.prepareStatement(sqlLockRow);
ps.setString(1,SomeValue);
ResultSet rs = ps.executeQuery();
rs.next();
The expression EMPTY_BLOB() is Oracle specific. This function manufactures
a LOB for insertion into the column. You must do this, because if you don't,
when you try to update the row you won't get a LOB, you'll get NULL.
At this point you update the row as described in the preceding section
(WRITING DATA USING A LOB).
WHY "lock new row" ?
LOB data is not stored in the table proper, and is not even necessarily
stored in the database per se. LOB data can be stored anywhere, even in a
file accessed via a network file system. It is therefore outside the scope
of the conventional database locking mechanism.
Under such conditions it might be quite a lengthy process to obtain a lock,
so for performance reasons LOB locks are not obtained unless you explicitly
request them.
***********************************
READING LOB DATA
Although you can use getAsciiStream() or getBinaryStream(), reading LOB data
is not entirely straightforward. Before you can retrieve the data, you need
to allocate a byte[] buffer sized to accommodate the data. Here's the catch:
the stream reports its size according to the amount of data in the JDBC
buffer.
Before you retrieve the data, this is zero.
It gets worse. You cannot read the data twice, once to get the size and once
to actually acquire the data, because the stream is forward-only.
sqlLockRow =
"SELECT BLOB_DATA FROM BLOB_TABLE " +
"WHERE BLOB_ID = ? FOR UPDATE",
//lock row
ps = cxn.prepareStatement(sqlLockRow);
ps.setString(1,SomeValue);
ResultSet rs = ps.executeQuery();
byte[] returndata = null;
rs.next()
// The ByteArrayOutputStream buffers all bytes written to it
// until we call getBytes() which returns to us an
// array of bytes:
ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
// Create an input stream from the BLOB column.
// By default, rs.getBinaryStream()
// returns a vanilla InputStream instance.
// We override this for efficiency
// but you don't have to:
BufferedInputStream bis =
new BufferedInputStream( rs.getBinaryStream("BLOB_DATA") );
// A temporary buffer for the byte data:
byte bindata[1024];
// Used to return how many bytes are read with each read() of the input
stream:
int bytesread = 0;
// Make sure its not a NULL value in the column:
if ( rs.wasNull() ) { return returndata; )
while ( (bytesread = bis.read(bindata,0,bindata.length)) != -1 ) {
// Write out 'bytesread' bytes to the writer instance:
baos.write(bindata,0,bytesread);
}
// When the read() method returns -1 we've hit the end of
// the stream,
// so now we can get our bytes out of the writer object:
returndata = baos.getBytes();
// Close the binary input stream:
bis.close();
return returndata;
Nico Klasens
Finalist IT Group
Java Specialists
