You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by newboid123 <gn...@gmail.com> on 2011/12/19 18:10:28 UTC
loading jpg files into database
I have a very simple (conceptually, at least) problem that I need help with.
I want to put jpg images in a server database, which can then be accessed
via a client Java program. I realize that the images need to be store as
blob data, but how does one do this via the ij utility? The documentation
suggests using
SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE
but I can't seem to figure it out (even with the examples).
--
View this message in context: http://old.nabble.com/loading-jpg-files-into-database-tp33004165p33004165.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: loading jpg files into database
Posted by Rick Hillegas <ri...@oracle.com>.
On 12/19/11 9:10 AM, newboid123 wrote:
> I have a very simple (conceptually, at least) problem that I need help with.
>
> I want to put jpg images in a server database, which can then be accessed
> via a client Java program. I realize that the images need to be store as
> blob data, but how does one do this via the ij utility? The documentation
> suggests using
>
> SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE
>
> but I can't seem to figure it out (even with the examples).
Hello,
Can you be more specific about the problem you are having using this
procedure? It works for me. See the following script and Java function.
Thanks,
-Rick
----------- ij script --------------
connect 'jdbc:derby:memory:db;create=true';
create table photo
(
photoID int primary key,
caption varchar( 32672 ),
image blob
);
create function readImage( fileName varchar( 32672 ) ) returns blob
language java parameter style java no sql
external name 'ImageReader.readImage';
insert into photo( photoID, caption, image )
values ( 1, 'A Special Picture', readImage(
'/Users/rhillegas/images/whatHappenedToSanta.jpg' ) );
call syscs_util.syscs_export_table_lobs_to_extfile
(
'APP', 'PHOTO', 'photo.dat', ',' ,'"',
'UTF-8', 'photoImages.dat'
);
select photoID, caption, length( image ) from photo;
truncate table photo;
select photoID, caption, length( image ) from photo;
call syscs_util.syscs_import_table_lobs_from_extfile
(
'APP','PHOTO','photo.dat',',','"','UTF-8',0
);
select photoID, caption, length( image ) from photo;
----------- Java function --------------
import java.io.*;
import java.sql.*;
public class ImageReader
{
public static Blob readImage( String fileName ) throws Exception
{
return new MyBlob( new File( fileName ) );
}
public static class MyBlob implements Blob
{
private File _file;
public MyBlob( File file )
{
_file = file;
}
public InputStream getBinaryStream() throws SQLException
{
try { return new FileInputStream( _file ); } catch
(Exception e) { throw wrap( e ); }
}
public long length() throws SQLException { return _file.length(); }
public byte[] getBytes(long pos, int length) throws
SQLException { throw unimplemented(); }
public long position(Blob pattern, long start) throws
SQLException { throw unimplemented(); }
public long position(byte[] pattern, long start) throws
SQLException { throw unimplemented(); }
public OutputStream setBinaryStream(long pos) throws
SQLException { throw unimplemented(); }
public int setBytes(long pos, byte[] theBytes) throws
SQLException { throw unimplemented(); }
public int setBytes(long pos, byte[] theBytes, int offset, int
length) throws SQLException { throw unimplemented(); }
public void truncate(long length) throws SQLException { throw
unimplemented(); }
public void free() throws SQLException { throw unimplemented(); }
public InputStream getBinaryStream(long pos, long length)
throws SQLException { throw unimplemented(); }
private SQLException unimplemented() { return new
SQLException( "Unimplemented method." ); }
private SQLException wrap( Throwable t ) { return new
SQLException( t.getMessage(), t ); }
}
}
Re: loading jpg files into database
Posted by newboid123 <gn...@gmail.com>.
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.
Re: loading jpg files into database
Posted by "Dag H. Wanvik" <da...@oracle.com>.
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;