Re: HELP: really and truly stuck - Mailing list pgsql-jdbc

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