Thread: 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

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);
>
...
> // Create PreparedStatement
> PreparedStatement ps = db.prepareStatement("Insert into database values
> (?,?)");
> ps.setString(1,"TEST");
> ps.setBytes(2,objBytes);
> ps.executeUpdate();
> ps.close();

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.

Please look in the Postgres programmer documentation, in the JDBC part,
under "Postgres Extensions to the JDBC API".

There is a separate mechanism to access PostgreSQL's large object
interface, as well as the geometric types etc.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

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