Skip site navigation (1) Skip section navigation (2)

Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-jdbc(at)postgresql(dot)org, stagirus <mamasa(at)stagirus(dot)com>
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)
Date: 2010-09-27 20:43:15
Message-ID: 4CA101E3.5080005@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-jdbc
Kevin Grittner wrote:

> The relevant portions are TABLE B-5 and TABLE B-6. [...]

> But getBoolean against a SMALLINT is
> clearly supposed to be attempted on demand.  I didn't happen across
> the specification of how values map there, but in the absence of
> evidence to the contrary I'd assume zero is false and anything else
> is true;  Likewise, setObject using a Boolean against a SMALLINT
> target is supposed to work.

If I read the original report right, it's about setBoolean(), not
getBoolean() (the error is a type mismatch while executing the query,
not a problem processing the results)

Note that table B-5 is specifically about "What combinations of
java.sql.Types value and actual instance type are valid for
setObject()?". So, for example, if you called "setObject(column,
Boolean.TRUE, Types.SMALLINT)", that should work - you're explicitly
asking the driver to represent a Boolean as a SMALLINT. (As noted, the
mapping's not standard, so you may not get exactly the same as other
databases).

However, if you just call setBoolean() in a context where the database
is expecting a SMALLINT, then it's not going to work - how does the
driver know, in the general case, that it should apply a conversion
there? (Consider fun cases where the parameter isn't just directly
mapped to a column, it's part of an expression, etc).

The driver could in theory ask the backend to always infer a type for
positional parameters, then apply its own conversions, but this requires
an extra round trip per query and would actually break other cases where
valid queries with correct types would suddenly start complaining about
being unable to infer a type. And anyway, it's not the right place for
it - the backend already has a large set of type conversion
functionality, why are we suddenly trying to duplicate that in the driver?

You can think of setBoolean(column, boolValue) as essentially the same
as setObject(column, Boolean.valueOf(boolValue), Types.BOOLEAN) - if you
look at it that way, there's obviously no conversion required, because
the type you passed is already a BOOLEAN as you requested ..

I would suggest that the OP either:

* use a real boolean column in their schema (I mean, the data you're
representing IS a boolean value in this case, right?); or
* use setObject(column, value, Types.SMALLINT) instead of the implied
setBoolean() call when setting the column (this may be tricky to do
since you have a layer between you and the driver, but that's not really
the driver's fault..); or
* add a suitable implicit cast on the backend side from boolean ->
smallint (not sure if this works, haven't looked at the details)

-O

In response to

Responses

pgsql-bugs by date

Next:From: Alexey ParshinDate: 2010-09-27 20:56:01
Subject: BUG #5680: Failure to start: too many private dirs demanded
Previous:From: Kevin GrittnerDate: 2010-09-27 17:42:44
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)

pgsql-jdbc by date

Next:From: Samuel GendlerDate: 2010-09-27 23:01:53
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)
Previous:From: Kevin GrittnerDate: 2010-09-27 17:42:44
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group