Thread: Custom type woes
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. 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. SQLWorkbench reports columns of uniqueidentifier as java sql type OTHER ... So, I need a kick in the right direction. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
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 > 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. > 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? Kris Jurka
--- Kris Jurka <books@ejurka.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
On Fri, 30 Jun 2006, CG wrote:
> 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.
The key thing you need to do is call setObject like so:
ps.setObject(1, new PGuniqueidentifier("stringdata"));
This is how it determines the type you're passing in.
>> 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.
For the moment all of the mappings to java.sql.Type values are hardcoded
in org.postgresql.jdbc2.TypeInfoCache and money happens to have an entry
for historical reasons, but no other extension type does.
If you really want it to operate exactly like text the easiest thing to do
is just make it a text entry (possibly a domain on text) in the database.
Yes, you'll waste some storage space, but perhaps that is acceptable.
Kris Jurka