RE: Large objects with JDBC

Ok, first the documentation on our extensions to jdbc is in the main
documentation. The rest on JDBC is on the javasoft site
(http://www.javasoft.com).

Stream's are not yet supported for BLOBS, but they are only one method of
accessing BLOBS. The other ways are either ResultSet.getBytes() and
PreparedStatement.setBytes(), or using our LargeObject extensions (as you
found). An example on how to use both methods are in the ImageViewer example
included with the source.

Serialize can be a tad difficult to understand, but it doesn't work with
Blobs anyhow. It implements a means of relating a set of tables to Java
classes, so you can store a class in a table and perform SQL on it.

Peter

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

-----Original Message-----
From: Rob Judd [mailto:rjudd(at)mlug(dot)missouri(dot)edu]
Sent: Sunday, August 13, 2000 1:03 AM
To: pgsql-interfaces(at)postgresql(dot)org
Subject: [INTERFACES] Large objects with JDBC

I would like to save Java objects in the database. I tried a couple of
different ways before finding one that worked, and I'd like to know if
my first attempts failed because the functionality is missing in the
java classes, or if I did something wrong.

I'm using version 7.0.2.

Serialize.create(conn, Vector.class) failed - looking in the sources it
seemed to be missing the code to support arrays.

PreparedStatement.setBinaryStream(1, InputStream, length) failed with a
not supported exception.

I read various documentation and came up with the options below. I
couldn't find anywhere that said they would not work - they talk about
these things as if they are okay, so what am I missing here?

Another question: is the javadoc documentation available anywhere. I
created my own set from the sources, but it seemed like it should be
somewhere in the main documentation. Also in (for example
postgresql.util.Serialize there is a note that says, "This is too
complex to show here, and will be documented in the main documents in
more detail." Is this written yet?

Please don't take this email as a complaint - I am very grateful for the
implementation of the JDBC for Postgresql, and for the most part it
works well. I did manage to get this working eventually using a
different method (the large object API) but the other ways seemed much
more straight forward.

Any help would be appreciated.

Thanks, Rob Judd

Long description of my attempts.
=========================================================================

The standard JDBC method:

From doc/postgres.tgz Chapter 58. JDBC Interface (Using Large Objects)

...
Now, there are two methods of using Large Objects. The first is the
standard JDBC way, and is documented here.
...

File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps =
conn.prepareStatement("insert into images values (?,?)");
ps.setString(1,file.getName());
ps.setBinaryStream(2,fis,file.length());
ps.executeUpdate();
ps.close();
fis.close();

...

I tried this and got an unsupported exception on ps.setBinaryStream().
I looked in the sources (that came with postgresql v7.0.2) and sure
enough in PreparedStatement the method is:

public void setBinaryStream
(int parameterIndex, InputStream x, int length)
throws SQLException
{
throw new PSQLException("postgresql.prep.is");
}

--------------------------------------

Next method - use postgresql.util.Serialize to write the class to a and
then use the table to save the class.

From the documentation for postgresql.util.Serialize:

...
public static void create(Connection con,
Object o) throws SQLException

This method is not used by the driver, but it creates a table, given a
Serializable Java Object. It should be used before serializing any
objects.
...

The following code fails to create the table:

Serialize.create(conn, Vector.class) ;

with a NullPointerException. That seemed to be caused by a logging
statement so I took out the logging statement, but then noticed lower
down, where the SQL to create the table is being written, the following:

...

if(type.isArray()) {
// array handling
} else {

...

and since Vector uses an array to store its elements, Serialize.create()
fails because of this.

I finally succeeded using the large object API, much to my relief.