Re: smallint mapping issue - Mailing list pgsql-jdbc

His challenge is that he has to do that in quite a few places in his
code.

Arguably it should be hidden in a data access object, but his design
is not our concern here

I think given that we are changing the behaviour from one version to
another we may wish to consider
providing a compatibility option.

There are a few places I'd like to see this.

1) this one smallint returned as Short
2) handling setString(type other than string) which used to work


Dave
On 28-Jul-05, at 1:11 PM, Mark Lewis wrote:

> Christian,
>
> One thing you can do to mitigate the impact on your application is to
> write a method like this:
>
> public Object getObject(ResultSet rs, int index) {
>   Object o = rs.getObject(index);
>   if(rs.getMetaData().getColumnType(index) == Types.SMALLINT) {
>     if(o instanceof Integer) {
>       o = new Short((short)((Integer)o).intValue());
>     }
>   }
>   return o;
> }
>
> Unless I'm reading the driver source wrong, calling rs.getMetaData
> ().getColumnType() is a fast operation and does not require a round-
> trip
> to the database, so there shouldn't be a performance impact.
>
> You could even hide this behavior inside a dynamic proxy class that
> reimplements the ResultSet.getObject() method, to avoid making changes
> to any of your SQL code.
>
> -- Mark Lewis
>
> On Thu, 2005-07-28 at 10:05 -0600, Christian Cryder wrote:
>
>> Hi Oliver,
>>
>> Yeah, I see what you are talking about in the spec, and we're
>> certainly not opposed to fixing our app. Here's the difficulty,
>> however, and this is something I would just encourage everyone to
>> consider.
>>
>> We are in the midst of trying to move to Postgres from MS SQL. Our
>> current app consists of about 7500 lines of code (this doesn't count
>> our custom OR mapping middleware). We have over 2500 references to
>> Short's - because that's how NetDirect's MS SQL drivers return
>> smallints. It's also how MySQL returns them, and I _think_ IBM's DB2
>> returns them that way as well (I just uninstalled my demo version of
>> DB2, so I can't easily verify).
>>
>> So there's several points to consider:
>>
>> a) in a decent sized app like ours, saying "just change your app" is
>> not a trivial thing - we can probably fix all those place fairly
>> easily, but it requires time and testing
>>
>> b) where it really complicates things is that our current application
>> is running in production on a db that returns values differently.
>> Now,
>> it's easy to say "tell them to fix their drivers", but realistically,
>> that's not an option w/ MS (and I think you guys know this).
>>
>> c) because we are a pretty-close-to-enterprise app (we handle about
>> 3-5 million trxs a month), there are 2 things that are extremely
>> important to us: a) performance, b) stability. What that means is
>> that
>> we can't afford to shoot ourselves in the foot. Which means that
>> before we can ever consider making a switch to the lovely "we follow
>> the standards better" postgres, we need to test the crap out of it
>> while we live with the "we interpret the spec a little differently"
>> way of other dbs. In other words, we need to be able to have our app
>> run against both MS (production) and Postgres (test) simultaneously.
>> So if you insist on doing it your way and your way only, that
>> makes it
>> a whole lot harder for us.
>>
>> My point in all this is not that you guys are wrong (I'm actually a
>> huge fan of sticking to the spec - even though in this case I think
>> the spec is vague and wrong, I'm still willing to change our code) -
>> my point is just that by adopting a hardline stance in places of
>> ambiguity or discrepency we erect barriers to adoption.
>>
>> We make it hard for people with existing apps - especially if they
>> are
>> big - to switch to from some other platform to Postgres. I'd rather
>> see us make it as easy as possible to make the switch to postgres,
>> especially if we want to see more shops like us (upper midsize dbs)
>> make the transition.
>>
>> So my suggestion is "look for ways to support both". In this case, it
>> would probably be very easy for us to include a configuration param
>> that tells the driver "return Shorts instead of Integers for type
>> smallint." This same type of thing could probably help in the
>> Timestamp issue as well.
>>
>> I'm very willing to be a guinea pig here - because we have a large
>> app, we can probably help expose a lot of issues other folks might
>> not
>> run into. But I'd like to know whether the community is willing to
>> take a "we can help you find a way to do this" approach, or whether
>> its just "our way or the highway".
>>
>> Does that help explain where I'm coming from here?
>>
>> Christian
>>
>> On 7/27/05, Oliver Jowett <oliver@opencloud.com> wrote:
>>
>>> Christian Cryder wrote:
>>>
>>>
>>>> Dave tells me this has been discussed recently, but doesn't
>>>> recall the
>>>> thread, so I'll post here.
>>>>
>>>
>>> You might want to search on archives.postgresql.org too.
>>>
>>>
>>>> In postgresql-8.0-311.jdbc3.jar with a column defined as
>>>> smallint, the
>>>> corresponding value returned from getObject() is a Short.
>>>>
>>>> In the current HEAD, that same smallint column is returned as an
>>>> Integer.
>>>>
>>>
>>> Please see table B-3 in the JDBC 3.0 spec:
>>>
>>> TABLE B-3 JDBC Types Mapped to Java Object Types
>>> ResultSet.getObject and CallableStatement.getObject use the mapping
>>> shown in this table for standard mappings
>>>
>>> [...]
>>>
>>> TINYINT Integer
>>> SMALLINT Integer
>>> INTEGER Integer
>>> BIGINT Long
>>>
>>>
>>>> This causes HUGE problems if it's not an oversight, as it makes it
>>>> virtually impossible to write JDBC code that works with other dbs
>>>> (which typically return Short).
>>>>
>>>
>>> Which other DBs exactly? You should tell them to fix their JDBC
>>> drivers :)
>>>
>>> It's easy enough to deal with both Short and Integer being
>>> returned --
>>> cast to java.lang.Number.
>>>
>>>
>>>> Can someone confirm that this is a bug, and if not, a) why
>>>> (given the
>>>> above documentation), and b) how do you expect to deal with the
>>>> incompatibility issues this would cause?
>>>>
>>>
>>> It's not a bug -- it's a deliberate change so that we actually
>>> follow
>>> the spec.
>>>
>>> Personally I'm not prepared to put much effort into supporting
>>> backwards
>>> compatibility with old behaviour that was not spec compliant. Fix
>>> your
>>> application.
>>>
>>> -O
>>>
>>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that
> your
>        message can get through to the mailing list cleanly
>
>