Writing to Blob!

 Note: tested with Oracle DB 9.x and a thin driver 9.x !

import groovy.sql.Sql

println "---- A working test of writing and then reading a blob into an Oracle DB ---"
sql = Sql.newInstance("jdbc:oracle:thin:@pignut:1521:TESTBNDY", "userName",
                     "paSSword", "oracle.jdbc.OracleDriver")
sql.execute(
    "INSERT INTO MEDIA VALUES (SEQ_MEDIAID.NextVal, empty_blob())");

sql.connection.autoCommit = false

try {
    row = sql.firstRow("select SEQ_MEDIAID.CurrVal from Dual")
    mediaID = row[0]

    row = sql.firstRow("select binarydata from media where mediaid = ? for update",[mediaID])

    my_blob = (oracle.sql.BLOB)row[0]
    if( my_blob == null ) println "my_blob is null!"

    // write the array of binary data to a BLOB
    outstream = my_blob.getBinaryOutputStream();

    // read data into a byte array
    data = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] as byte[];
    outstream.write(data);
    outstream.close()

    sql.commit()
} catch (Exception e) {
  println "Failed: $e"
  sql.rollback()
} finally {
  sql.connection.close()
}


Uses a table:

CREATE TABLE MEDIA
(
   MEDIAID NUMBER(22) NOT NULL,
   BINARYDATA BLOB NOT NULL
);

CREATE   SEQUENCE   SEQ_MEDIAID
INCREMENT   BY   1
START   WITH   100
ORDER;


.  Copying a file to Blob!

    ...
    // write the array of binary data to a BLOB
    outstream = my_blob.getBinaryOutputStream();
  
    def fis= new FileInputStream('c:\\Jornada\\auxil\\010306_115332.jpg')
    println "File size= ${fis.available()}"
    byte[] data = new byte[fis.available()]
    fis.read(data)
    fis.close()
    
    outstream.write(data);
    outstream.close()
    
    sql.commit()
    ...