Thread: "create type" custom types not supported by JDBC

I'm getting errors when I'm trying to access my custom data types
through JDBC.

If I don't supply a custom map using Connection.setTypeMap() then I'm
getting ...

org.postgresql.util.PGobject cannot be cast to Money at ...
Money m = (Money) resultSet.getObject(1);  // Money is my class to
handle multi-currency money types


If I add a mapping with ...
map.put("couk_cleverthinking_gwappo_currency",Class.forName("Money") )
connection.setTypeMap(map)

Then I get ...
java.lang.ClassCastException: java.lang.Class cannot be cast to
java.sql.SQLData
    at
org.postgresql.jdbc2.AbstractJdbc2Connection.getObject(AbstractJdbc2Connection.java:403)



Poking around the source code of AbstractJdbc2Connection it looks like
this functionality isn't support.

Please could somebody confirm this is the cause and offer any insights
as to when it might be included.

Perhaps an "Unsupported feature" error would be more helpful than a
ClassCastException.

Very best wishes
Roy

On Sat, 1 Dec 2007, Roy Smith wrote:

> I'm getting errors when I'm trying to access my custom data types through
> JDBC.
>
> If I don't supply a custom map using Connection.setTypeMap() then I'm
> getting ...
>
> org.postgresql.util.PGobject cannot be cast to Money at ...
> Money m = (Money) resultSet.getObject(1);  // Money is my class to handle
> multi-currency money types

It's not clear whether your type extends PGobject or implements SQLData.
Currently the PG driver only supports custom types via its own
non-standard method.  You must extend PGobject and register it with the
driver's non-standard type map via either PGConnection.addDataType()
or via a URL paramater.

> Poking around the source code of AbstractJdbc2Connection it looks like
> this functionality isn't support. Perhaps an "Unsupported feature" error
> would be more helpful than a ClassCastException.
>

Yes, it would.  I'll take a look.

Kris Jurka

On Sat, 1 Dec 2007, Kris Jurka wrote:

>> Poking around the source code of AbstractJdbc2Connection it looks like this
>> functionality isn't support. Perhaps an "Unsupported feature" error would
>> be more helpful than a ClassCastException.
>>
>

I've put a fix for this into CVS.

Kris Jurka

Thanks for the pointer Kris.
It's now working.

For the sake of anybody trying the same, here's what I did ...

In the database:-
create type couk_cleverthinking_gwappo_db_Money as (local_value
numeric(11,2),mnemonic char(3), exchange_rate numeric(9,5),base_value
numeric(11,2));
CREATE TABLE prices (price couk_cleverthinking_gwappo_db_Money);
insert into prices values(ROW(100,'PHP',.943,0.94));
select * from prices;
        price
----------------------
 (100.00,PHP,0.943,0.94)


My Custom class is:-
public class Money extends PGobject implements SQLData, Serializable{
    public Money() {
        setType("couk_cleverthinking_gwappo_db_money");
    }
    public Money(String _value) {
        setType("couk_cleverthinking_gwappo_db_money");
    ... stuff ...
    }
... stuff ...
}

To use it I do :-
            Connection conn = DriverManager.getConnection(url, userName,
password);
            ((PGConnection)
conn).addDataType("couk_cleverthinking_gwappo_db_money",Class.forName("couk.cleverthinking.gwappo.db.Money"));
            Money m2 = new Money("(100,USD,0.534,0.53)");
            PreparedStatement pst;
            pst = conn.prepareStatement("insert into prices values(?)");
            pst.setObject(1,m2);


I do have a follow up question about how this might all work in a JPA
layer (Oracle Toplink). That's another post :-)

Many thanks




Kris Jurka wrote:
> On Sat, 1 Dec 2007, Kris Jurka wrote:
>>> Poking around the source code of AbstractJdbc2Connection it looks
>>> like this functionality isn't support. Perhaps an "Unsupported
>>> feature" error would be more helpful than a ClassCastException.
> I've put a fix for this into CVS.