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

stagirus wrote:
> Good morning. Please find the attached log file regarding our problem. The
> main issue is with "DELETED" column as SMALLINT. Postgres is not accepting
> '0' in the values(..) for DELETED column. (This was the original issue that
> I posted.)
>
> http://postgresql.1045698.n5.nabble.com/file/n3199236/PostgresForum.log
> PostgresForum.log

> ERROR 04-10 15:26:32,937 (JDBCExceptionReporter.java:logExceptions:101)  -Batch entry 0 /* insert
com.stagirus.subsystem.suppliermaster.domain.Provider*/ insert into ST_PROVIDER (GEOGRAPHY_ID, ADDRESS_ID,
PROVIDER_NAME,DESCRIPTION, WEBSITE, EMAIL, PHONE, FAXNO, PRIMARY_ORG_ID, DELETED, CREATED_ON, CREATED_BY,
LAST_MODIFIED_ON,LAST_MODIFIED_BY, PROVIDER_ID) values ('1', '2', 'supplier1', NULL, NULL, NULL, '1245874123', '', '1',
'0','2010-10-04 15:26:32.734000 +05:30:00', '1', '2010-10-04 15:26:32.734000 +05:30:00', '1', '1') was aborted.  Call
getNextExceptionto see the cause. 

Not shown in your logs is the type which Hibernate is binding the
parameters as. I expect that the '0' is actually bound with Oid.BOOL
because Hibernate is using setBoolean() or setObject(...,Types.BOOLEAN).

For example, the implementation of setBoolean does this:

>     public void setBoolean(int parameterIndex, boolean x) throws SQLException
>     {
>         checkClosed();
>         bindString(parameterIndex, x ? "1" : "0", Oid.BOOL);
>     }

which means that the value parameter is indeed "0" or "1" - but its type
is BOOL. (I'm guessing that the query logged by Hibernate above is the
result of PreparedStatement.toString(), which just substitutes the raw
parameter values into the query string without respect for their bound
types)

You could check this by setting loglevel=2 and looking at the messages
sent to the backend, or by looking at a packet capture with wireshark
(IIRC, it has a decent protocol analyzer for the postgresql FE/BE protocol)

Oliver