Hi,

I have to work on a legacy dbase with composite primary keys.
Using postgres dbase, I get following error report:

18:13:57,593 DEBUG BatcherImpl:227 - preparing statement
18:13:57,671 DEBUG JDBCExceptionReporter:36 - SQL Exception
java.sql.SQLException: ERROR: pg_atoi: error in "vvv": can't parse "vvv"

The short version of the error is:

Object A has a one to many relation to B.
B has on its turn a one to many relation to C.

In the mapping file of A, I set lazy loading to false, in the mapping file of B I set lazy loading to true. It's possible to load object A and all the objects B in the collection that are related to object A.

The problem occurs when in the mapping file of B I set lazy loading to false. When loading A I would have expected to load A, all the B child objects and all the C child objects of B. But then I get the error pg_atoi.

This is a postgres error complaining that vvv is not an integer ... (that"s what I've found so far). but vvv should be a string.

To explain the meaning of the vvv, I have to describe the setup in more detail:

This is the situation:


Object Derde has a one to many relation to DerdeRol.
DerdeRol has on its turn a one to many relation to DerdeRolErk.

Mapping Files:
for Derde.hbm.xml :

<class name="Derde" table="derde" >
<id name="id" column="derde_id" type="int">
<generator class="native"/>
</id>
<!-- bi-directional one-to-many association to DerdeRol -->
<set name="roles" inverse="true" lazy="false" cascade="all-delete-orphan">
<key column="derde_id" />
<one-to-many class="DerdeRol" />
</set >
********************************
for DerdeRol.hbm.xml:

<class name="DerdeRol" table="derde_rol" >
<composite-id name="compId" class="DerdeRolCompId">
<key-property name="rolId" column="rol_id" type="string" />
<!-- bi-directional many-to-one association to Derde -->
<key-many-to-one name="derde" class="Derde" >
<column name="derde_id" />
</key-many-to-one>
</composite-id>
<!-- bi-directional one-to-many association to DerdeRolErk -->
<set name="erkenningen" lazy="false" inverse="true" cascade="all">
<key>
<column name="derde_id" />
<column name="rol_id" sql-type="string"/>
</key>
<one-to-many class="DerdeRolErk" />
</set>
********************************************
for DerdeRolErk.hbm.xml:

<class name="DerdeRolErk" table="derde_rol_erk">
<composite-id name="compId" class="DerdeRolErkCompId" >
<key-property name="startDatum" column="start_d" type="java.util.Date" />
<!-- bi-directional many-to-one association to DerdeRol -->
<key-many-to-one name="derdeRol" class="DerdeRol" >
<column name="derde_id" />
<column name="rol_id" sql-type="string"/>
</key-many-to-one>
</composite-id>

**********************************************

the java classes that construct the composite ids:

public class DerdeRolCompId implements Serializable{

private Derde derde;
private String rolId;


public class DerdeRolErkCompId implements Serializable{

private DerdeRol derdeRol;
private Date startDatum;



**********************************************

the relevant sql statements that built the postgres dbase:

create table derde (
DERDE_ID int PRIMARY KEY not null,

create table derde_rol(
DERDE_ID int not null,
ROL_ID char(3) not null,
primary key (DERDE_ID,ROL_ID))

create table derde_rol_erk(
DERDE_ID int not null,
ROL_ID char(3) not null,
START_D date not null,
primary key (DERDE_ID, ROL_ID,START_D))


*************************************************

It seems my A to B relationship is working fine with the composite ids, I can create, update, delete, fetch, etc... both parent and child objects.

But I can't figure out what's wrong with the B to C relation in the mappings ...

Can anyone point me in the right direction ?

tx a lot !

kazroh

Well I can't see where that vvv value comes from - most likely your column types do not match the types in your mapping/objects. Check the generated SQL.

Sorry for the incomplete information ...

in a test program I created first some parent objects (A), then adding to
these parents some child objects (B). The composite id of the child objects consist of an int derdeId and a String rolId. The composite id of the child objects C of the previous child objects B consists of int derdeId, String rolId and Date startDate.

The rol_id in the dbase is a varchar(3). rol_id column was bpchar before but I changed this to varchar to rule out the possibility that this would cause this error.

One of the child members I gave the rolId of 'vvv', so that is where this 'vvv' comes from.

In the object the id is defined as String, in the mapping files I use:

<key-property name="rolId" column="rol_id" type="string" />
.....
<key>
<column name="derde_id" />
<column name="rol_id" sql-type="string"/>
</key>
......
<key-many-to-one name="derdeRol" class="DerdeRol" >
<column name="derde_id" />
<column name="rol_id" sql-type="string"/>
</key-many-to-one>

since the problem only seems to occur in the relationship B to C, I guess that the last piece of mapping code shown above is not 100% correct.

tx,
kazroh

Take a look at the generated sql and the bound parameters, Enable logging and check what is actually done.

bound parameters ? I checked the manual, the only reference to bound I found in tomcat implementations. For the time being, I'm testing it stand alone.

Maybe the problem is in the way I try to load the parent and all it's child objects: I do this with the session.find() method., can this be done ?

Below 2 parts of logging: the first shows the loading of a Parent object Derde to which no child objects DerdeRol are connected.

17:53:32,296 DEBUG SessionImpl:3132 - initializing collection [Derde.roles#55]
17:53:32,296 DEBUG BatcherImpl:192 - about to open: 0 open PreparedStatements, 0 open ResultSets
17:53:32,296 DEBUG SQL:223 - select roles0_.rol_id as rol_id__, roles0_.derde_id as derde_id__, roles0_.rol_id as rol_id0_, roles0_.derde_id as derde_id0_, roles0_.toepassingsnummer as toepassi3_0_, roles0_.klassenummer as klassenu4_0_, roles0_.titularis_id as titulari5_0_, roles0_.checkdigit as checkdigit0_, roles0_.password as password0_ from derde_rol roles0_ where roles0_.derde_id=?
Hibernate: select roles0_.rol_id as rol_id__, roles0_.derde_id as derde_id__, roles0_.rol_id as rol_id0_, roles0_.derde_id as derde_id0_, roles0_.toepassingsnummer as toepassi3_0_, roles0_.klassenummer as klassenu4_0_, roles0_.titularis_id as titulari5_0_, roles0_.checkdigit as checkdigit0_, roles0_.password as password0_ from derde_rol roles0_ where roles0_.derde_id=?
17:53:32,296 DEBUG BatcherImpl:227 - preparing statement
17:53:32,343 DEBUG Loader:326 - result set contains (possibly empty) collection: [Derde.roles#55]
17:53:32,343 DEBUG SessionImpl:2902 - uninitialized collection: initializing
17:53:32,343 DEBUG Loader:196 - processing result set
17:53:32,359 DEBUG Loader:225 - done processing result set (0 rows)
17:53:32,359 DEBUG BatcherImpl:199 - done closing: 0 open PreparedStatements, 0 open ResultSets
17:53:32,359 DEBUG BatcherImpl:240 - closing statement
17:53:32,359 DEBUG Loader:238 - total objects hydrated: 0
17:53:32,359 DEBUG SessionImpl:2961 - 1 collections were found in result set
17:53:32,359 DEBUG SessionImpl:2979 - collection fully initialized: [Derde.roles#55]
17:53:32,359 DEBUG SessionImpl:2982 - 1 collections initialized


***********************************************
This is the second part of the logging, for a parent object Derde with ID 38 to which several child objects derderol are connected. The rol_id of the first child object derderol is shown to be vvv, like it is in the dbase.


17:53:32,890 DEBUG SessionImpl:3132 - initializing collection [DerdeRol.erkenningen#DerdeRolCompId@992bae[derde=Derde@3ae941[derdeId=38],rolId=vvv]]
17:53:32,890 DEBUG BatcherImpl:192 - about to open: 0 open PreparedStatements, 0 open ResultSets
17:53:32,890 DEBUG SQL:223 - select erkenninge0_.start_d as start_d__, erkenninge0_.derde_id as derde_id__, erkenninge0_.rol_id as rol_id__, erkenninge0_.start_d as start_d0_, erkenninge0_.derde_id as derde_id0_, erkenninge0_.rol_id as rol_id0_, erkenninge0_.eind_d as eind_d0_, erkenninge0_.timestamp as timestamp0_, erkenninge0_.reden_desactiv as reden_de6_0_ from derde_rol_erk erkenninge0_ where erkenninge0_.derde_id=? and erkenninge0_.rol_id=?
Hibernate: select erkenninge0_.start_d as start_d__, erkenninge0_.derde_id as derde_id__, erkenninge0_.rol_id as rol_id__, erkenninge0_.start_d as start_d0_, erkenninge0_.derde_id as derde_id0_, erkenninge0_.rol_id as rol_id0_, erkenninge0_.eind_d as eind_d0_, erkenninge0_.timestamp as timestamp0_, erkenninge0_.reden_desactiv as reden_de6_0_ from derde_rol_erk erkenninge0_ where erkenninge0_.derde_id=? and erkenninge0_.rol_id=?
17:53:32,890 DEBUG BatcherImpl:227 - preparing statement
17:53:32,968 DEBUG JDBCExceptionReporter:36 - SQL Exception
java.sql.SQLException: ERROR: pg_atoi: error in "vvv": can't parse "vvv"

at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:153)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:909)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:884)
at net.sf.hibernate.loader.OneToManyLoader.initialize(OneToManyLoader.java:80)
at net.sf.hibernate.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:284)
at net.sf.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:3133)
at net.sf.hibernate.collection.PersistentCollection.forceInitialization(PersistentCollection.java:331)
at net.sf.hibernate.impl.SessionImpl.initializeNonLazyCollections(SessionImpl.java:3007)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:137)
at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
at net.sf.hibernate.loader.Loader.list(Loader.java:940)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at be.javasoft.business.model.Derde.fetchAll(Derde.java:254)
at be.javasoft.business.model.Main.main(Main.java:66)
17:53:32,968 WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: null
17:53:32,968 ERROR JDBCExceptionReporter:46 - ERROR: pg_atoi: error in "vvv": can't parse "vvv"

in a desperate attempt to find my error, I rebuilt the dbase so that rol_id is of type integer in stead of string ...

and now everything works fine ....

a minor detail is that I have to work on a legacy dbase so I don't have the freedom to alter the type of rol_id ....

anyhow, iI have to look for a wrong int to string conversion ...

except for the java objects, the mapping files, where can this go wrong ?
in the hibernate.properties file maybe ?

tx,
kazroh

I am quite sure that you are somewhere mapping this string column to an int or Integer property. Check your code and mappings, Hibernate will NOT automatically convert String results from the database to ints. If you need to do such things (persisting ints in varchar columns or such stuff) use a custom UserType.

Gloeglm,

In the end I got it working by swapping the order of the mapping declarations of the one-to-many relation. Below you 'll find the old mapping file, under the ****** you'll find the one that is working., just by swapping the order of the column declarations ..... Same for the many-to-one declarattion in the mapping file derderolerk.hbm.xml

can you confirm that the order really does matter, or was I just lucky ?

Anyway, everything seems fine now, I even added an additional one to many relation to have A -> B -> C -> D relation between the objects.

Thanks a lot for your quick replies, they kept me motivated to continue to stare into the eclipse debug perspective ;-)

cheers,
kazroH

-----------------------------------

mapping file DerdeRol.hbm.xml:

<class name="DerdeRol" table="derde_rol" >
<composite-id name="compId" class="DerdeRolCompId">
<key-property name="rolId" column="rol_id" type="string" />
<!-- bi-directional many-to-one association to Derde -->
<key-many-to-one name="derde" class="Derde" >
<column name="derde_id" />
</key-many-to-one>
</composite-id>
<!-- bi-directional one-to-many association to DerdeRolErk -->
<set name="erkenningen" lazy="false" inverse="false" cascade="all">
<key>
<column name="derde_id" />
<column name="rol_id" sql-type="string"/>
</key>
<one-to-many class="DerdeRolErk" />
</set>
********************************************
<class name="DerdeRol" table="derde_rol" >
<composite-id name="compId" class="DerdeRolCompId" >
<key-property name="rolId" column="rol_id" type="String" />
<!-- bi-directional many-to-one association to Derde -->
<key-many-to-one name="derde" class="Derde" >
<column name="derde_id" />
</key-many-to-one>
</composite-id>
<!-- bi-directional one-to-many association to DerdeRolErk -->
<set name="erkenningen" inverse="true" lazy="false" cascade="all">
<key>
<column name="rol_id" sql-type="char" />
<column name="derde_id" />
</key>
<one-to-many class="DerdeRolErk" />
</set>

Yes it really matter, Hibernate may not know how to distinct 2 columns in a FK, so it use the declared order of composite-id