Обсуждение: Inserting "null" not working (Sun App Server, Postgres, EJB3)?

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.rex.ejb.entities.MyTable@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

Hi,

Am 01.03.2007 um 15:04 schrieb Ahmed Abd-el-Shafy Abd-Allah:
> 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.rex.ejb.entities.MyTable@10b287f)

We have the very same problem with JBoss EJB3 and Postgres.
Our "solution" is to get the underlying Hibernate session via
EntityManager.getDelegate() and call the setNull()  (?)  Method
from Hibernate for this. So the problem is twofold:
- EJB3 being too stupid to have a setNull() method
- Postgres JDBC driver not automatically the right thing anyway

   Heiko

--
Heiko W.Rupp
heiko.rupp@redhat.com, http://www.dpunkt.de/buch/3-89864-429-4.html

Heiko,

The reason that setNull is required is because the type information
is required. NULL by it self has no type information. The jdbc driver
binds values to a prepared statement and the type information is
required. So setNull( n, typeinfo) is required to bind the correct type.

Why else would setNull even exist ?

Dave
On 1-Mar-07, at 3:48 PM, Heiko W.Rupp wrote:

> Hi,
>
> Am 01.03.2007 um 15:04 schrieb Ahmed Abd-el-Shafy Abd-Allah:
>> 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.rex.ejb.entities.MyTable@10b287f)
>
> We have the very same problem with JBoss EJB3 and Postgres.
> Our "solution" is to get the underlying Hibernate session via
> EntityManager.getDelegate() and call the setNull()  (?)  Method
> from Hibernate for this. So the problem is twofold:
> - EJB3 being too stupid to have a setNull() method
> - Postgres JDBC driver not automatically the right thing anyway
>
>   Heiko
>
> --
> Heiko W.Rupp
> heiko.rupp@redhat.com, http://www.dpunkt.de/buch/3-89864-429-4.html
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Dave Cramer <pg@fastcrypt.com> writes:
> The reason that setNull is required is because the type information
> is required. NULL by it self has no type information. The jdbc driver
> binds values to a prepared statement and the type information is
> required. So setNull( n, typeinfo) is required to bind the correct type.

At least in this particular case, leaving the parameter type as UNKNOWN
would have worked as the OP wishes.  I dunno if that would break other
cases though.

            regards, tom lane

On 1-Mar-07, at 7:06 PM, Tom Lane wrote:

> Dave Cramer <pg@fastcrypt.com> writes:
>> The reason that setNull is required is because the type information
>> is required. NULL by it self has no type information. The jdbc driver
>> binds values to a prepared statement and the type information is
>> required. So setNull( n, typeinfo) is required to bind the correct
>> type.
>
> At least in this particular case, leaving the parameter type as
> UNKNOWN
> would have worked as the OP wishes.  I dunno if that would break other
> cases though.

This means you have to leave all parameter types as UNKNOWN. What's
the point of having parameter types at all ?

Dave
>             regards, tom lane
>

Dave Cramer <pg@fastcrypt.com> writes:
> On 1-Mar-07, at 7:06 PM, Tom Lane wrote:
>> At least in this particular case, leaving the parameter type as
>> UNKNOWN
>> would have worked as the OP wishes.  I dunno if that would break other
>> cases though.

> This means you have to leave all parameter types as UNKNOWN. What's
> the point of having parameter types at all ?

No, only the ones you do not have any knowledge about.  The OP's
complaint is basically that the driver is forcing the parameter type
to "varchar" on the basis of nothing whatsoever.

            regards, tom lane

On 1-Mar-07, at 7:16 PM, Tom Lane wrote:

> Dave Cramer <pg@fastcrypt.com> writes:
>> On 1-Mar-07, at 7:06 PM, Tom Lane wrote:
>>> At least in this particular case, leaving the parameter type as
>>> UNKNOWN
>>> would have worked as the OP wishes.  I dunno if that would break
>>> other
>>> cases though.
>
>> This means you have to leave all parameter types as UNKNOWN. What's
>> the point of having parameter types at all ?
>
> No, only the ones you do not have any knowledge about.  The OP's
> complaint is basically that the driver is forcing the parameter type
> to "varchar" on the basis of nothing whatsoever.
>
OK, this makes some sense, If they do setObject(n, null) then we bind
the type to UNKNOWN. If they do setInt(n, null) we can actually get
that one right.

Dave
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Tom Lane wrote:
> Dave Cramer <pg@fastcrypt.com> writes:
>> On 1-Mar-07, at 7:06 PM, Tom Lane wrote:
>>> At least in this particular case, leaving the parameter type as
>>> UNKNOWN
>>> would have worked as the OP wishes.  I dunno if that would break other
>>> cases though.
>
>> This means you have to leave all parameter types as UNKNOWN. What's
>> the point of having parameter types at all ?
>
> No, only the ones you do not have any knowledge about.  The OP's
> complaint is basically that the driver is forcing the parameter type
> to "varchar" on the basis of nothing whatsoever.

I didn't see the OP say what JDBC calls are being made so I think you're
jumping the gun here. If the EJB3 layer is calling something like
setString(n, null) then the driver is quite justified in assuming the
parameter is varchar. I don't think the driver ever goes from "I don't
know the type" to "the type is varchar" .. if anything, it has to do the
opposite to deal with applications that think that setString() on a
numeric column is a sensible thing to do :(

-O

Ok, looking at the code in the driver we should be doing the right
thing.

setObject(n,null) will set the Oid to UNSPECIFIED

Any chance we can get a test case to see how this fails ?

Dave
On 1-Mar-07, at 8:15 PM, Dave Cramer wrote:

>
> On 1-Mar-07, at 7:16 PM, Tom Lane wrote:
>
>> Dave Cramer <pg@fastcrypt.com> writes:
>>> On 1-Mar-07, at 7:06 PM, Tom Lane wrote:
>>>> At least in this particular case, leaving the parameter type as
>>>> UNKNOWN
>>>> would have worked as the OP wishes.  I dunno if that would break
>>>> other
>>>> cases though.
>>
>>> This means you have to leave all parameter types as UNKNOWN. What's
>>> the point of having parameter types at all ?
>>
>> No, only the ones you do not have any knowledge about.  The OP's
>> complaint is basically that the driver is forcing the parameter type
>> to "varchar" on the basis of nothing whatsoever.
>>
> OK, this makes some sense, If they do setObject(n, null) then we
> bind the type to UNKNOWN. If they do setInt(n, null) we can
> actually get that one right.
>
> Dave
>>             regards, tom lane
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

On Mar 1, 11:48 pm, heiko.r...@redhat.com ("Heiko W.Rupp") wrote:
> Hi,
>
> Am 01.03.2007 um 15:04 schrieb Ahmed Abd-el-Shafy Abd-Allah:
>
> > 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.rex.ejb.entities.MyTable@10b287f)
>
> We have the very same problem with JBoss EJB3 and Postgres.
> Our "solution" is to get the underlying Hibernate session via
> EntityManager.getDelegate() and call the setNull()  (?)  Method
> from Hibernate for this. So the problem is twofold:
> - EJB3 being too stupid to have a setNull() method
> - Postgres JDBC driver not automatically the right thing anyway
>
>    Heiko
>
> --
> Heiko W.Rupp
> heiko.r...@redhat.com,http://www.dpunkt.de/buch/3-89864-429-4.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Could you explain that a bit more, or better yet show a code sample? I
can get the delegate of course, but to go from there to setNull - you
lost me.

thanks!

Hi,


> Any chance we can get a test case to see how this fails ?
>

I can try to produce a test case with EJB3 on JBoss.
OTOH I am very willing to try a new driver version that has this
patch in.

  Heiko


--
Heiko W.Rupp
heiko.rupp@redhat.com, http://www.dpunkt.de/buch/3-89864-429-4.html

Wait, Heiko, I hope you get this message before you spend your time on
this!

First of all, I confirmed what Dave said: the Oracle Toplink package
was indeed sending every single NULL with an SQL type of VARCHAR. I
downloaded the postgresql JDBC source code and slowly traced what was
going on, and put in the necessary printlns to see what was being
sent. Garbage in, garbage out.

The good news: the latest version of the Toplink package solves it: it
correctly switches the type of the NULL based on the real type of the
field.

I thank you very much for attempting to help me, and I hope your
problem also gets solved similarly thru an upgrade. It is frustrating
building off of buggy software, but on the other hand, these are
enormously useful packages that people are putting out for free, so I
can't complain. Just gotta try and get some uninterrupted sleep now...

-A

Am 02.03.2007 um 02:41 schrieb Dave Cramer:

> Ok, looking at the code in the driver we should be doing the right
> thing.
>
> setObject(n,null) will set the Oid to UNSPECIFIED
>
> Any chance we can get a test case to see how this fails ?

Will this be in the PostgreSQL 8.2.4 release?

Again: I could test it with our code, but it's not so easy for me to
submit a testcase right now.

   Heiko

--
Heiko W.Rupp
heiko.rupp@redhat.com, http://www.dpunkt.de/buch/3-89864-429-4.html

This is in the code now.

Dave
On 8-Mar-07, at 11:06 AM, Heiko W.Rupp wrote:

>
> Am 02.03.2007 um 02:41 schrieb Dave Cramer:
>
>> Ok, looking at the code in the driver we should be doing the right
>> thing.
>>
>> setObject(n,null) will set the Oid to UNSPECIFIED
>>
>> Any chance we can get a test case to see how this fails ?
>
> Will this be in the PostgreSQL 8.2.4 release?
>
> Again: I could test it with our code, but it's not so easy for me
> to submit a testcase right now.
>
>   Heiko
>
> --
> Heiko W.Rupp
> heiko.rupp@redhat.com, http://www.dpunkt.de/buch/3-89864-429-4.html
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>