Re: smallint mapping issue

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(at)opencloud(dot)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
>