Thread: Trouble with large objets and jdbc

Hello,

I want to use large objets to store images in a database with the jdbc
interface. I have try to use PreparedStatement which has
setBinaryStream () but it's not possible with the old version I
usually use (6.4.2). I have download the 6.5b1 and unfortunately
setBinaryStream () is still not implemented.

I have tried to store the uuencoded image in a text field but they are
a strict limitation of 8190 byte for a field, which is too small.

I have tried to use LargeObjects, with no luck. The example.blobtest
fail. I have join the error message at the end of this mail (I use
postgresql 6.5b1 on a FreeBSD 3.0 computer)

example.ImageViewer sometime fail

Somebody can help ?

Thanks 

-- 
% Henri-Pierre.Charles@prism.uvsq.fr PRiSM, Université de Versailles
% Tel: 01 39 25 43 44        45, Av. des États Unis 78000 Versailles
% Il reste 58982399 secondes jusqu'au 01/01/2000


>java -Djdbc.driver=postgresql.Driver example.blobtest jdbc:postgresql:test anonymous ""
PostgreSQL blobtest v6.3 rev 1

Connecting to Database URL = jdbc:postgresql:test
Connected...Now creating a statement

----------------------------------------------------------------------
Testing postgresql large object api

Gaining access to large object api
Test 1 Creating a large object

Creating a large object
Opening test source object
Copying file to large object
Block size=2048 offset=0
Block size=2048 offset=2048
Block size=2048 offset=4096
Block size=413 offset=6144
Closing object
Test 2 Reading a large object and save as a file

Opening large object 18785
Opening test destination object
Copying large object to file
Exception caught.
java.sql.SQLException: IOError while reading from backend: java.io.IOException: The backend has broken the connection.
Possiblythe action you have attempted has caused it to close.
 
java.sql.SQLException: IOError while reading from backend: java.io.IOException: The backend has broken the connection.
Possiblythe action you have attempted has caused it to close.       at
postgresql.PG_Stream.ReceiveChar(PG_Stream.java:183)      at postgresql.fastpath.Fastpath.fastpath(Fastpath.java:114)
   at postgresql.fastpath.Fastpath.fastpath(Fastpath.java:188)       at
postgresql.largeobject.LargeObject.seek(LargeObject.java:207)      at
postgresql.largeobject.LargeObject.size(LargeObject.java:250)      at example.blobtest.ownapi_test2(blobtest.java:144)
    at example.blobtest.ownapi(blobtest.java:77)       at example.blobtest.<init>(blobtest.java:50)       at
example.blobtest.main(blobtest.java:188

Henri-Pierre CHARLES wrote:

> Hello,
>
> I want to use large objets to store images in a database with the jdbc
> interface. I have try to use PreparedStatement which has
> setBinaryStream () but it's not possible with the old version I
> usually use (6.4.2). I have download the 6.5b1 and unfortunately
> setBinaryStream () is still not implemented.

This bug has been fixed as of Monday.  They (Hackers group) have also fixed a big hairy bug with the backend.  If you
usethe anonymous cvs to checkout the latest snapshot, you will
 
find an ImageViewer.java example that works, the blobtest.java works also.

I also use code such as this to work fine:

 // The UPDATE statement to be used for populating the item_picture and // item_descr columns String itemUpdateStmt =
"UPDATEitem SET item_picture = ?, item_descr = ?"+  " WHERE item_num = ?";  // Create a PreparedStatement object & use
itto update the item_picture  // and item_descr columns in the item table  PreparedStatement updateStmt =
conn.prepareStatement(itemUpdateStmt);  // File names for the image & text files   String itemPictureFileName =
("item_"+ new Integer(i).toString()            + ".gif");   String itemDescrFileName = ("item_" + new
Integer(i).toString()         + ".txt");
 
   // java.io.File objects corresponding to the image & text files   File itemPictureFile = new
File(itemPictureFileName);  File itemDescrFile = new File(itemDescrFileName);
 
   // Byte arrays to store text & byte data for the item_picture and   // item_descr columns   byte itemPictureArray []
=new byte[(int)itemPictureFile.length()];   String itemDescrString = "";
 
   // Using java.io.FileInputStream to reading the image & text files   // into byte arrays   FileInputStream
itemPictureInputStream= new    FileInputStream(itemPictureFile);   itemPictureInputStream.read(itemPictureArray, 0,
    (int)itemPictureFile.length());
 
   FileReader fr = new FileReader(itemDescrFile);   BufferedReader br = new BufferedReader(fr);   String lineIn;
while((lineIn = br.readLine()) != null)    itemDescrString += lineIn;
 
   // Set the values of the parameters & execute the statement   updateStmt.setBytes(1, itemPictureArray);
updateStmt.setString(2,itemDescrString);   updateStmt.setInt(3, i);   updateStmt.executeUpdate();
 


Then to read/view the image, I do:
    // read the image from the resultset    byte itemPictureArray [] = queryResults.getBytes(2);     Image img =
Toolkit.getDefaultToolkit().createImage(itemPictureArray);    itemPictureCanvas.setImage(img);
itemPictureCanvas.repaint();


--
Brian Millett
Enterprise Consulting Group     "Heaven can not exist,
(314) 205-9030                     If the family is not eternal"
bpm@ec-group.com                   F. Ballard Washburn