Re: JDBC:Using Large Objects

The large object code has been working for some time now, however not
all of the stream methods work fully.

The best thing is to look at the blobtest and ImageViewer examples in
the src/interfaces/jdbc/example directory, as they show what methods are
currently supported.

Peter

--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.

-----Original Message-----
From: Steffen Zimmert [mailto:szimmert@crcg.edu]
Sent: Tuesday, June 22, 1999 7:38 PM
To: PostgreSQL General Mailing List; pgsql-interfaces@postgreSQL.org
Subject: [INTERFACES] JDBC:Using Large Objects

Hi,

I am trying to store some Java objects in the PostgreSQL database
system. The version of PostgreSQL is 6.4.2 running on a linux sparc
machine. The JDBC driver is the one I found in the src/interfaces/jdbc
directory.

First of all I tried to use the example provided on page 167 of the JDCB
interface guide but the setBinaryStream() method is not supported by the
driver yet. So I wanted to store the objects as an array of bytes.
Here's the code I am using at the moment:

I created the associated database table with the following statement:
create table images (imgname name, imgoid oid);

// Load the driver
Class.forName("postgresql.Driver");

// Connect to database
System.out.println("Connecting to Database URL = " + url);
db = DriverManager.getConnection(url, usr, pwd);

System.out.println("Connected...Now creating a statement");
TestObject testObject = new TestObject(1);
byte[] objBytes = objectToBytes(testObject);
System.out.println("Bytes: " + objBytes.length);

// Create PreparedStatement
PreparedStatement ps = db.prepareStatement("Insert into database values
(?,?)");
ps.setString(1,"TEST");
ps.setBytes(2,objBytes);
ps.executeUpdate();
ps.close();

Every time when I execute my little program I receive the following
exception:
PostgreSQL basic test v6.3 rev 1
Connecting to Database URL = jdbc:postgresql://hornbill/foo
Connected...Now creating a statement
Bytes: 42
Exception caught.
java.sql.SQLException: IOError while reading from backend:
java.io.IOException: The backend has broken the connection. Possibly the
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. Possibly the
action you have attempted has caused it to close.
at postgresql.PG_Stream.ReceiveChar(PG_Stream.java:183)
at postgresql.fastpath.Fastpath.fastpath(Compiled Code)
at postgresql.fastpath.Fastpath.fastpath(Fastpath.java:185)
at
postgresql.largeobject.LargeObject.write(LargeObject.java:147)
at
postgresql.PreparedStatement.setBytes(PreparedStatement.java:295)
at basic.<init>(basic.java:45)
at basic.main(basic.java:107)

In general is it possible to use the LargeObject interface with the JDBC
driver or is it not supported yet????
--
Best Regards,
Steffen Zimmert

Steffen Zimmert
Fraunhofer Center for Research in Computer Graphics
Providence, Rhode Island, USA
email: szimmert@crcg.edu

Herouth Maoz wrote:

At 21:38 +0300 on 22/06/1999, Steffen Zimmert wrote:

I created the associated database table with the following statement:
create table images (imgname name, imgoid oid);

Well, if it's defined as an oid, and you pass an array of bytes to it, it
certainly won't work. An oid is a four-byte integer, not a byte array.

I see. But this is the way it is described in the JDBC docs, at page
167. Is there a way to save a Byte array in the PostgreSQL database?

--
Best Regards,
Steffen Zimmert

Steffen Zimmert
Fraunhofer Center for Research in Computer Graphics
Providence, Rhode Island, USA
email: szimmert@crcg.edu

On Tue, 22 Jun 1999, Steffen Zimmert wrote:

Well, if it's defined as an oid, and you pass an array of bytes to it, it
certainly won't work. An oid is a four-byte integer, not a byte array.

I see. But this is the way it is described in the JDBC docs, at page
167. Is there a way to save a Byte array in the PostgreSQL database?

That's something else. I don't have the JDBC guide in front of me at this
minute, but I think you refer to a stream, not a byte array. The normal
method of setting a stream, when and if it is implemented, hides behind it
the creation of a large object, and stores that large object in the place
where objects are stored, and places the oid in the field in the table.

I don't think the same applies to a byte array. In any case, I would
expect byte arrays to be stored in byte-array fields.

However, I wouldn't recommend you to create a byte array, because then its
size is limited to the 8k upper boundary of a row in PostgreSQL. If you
know that this is going to be the size of your objects, fine. Define it
like that. Otherwise, use the special large object interface Peter added
to the JDBC driver. It doesn't require additional stuff beyond what's
already in the driver, if that's what you are afraid of.

Herouth

Hi,

The large object code has been working for some time now, however not
all of the stream methods work fully.

The best thing is to look at the blobtest and ImageViewer examples in
the src/interfaces/jdbc/example directory, as they show what methods are
currently supported.

I looked at the suggested source files and also compiled them. However,
I was not able to run neither Bobtest nor ImageViewer because the same
exception messages occured as in my own application. In addition, I
wrote also a little application based on the source of blobtest and the
exception occurs whenever I try to save a LargeObject. Here's the
source:

// Load the driver
Class.forName("postgresql.Driver");

// Connect to database
System.out.println("Connecting to Database URL = " + url);
db = DriverManager.getConnection(url, usr, pwd);

System.out.println("Connected...Now creating a statement");
stat = db.createStatement();

// Also, get the LargeObjectManager for this connection

System.out.println("Gaining access to LargeObjectApi.");
lom = ((postgresql.Connection)db).getLargeObjectAPI();

// Now create the large object

System.out.println("creating blob");

int oid = lom.create();

System.out.println("Opening "+oid);

LargeObject blob = lom.open(oid);

// Create a new TestObject
System.out.println("Creating a new TestObject...");
TestObject tObject = new TestObject(1);

// Convert TestObject to Byte array
System.out.println("Converting object to byte array...");
byte[] objBytes = objectToBytes(tObject);
System.out.println("Size of byte array: " + objBytes.length);

// Writing byte array to blob
System.out.println("Writing byte array to blob...");
blob.write(objBytes,0,objBytes.length);
^^^^
The following exception occurs here!

Exception caught.
java.sql.SQLException: IOError while reading from backend:
java.io.IOException: The backend has broken the connection. Possibly the
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. Possibly the
action you have attempted has caused it to close.
at postgresql.PG_Stream.ReceiveChar(PG_Stream.java:183)
at postgresql.fastpath.Fastpath.fastpath(Compiled Code)
at postgresql.fastpath.Fastpath.fastpath(Fastpath.java:185)
at
postgresql.largeobject.LargeObject.write(LargeObject.java:147)
at
postgresql.PreparedStatement.setBytes(PreparedStatement.java:295)
at basic.<init>(basic.java:45)
at basic.main(basic.java:107)

Are there perhaps any parameters you have to specify during the
installation process of the database to explicitly allow the storage of
large objects?
Any help is welcome because I really don't know what to do now! :-((

--
Best Regards,
Steffen Zimmert

Steffen Zimmert
Fraunhofer Center for Research in Computer Graphics
Providence, Rhode Island, USA
email: szimmert@crcg.edu