Writing to BLOBs

["Williamson, Nick" <ni...@mentor.com>]

Hi all,
 
In Oracle, when you want to update a BLOB, you have to do this first:
 
set my_blob_column=empty_blob();
 
...which initializes it and opens it up for writing. Oracle has a
concept of a "locator", which I guess is similar to a pointer or handle
to the BLOB object, and it is required in order to start streaming data
to it. Just setting my_blob_column=null doesn't do the same thing. 
 
Anyway, I'm trying to port this currently-Oracle app to Derby and of
course Derby doesn't like the reference to empty_blob(). Can someone put
me in the picture regarding the way in which you initialize BLOBs for
update in Derby, maybe posting a code sample or a link to the relevant
docs? I've had a quick scan through the docs and Google without much
success...
 
TIA
Nick
 
 

Re: Writing to BLOBs

[Stanley Bradbury <St...@gmail.com>]

Williamson, Nick wrote:
> Hi all,
>  
> In Oracle, when you want to update a BLOB, you have to do this first:
>  
> set my_blob_column=empty_blob();
>  
> ...which initializes it and opens it up for writing. Oracle has a 
> concept of a "locator", which I guess is similar to a pointer or 
> handle to the BLOB object, and it is required in order to start 
> streaming data to it. Just setting my_blob_column=null doesn't do the 
> same thing.
>  
> Anyway, I'm trying to port this currently-Oracle app to Derby and of 
> course Derby doesn't like the reference to empty_blob(). Can someone 
> put me in the picture regarding the way in which you initialize BLOBs 
> for update in Derby, maybe posting a code sample or a link to the 
> relevant docs? I've had a quick scan through the docs and Google 
> without much success...
>  
> TIA
> Nick
>  
>  
Hope this helps:
import java.sql.*;
// create table t_lob1_log(oldvalue varchar(80), newvalue varchar(80), 
chng_time timestamp default current_timestamp);
// Loads the contents of the file into the BLOB column
public class blob_insert
{
    public static void main(String[] args) {
        String filename = "cheesecake.bmp";
        try {
            String url = "jdbc:derby:101toursDB";

            
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            Connection conn = DriverManager.getConnection(url);

            Statement s = conn.createStatement();
          //  s.executeUpdate("create table LOB1 (str1 Varchar(80), 
b_lob BLOB(50M))");
          //  conn.commit();

            // --- add a file
            java.io.File file = new java.io.File(filename);
            int fileLength = (int) file.length();

            // - first, create an input stream
            java.io.InputStream fin = new java.io.FileInputStream(file);
            PreparedStatement ps = conn.prepareStatement("INSERT INTO 
LOB1 VALUES (?, ?)");
            ps.setString(1, filename);

            // - set the value of the input parameter to the input stream
            ps.setBinaryStream(2, fin, fileLength);
            ps.execute();
            conn.commit();

            // --- reading the columns - print: converts BLOB to 
meaninless String
            ResultSet rs = s.executeQuery("SELECT * FROM LOB1 WHERE str1 
= '" + filename +"'");
            while (rs.next()) {
                java.sql.Blob ablob = rs.getBlob(2);
                java.io.InputStream ip = rs.getBinaryStream(2);
                int c = ip.read();
                while (c > 0) {
                    System.out.print((char)c);
                    c = ip.read();
                }
                System.out.print("\n");
                // ...
            }
        } catch (Exception e) {
            System.out.println("Error! "+e);
        }
    }
}