Re: Interpretability with xDBC specification

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Sumit Pandya" <sumit(dot)pandya(at)elitecore(dot)com>, "Oliver Jowett" <oliver(at)opencloud(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Interpretability with xDBC specification
Date: 2010-07-06 15:03:43
Message-ID: 4C32FF7F0200002500033016@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Oliver Jowett <oliver(at)opencloud(dot)com> wrote:

> If you'd like to prepare a compliance table yourself, I'm sure the
> list would be interested in the results.

FWIW, we took a great deal of care to keep our code portable and had
few problems converting to PostgreSQL. Probably the biggest problem
for *us* was the fact that {fn IFNULL(NULL, NULL)} didn't return the
same thing as a bare NULL literal. A bare NULL is of unknown type,
whereas this escape sequence returns a NULL coerced to the TEXT
type, which didn't work very well in some contexts when the
arguments were dates or numbers.

I understand why this is hard to handle in the PostgreSQL backend
for COALESCE, but we were able to get past it in JDBC by hacking the
driver to replace "{fn IFNULL(NULL, NULL)}" with "NULL" -- as a
stopgap until we could push type information far enough down into
statement generation to wrap the NULL arguments, so that we were
generating "{fn IFNULL(CAST(NULL AS INTEGER), CAST(NULL AS
INTEGER))}" instead. While I agree that pushing that type
information down and generating the CASTs into the statement is
better, I don't believe that current behavior here is strictly
compliant for the {fn IFNULL()} portability escape. In any event,
something similar to the hack I used (although it would have to be
made more bullet-proof) might ease migration to PostgreSQL for some.

-Kevin

In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message David Kerr 2010-07-06 16:16:30 Re: Unable to commit: transaction marked for rollback
Previous Message Dave Cramer 2010-07-06 14:27:13 Re: Interpretability with xDBC specification