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;
