Re: How to insert binary data - Mailing list pgsql-jdbc

Hi, Oliver,

On Sat, 05 Jun 2004 21:30:33 +1200
Oliver Jowett <oliver@opencloud.com> wrote:

> Markus Schaber wrote:
> > Today, I had some problems inserting Data via WKB format (which we
> > suspect to be more efficient compared to the text representation)
> > into a PostGIS database through JDBC.
>
> What is the "WKB" representation exactly? (I'm guessing "well known
> binary"?)

Yes, exactly. I can send you pointers to the exact specifications, if
needed.

> > Is there any way that allows efficient storing of WKB data into
> > PostGIS?
> The standard driver can't use binary parameter values as it does not
> use the extended query protocol for sending parameters.

That's what I was afraid of :-(

> I'm working on some driver modifications that would allow sending
> parameters in a binary form (among other things). The main unresolved
> question in this area is how it would fit with the PGobject system.

I think it might be better to implement the JDBC type map system, as
soon as we have binary data in place. This would allow to use this jdbc
compliant method, which might have some advantages when running in jboss
or other environments which tend to wrap the connection objects in their
own wrappers.

It may also help to reduce the database specific code in the
application, and could partially render my "arbitrary types framework"
proposal from 10th of March obsolete (for Reading, we could subclass the
Class we want to use, and register this subclass with the driver, we
would only need some trickery for writing...)

> My current thoughts are to have a PGBinaryObject subclass of PGobject
> that has methods for streaming the parameter in a binary form. The
> addDataType registration mechanism would remain unchanged, and you'd
> continue to use setObject to bind parameters. At statement execution
> time, if the bound parameter value subclasses PGBinaryObject *and* the
> protocol in use allows use of binary parameters (version 2 connections
> won't) then the appropriate PGBinaryObject methods are invoked to
> actually send the parameter. Otherwise the driver uses the current
> string representation approach.

Maybe we could also look for implementing SQLData, or make
PGBinaryObject implement SQLData so one can use the same wrapper
Implementation for both methods.

> Getting results back in a binary form is further away

How do we get byte areas currently? I ask this because there's a
bytea(wkb) conversion in postgis. Maybe this is temporary way to get WKB
data into the application until the driver fully supports reading binary
data. (Sadly, there's no wkb(bytea) method...)

> but would use a
> similar strategy (if we have a binary result value and a corresponding
> registered type that subclasses PGBinaryObject, call an appropriate
> method, otherwise treat it as an unknown type).

I think that's the correct way to go.

> Does this sound like the right approach for what you need?

Yes, it sounds reasonable and would help to solve my problem. Although
I'm not shure whether the java type map is the better way to deal with
binary representations.

I'd like to help coding, but I think I need some amounts of time to dig
into the code and protocol to be of any bigger help there...


Thanks for your patience,

Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com