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