SEO

Hello,

This is a posting I made on Sun's web site, and I've researched the
issue enough to believe that it is due to the behavior of PostgreSQL's
JDBC driver. If you can help me at all, that would be great; my eyes
are really tired from the amount of effort I have put into this in the
last couple of days. And I can't find a solution...

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.DatabaseExcep tion

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.MyT able@10b287f)

The type of the column "pools" is "smallint".

The underlying database is PostgreSQL 8.1.4, and I am using the latest
JDBC driver (8.2.504) - I was using the 8.1.407 driver and it made no
difference on the upgrade. 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 (again, that is the type of the column "pools"). 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.

-A

Since the defined jdbc specification is to use setNull I'd complain
to the ejb entity manager.

Dave
On 1-Mar-07, at 2:21 PM, csimam@gmail.com wrote:

> Hello,
>
> This is a posting I made on Sun's web site, and I've researched the
> issue enough to believe that it is due to the behavior of PostgreSQL's
> JDBC driver. If you can help me at all, that would be great; my eyes
> are really tired from the amount of effort I have put into this in the
> last couple of days. And I can't find a solution...
>
> 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.DatabaseExcep tion
>
> 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.MyT able@10b287f)
>
> The type of the column "pools" is "smallint".
>
> The underlying database is PostgreSQL 8.1.4, and I am using the latest
> JDBC driver (8.2.504) - I was using the 8.1.407 driver and it made no
> difference on the upgrade. 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 (again, that is the type of the column "pools"). 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.
>
> -A
>
>
> ---------------------------(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
>


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org