db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From newboid123 <gnmerr...@gmail.com>
Subject Re: loading jpg files into database
Date Wed, 21 Dec 2011 14:23:17 GMT

Christ! There has got to be a better way. (I wonder if it is easier with
another DB program like MySQL.)

Thanks for your help, Dag. I'll just run my little Java program to load the
DB.

Here's an ancillary question. Might it not be more efficient just to have
the client download all the jpgs? When I store the jpg as a binary, it
increases in size.


Dag H. Wanvik-2 wrote:
> 
> 
> 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;
> 
> 

-- 
View this message in context: http://old.nabble.com/loading-jpg-files-into-database-tp33004165p33017103.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message