Re: Custom type woes

--- Kris Jurka <books(at)ejurka(dot)com> wrote:

>
>
> On Wed, 28 Jun 2006, CG wrote:
>
> > I'm trying to use the datatype defined in the uniqueidentifier contrib
> > module from within my java application. I had originally thought I would
> > process the values in java as strings, but I can't insert the values
> > back into a table becuase I can't figure out how to create an explicit
> > cast. I had hoped that the automatic type conversion would work, but it
> > doesn't in the case of insert, or in function parameters.
>
> If you use the 8.2dev driver and create your connection using the
> stringtype=unspecified, then these automatic casts should work.
>
>
http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
>

I think we were using the 8.1-402 driver ... We'll try that next. My fear is
that option may create unexpected behaviors other places in the code if the
strong typing is relied upon. It is worth a try.

> > My next idea was to add the type to JDBC a la Money and Interval. I've
> never
> > worked with JDBC drivers before, so I just guessed at what it would take. I
> > created the java class PGuniqueidentifier and modeled it after the other
> > classes in org.postgresql.util. I linked it up along with all the other odd
> > types in org.postgresql.jdbc2.AbstractJdbc2Connection ... That didn't work
> > either.
>
> Could you describe didn't work? I don't see why it wouldn't.

It still generated the "must use explicit cast" error on insert. My
inexperience with jdbc really shows here. I must not have hooked in the new
class in all the spots where it should be hooked. I created a
PGuniqueidentifier.class with appropriate member data, constructors, and
methods setValue, equals, clone, and getValue. I then hooked it in under
org.postgresql.jdbc2.AbstractJdbc2Connection#initObjectTypes by adding the line
addDataType("uniqueidentifier", org.postgresql.util.PGuniqueidentifier.class);
I must be leaving something out.

>
> > SQLWorkbench reports columns of uniqueidentifier as java sql type OTHER ...
>
> It will still do that regardless of what you do. What java.sql.Type would
> you like it to use and how could it know that?

Like MONEY uses double, I was hoping to map uniqueidentifier to VARCHAR... It
wouldn't know unless someone told it. :) Perhaps putting something in under
org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData#getColumns ? That also may
be the wrong course of action.

CREATE TYPE uniqueidentifier
(INPUT=uniqueidentifier_in, OUTPUT=uniqueidentifier_out, DEFAULT='',
INTERNALLENGTH=16, ALIGNMENT=int4, STORAGE=PLAIN);
ALTER TYPE uniqueidentifier OWNER TO postgres;

I'm not sure what values should be used with the tuple array... Yes: I am in
over my head, but I hope to soon begin to tread water.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com