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

Inserting "null" not working (Sun App Server, Postgres, EJB3)?

From: "Ahmed Abd-el-Shafy Abd-Allah" <aaabdallah(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Inserting "null" not working (Sun App Server, Postgres, EJB3)?
Date: 2007-03-01 14:04:43
Message-ID: 40939ed60703010604j149caad2n92dccac2ae6f6824@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
 Hello,

I posted this question to the Java forums at Sun, but I think the problem is
attributable to something in PostgreSQL.

I have a table with some columns that allow null values. From within my Java
code, I have an EJB3 entity which models that table. I know that the overall
code works because in the beginning I did not allow null values at the
database level (using NOT NULL), and everything worked fine. But after
changing the database to allow null values, I ran into the following
problem.

If the entity has null values for some of its fields, when I call persist()
on the entity manager, I get the following exception (which I have edited to
only show one of the fields for brevity):

Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2006.4 (Build
060412)): oracle.toplink.essentials.exceptions.DatabaseException

Internal Exception: org.postgresql.util.PSQLException: ERROR: column
"pools" is of type smallint but expression is of type character
varyingError Code: 0

Call:INSERT INTO MYTABLE (POOLS) VALUES (?)
        bind => [*null*]
Query:InsertObjectQuery(aaacs(dot)rex(dot)ejb(dot)entities(dot)MyTable(at)10b287f)



The underlying database is PostgreSQL 8.1.4 (and the jdbc driver is 8.1.407,
JDBC3), and I am using the Sun 9.0 application server.

I searched on the internet for clues to this problem, and found more than
one post that indicate that the PostgreSQL JDBC driver - and in fact other
JDBC drivers - may be trying to interpret "null" as a number (that is the
type of the column "pools" by the way). Therefore it fails. If I were using
plain JDBC it would be easy to solve this problem because I would use
"setNull()" explicitly.

The problem is that I am using EJB3, which means all I can do is call
persist on the manager - I do not have finegrained control over what the
container is doing actually.

Can anyone help here? I am really stuck. I know that another solution would
be to explicitly cast the value inside the SQL insert, but again, I have no
access to the query because it is being constructed by the container...

Many thanks in advance for any and all advice.

One more thing: is the only solution using "protocolVersion=2"? I have
looked at the FAQ at the JDBC PostgreSQL web site, and that seems to be the
advice right now. Since the code that needs to be modified is within
Toplinnk, or within Sun's server, I can't really change it... so falling
back to protocolVersion 2 might be my only choice. Any thoughts?

-A

Responses

pgsql-jdbc by date

Next:From: csimamDate: 2007-03-01 19:21:33
Subject: HELP: really and truly stuck
Previous:From: Dave CramerDate: 2007-02-28 22:33:40
Subject: Re: how to continue using a connection after an error with autocommit=false

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