Re: smallint mapping issue - Mailing list pgsql-jdbc

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