db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dag.wan...@oracle.com (Dag H. Wanvik)
Subject Re: loading jpg files into database
Date Wed, 21 Dec 2011 01:46:00 GMT

This dirty hack worked for me, although its not efficient. Tricky to get
a BLOB into ij.. anyone has a simpler way? Probably better to write a
small app to load a set of files..

Thanks,
Dag

cat - > foo.sql <<"EOF"
    connect 'jdbc:derby:wombat;create=true';
    create table t (id int, b blob(100m));
    create function jpg(v varchar(50)) returns blob(100m) language java no sql parameter style
java external name 'ReadJpg.read';
    autocommit off;
    prepare p as 'insert into t values(?,?)';
    execute p using 'values (1,jpg(''foo.jpg''))';
    select * from t;
    commit;
    execute p using 'values (2,jpg(''foo.jpg''))';
    select * from t;
    commit;
    exit;
EOF

export CLASSPATH=derby.jar:derbytools.jar:ReadJpg.jar
java org.apache.derby.tools.ij foo.sql
 

where the function maps to the Java program 'ReadJpg' is below which must
be on the classpath as shown above:

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.FileNotFoundException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ReadJpg {

    /**
     * @param args the command line arguments
     */
    public static Blob read(String fileName) throws FileNotFoundException, SQLException {
        File f = new File(fileName);
        InputStream i = new FileInputStream(f);
        Connection c = DriverManager.getConnection("jdbc:derby:memory:tmp;create=true");
        try {
            c.createStatement().executeUpdate("create table t(v blob(100m))");
        } catch (SQLException e) {
            // already created
        }
        PreparedStatement ps = c.prepareStatement("insert into t values (?)");
        ps.setBinaryStream(1, i);
        ps.executeUpdate();
        ResultSet rs = c.createStatement().executeQuery("select v from t");
        rs.next();
        return rs.getBlob(1);
    }
}


Output on my console:

$ bash script.sql
ij version 10.9
ij> connect 'jdbc:derby:wombat;create=true';
ij> create table t (id int, b blob(100m));
0 rows inserted/updated/deleted
ij> create function jpg(v varchar(50)) returns blob(100m) language java no sql parameter
style java external name 'ReadJpg.read';
0 rows inserted/updated/deleted
ij> autocommit off;
ij> prepare p as 'insert into t values(?,?)';
ij> execute p using 'values (1,jpg(''foo.jpg''))';
1 row inserted/updated/deleted
ij> commit;
ij> select * from t;
ID         |B                                                                            
                                                  
--------------------------------------------------------------------------------------------------------------------------------------------
1          |616272616b6164616272610a                                                     
                                                  

1 row selected
ij> execute p using 'values (2,jpg(''foo.jpg''))';
1 row inserted/updated/deleted
ij> commit;
ij> select * from t;
ID         |B                                                                            
                                                  
--------------------------------------------------------------------------------------------------------------------------------------------
1          |616272616b6164616272610a                                                     
                                                  
2          |616272616b6164616272610a                                                     
                                                  

2 rows selected
ij> exit;

Mime
View raw message