Hi all,

I have the following mappings:

<hibernate-mapping default-cascade="all" auto-import="true">
<class name="com.diatem.db.teste.Address" table="address" mutable="true" polymorphism="implicit" dynamic-update="false" dynamic-insert="false">
<id name="id" type="long" unsaved-value="0">
<generator class="native">
</generator>
</id>

<property name="street" type="string"></property>
<property name="city" type="string"/>
<property name="person" type="com.diatem.db.teste.Person" length="1024"/>

</class>

</hibernate-mapping>

<hibernate-mapping default-cascade="none" auto-import="true">
<class name="com.diatem.db.teste.Person" table="person" mutable="true" polymorphism="implicit" dynamic-update="false" dynamic-insert="false">
<id name="id" type="long" unsaved-value="0">
<generator class="native">
</generator>
</id>

<property name="firstName" type="string"/>
<property name="lastName" type="string"/>
<property name="age" type="string"/>

<property name="address" type="com.diatem.db.teste.Address" length="1024"/>
<property name="office" type="com.diatem.db.teste.Address" length="1024"/>


</class>

</hibernate-mapping>


The problem is that if I DONT specify length(1024) and it takes the default 255 for the VARCHARs that represent the Person or Address references, I get the folowing from DB2 when trying to save one of the above objects:


COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/SUN] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use. SQLSTATE=22001
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:270)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:207)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:458)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.loadParameters(DB2PreparedStatement.java:1398)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(DB2PreparedStatement.java:2097)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeUpdate(DB2PreparedStatement.java:1642)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:504)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:444)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:717)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:605)
at com.diatem.db.teste.MainTeste.go(MainTeste.java:68)
at com.diatem.db.teste.MainTeste.main(MainTeste.java:37)
rethrown as net.sf.hibernate.JDBCException: Could not insert: [IBM][CLI Driver][DB2/SUN] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use. SQLSTATE=22001
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:720)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:605)
at com.diatem.db.teste.MainTeste.go(MainTeste.java:68)
at com.diatem.db.teste.MainTeste.main(MainTeste.java:37)
Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/SUN] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use. SQLSTATE=22001
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:270)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:207)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:458)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.loadParameters(DB2PreparedStatement.java:1398)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(DB2PreparedStatement.java:2097)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeUpdate(DB2PreparedStatement.java:1642)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:504)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:444)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:717)
... 3 more

First, I do not belive that DB2 defaults varchar fields to 255 - rather 1.

In any case, enable sql logging to see exactly what sql is being attempted by putting

in hibernate.properties
or
in hibernate.cfg.xml to show the sql
and
into your log4j properties file to display the bind params

Hi,

I am sure that the DB2 tables are created with varchar(255) because I saw the output.

TIA,

steve

Question: do you want these to be foreign key references? Because thats not what the above mapping does. The above is most likely serializing instances of type Address into the "address" and "office" columns. If you wanted FK references, you should be using the <many-to-one> mapping for these:

http://www.hibernate.org/hib_docs/reference/html/or-mapping.html#or-mapping-s1-8

Otherwise, what is the problem? Just that you have to specify the size?

There is a DB2 config parameter which makes this exception go away. It is a common problem not at all related to Hibernate.

Hi,

Steve, Gavin, thanks so much!

Steve must be right saying that Hibernate is trying to serialize the objects into DB2 columns. That explains the need for such a big VARCHAR value for the serialized objects!

Actually, I wanted only to go with FK, not the entire serialized object. So Steve's suggestion makes perfect sense.

Thanks Gavin for pointing out the need for a specific DB setting (in any case).

Best,

Steve.

Of course, steve saw the problem immediately which I missed (actually my mind read them as references.. doh!), but I would still like to point out that had you printed out the sql this problem would probably been quite obvious much sooner, when you saw the serialized object being inserted.