Re: loading jpg files into database

[Rick Hillegas]

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 ); }
    }

}