Re: [Patch] JDBC3 Blob support

On Sat, 19 Feb 2005, Michael Barker wrote:

> I have attached a patch that provides support for JDBC 3 Blobs for the
> PostgreSQL driver.
>
> JUnit tests are included. The only method that is not implemented is
> truncate(). I couldn't find a simple efficient method for doing this
> via the LargeObject API.
>

You have not included the unit tests. What about implemeting these
methods for CLOB at the same time? They're pretty much identical.

You have included your username and password in build.properties though,
you can put these values in a file called build.local.properties and they
will not show up in diffs.

Kris Jurka

Hi,

I have attached a patch that provides support for JDBC 3 Blobs for the
PostgreSQL driver.

JUnit tests are included. The only method that is not implemented is
truncate(). I couldn't find a simple efficient method for doing this
via the LargeObject API.

I am posting off list. Please email me direct if you have any queries.

Regards,
Michael Barker

Attached is a patch for Blob and Clob support with JUnits.

Regards,
Michael Barker.

On Sat, 2005-02-19 at 15:15 -0500, Kris Jurka wrote:
>
> On Sat, 19 Feb 2005, Michael Barker wrote:
>
> > I have attached a patch that provides support for JDBC 3 Blobs for the
> > PostgreSQL driver.
> >
> > JUnit tests are included. The only method that is not implemented is
> > truncate(). I couldn't find a simple efficient method for doing this
> > via the LargeObject API.
> >
>
> You have not included the unit tests. What about implemeting these
> methods for CLOB at the same time? They're pretty much identical.
>
> You have included your username and password in build.properties though,
> you can put these values in a file called build.local.properties and they
> will not show up in diffs.
>
> Kris Jurka
>

On Fri, 4 Mar 2005, Michael Barker wrote:

> Attached is a patch for Blob and Clob support with JUnits.
>

I've finally gotten around to reviewing this. The real problem is how to
deal with Clobs and encodings. PostgreSQL doesn't really have any
specific character large object support and the jdbc driver just
layers the Clob type on the existing binary large objects. From
the server's perspective it is all just binary data which makes
things like length return a number of bytes instead of the number
of characters, at least for a multibyte encoding.

As I see it we've got a number of poor options:
1) Always use a single byte encoding (like US-ASCII) for storing data even
though this cannot store all characters.
2) Always use a multibyte encoding (like UTF-8) for storing data even
though this will break many length/positioning methods.
3) Allow the user to select the encoding via a URL parameter.
4) Use the database's server encoding even though this won't always have a
JVM equivalent.
5) Use the JVM's encoding.

3) Seems like the only way to really allow a user to pick their poison.
Anyone else have an opinion? I suppose we could move a lot of the large
object exection from the server to the JDBC driver so it could correctly
count characters, but that'd we quite a performance hit.

I've attached the version of the patch that I've adjusted slightly and
pretty much ignores encoding entirely.

Finally some technical comments on the original patch:

You shouldn't throw a SQLException without a SQL State. To help enforce
this we throw org.postgresql.util.PSQLException that doesn't have a
constructor without a SQL state aregument.

In the unit tests you write:

if (rs.next()) {
// do something
} else {
assertFalse("Message", true);
}

We don't use assertFalse in order to retain compatibility with junit 3.7.
Also this is written more clearly as:

assertTrue(rs.next());
// do something

Kris Jurka

On Wed, 23 Mar 2005, Michael Barker wrote:

> I will have a look at the patch over the weekend. When I wrote the CLOB
> stuff I set it up to always use a single byte encoding, except when using
> get/setCharacterStream. If a user required mulltibyte encodings then they
> should use the Reader/Writer combination exclusively.

AbstractJdbc2Clob.getSubString creates a String from bytes with no
encoding specified.

AbstractJdbc3Clob.setString(long, String, int, int) gets a String's bytes
with no encoding specified.

Even when the user wants a multibyte encoding they are stuck using the
JVM's default encoding because the Reader/Writer's are not created with an
encoding specified.

Yeah, it's a complicated issue. I know you were after just the BLOB part
and I pushed you into the CLOB part. No one else has any feedback, and no
matter what we do it won't be all that good, so maybe the smart thing to
do is just to abandon the CLOB part and just put the BLOB in by itself.
Not to discourage you if you really are interested in the CLOB part of it,
just letting you know that it's not a prerequisite for the BLOB part to
get in.

Kris Jurka

On Thursday 24 March 2005 14:58, Kris Jurka wrote:
> and I pushed you into the CLOB part. No one else has any feedback

After reading your original comments in
http://archives.postgresql.org/pgsql-jdbc/2005-03/msg00167.php, I had
a couple of questions that I neglected to ask right away. If you
don't mind clarifying a couple of things for me, here they are:

* Did I understand correctly that the server does not have an
encoding associated with CLOB columns?

* How do other databases deal with this? Since I haven't touched
Oracle in a long time, I would appreciate it if someone could
refresh my recollection. Do Oracle's CLOBs know their encoding?
Do Oracle's dbms_lob functions respect the CLOB encoding?

On Thu, 24 Mar 2005, Michael Barker wrote:

> Yeah, you got me. My intent was to implement the above, but I kinda
> hacked in the CLOB stuff, as I was more interested in getting the BLOB
> support in place. If you would be willing to commit the BLOB pieces
> then it would be appreciated as this has the greatest benefit to the
> JBoss Mail project. I am still interested in doing the CLOB stuff as a
> have a picture of how to do it (though its not pretty), but would prefer
> it not to hold up the BLOB support. Would you like me to resubmit a
> BLOB patch w/JUnits?

I'll apply just the BLOB part of this while we decide what to do about
CLOBs.

Kris Jurka

On Thu, 2005-03-24 at 14:58 -0500, Kris Jurka wrote:
>
> On Wed, 23 Mar 2005, Michael Barker wrote:
>
> > I will have a look at the patch over the weekend. When I wrote the CLOB
> > stuff I set it up to always use a single byte encoding, except when using
> > get/setCharacterStream. If a user required mulltibyte encodings then they
> > should use the Reader/Writer combination exclusively.
>
> AbstractJdbc2Clob.getSubString creates a String from bytes with no
> encoding specified.
>
> AbstractJdbc3Clob.setString(long, String, int, int) gets a String's bytes
> with no encoding specified.

Yeah, you got me. My intent was to implement the above, but I kinda
hacked in the CLOB stuff, as I was more interested in getting the BLOB
support in place. If you would be willing to commit the BLOB pieces
then it would be appreciated as this has the greatest benefit to the
JBoss Mail project. I am still interested in doing the CLOB stuff as a
have a picture of how to do it (though its not pretty), but would prefer
it not to hold up the BLOB support. Would you like me to resubmit a
BLOB patch w/JUnits?

>
> Even when the user wants a multibyte encoding they are stuck using the
> JVM's default encoding because the Reader/Writer's are not created with an
> encoding specified.
>
> Yeah, it's a complicated issue. I know you were after just the BLOB part
> and I pushed you into the CLOB part. No one else has any feedback, and no
> matter what we do it won't be all that good, so maybe the smart thing to
> do is just to abandon the CLOB part and just put the BLOB in by itself.
> Not to discourage you if you really are interested in the CLOB part of it,
> just letting you know that it's not a prerequisite for the BLOB part to
> get in.
>
> Kris Jurka
>

Mike.

On Thu, 24 Mar 2005, Kris Jurka wrote:
>
> On Thu, 24 Mar 2005, Michael Barker wrote:
>
> > Yeah, you got me. My intent was to implement the above, but I kinda
> > hacked in the CLOB stuff, as I was more interested in getting the BLOB
> > support in place. If you would be willing to commit the BLOB pieces
> > then it would be appreciated as this has the greatest benefit to the
> > JBoss Mail project. I am still interested in doing the CLOB stuff as a
> > have a picture of how to do it (though its not pretty), but would prefer
> > it not to hold up the BLOB support. Would you like me to resubmit a
> > BLOB patch w/JUnits?
>
> I'll apply just the BLOB part of this while we decide what to do about
> CLOBs.
>

OK, I've applied the BLOB part. I left in AbstractJdbc2BlobClob with the
assumption that the CLOB stuff will build upon it.

Kris Jurka