Hi *, There was no answer so I retry because we have no solution and stuck on our project. Do you plan to study/solve our problem in the near future or, for any reason (time, resource...), should we go back to the 0.9.2 at the moment? [we migrated to the 0.9.3 only for using SQL functions like upper in our queries].
Cheers, Fabrice. -----Original Message----- From: Cyril INARDDARGENCE [mailto:[EMAIL PROTECTED]] Sent: mardi 25 septembre 2001 14:01 To: [EMAIL PROTECTED] Subject: [castor-dev] Bug in hypersonic sql generation when an object participates in two relationship. Hi, I encountered a problem using castor jdo (version 0.9.3 and 09/21 cvs snapshot) with hypersonc sql database (please note that all this was ok with castor 0.9.2). I use the following object model to illustrate the problem. All classes have an integer 'id' property serving as primary key. -------- 0..n ---------- 0..n --------- | City | ---------- | Street | ---------- | House | -------- ---------- --------- When I try to get a Street object using the following request : OQLQuery query = db.getOQLQuery( "SELECT o FROM Street o WHERE city.id = 0"); QueryResults results = query.execute(); Castor generates the sql query : SELECT Street.Id,House.Id,Street.CityRef FROM Street LEFT OUTER JOIN House ON (Street.Id=House.StreetRef), City WHERE Street.CityRef=City_0.Id AND City_0.Id = 0 which leads to following exception java.sql.SQLException: Column not found: ID in statement [<see sql above>] at org.hsql.Trace.getError(Trace.java:124) at org.hsql.Result.<init>(Result.java:70) at org.hsql.jdbcConnection.executeHSQL(jdbcConnection.java:644) at org.hsql.jdbcConnection.execute(jdbcConnection.java:540) at org.hsql.jdbcStatement.fetchResult(jdbcStatement.java:499) at org.hsql.jdbcStatement.executeQuery(jdbcStatement.java:37) at org.hsql.jdbcPreparedStatement.executeQuery(jdbcPreparedStatement.java:99) at org.exolab.castor.jdo.engine.SQLEngine$SQLQuery.execute(SQLEngine.java:1584) at org.exolab.castor.persist.TransactionContext.query(TransactionContext.java:6 44) at org.exolab.castor.jdo.engine.OQLQueryImpl.execute(OQLQueryImpl.java:457) at org.exolab.castor.jdo.engine.OQLQueryImpl.execute(OQLQueryImpl.java:413) obviously City_0 table does not exist and no alias has been defined in the query for the City table. Any idea. Thanks. Cyril. Following is my castor mapping and the classes for this example : // castor mapping ======================================================= <mapping> <class name="City" identity="id" > <description>TODO</description> <map-to table="City"/> <field collection="collection" type="Street" name="streets"> <sql many-key="CityRef"/> </field> <field type="integer" name="id"> <sql name="Id"/> </field> </class> <class name="Street" identity="id" > <description>TODO</description> <map-to table="Street"/> <field collection="collection" type="House" name="houses"> <sql many-key="StreetRef"/> </field> <field type="integer" name="id"> <sql name="Id"/> </field> <field name="city" type="City"> <sql name="CityRef"/> </field> </class> <class name="House" identity="id" > <description>TODO</description> <map-to table="House"/> <field type="integer" name="id"> <sql name="Id"/> </field> <field name="street" type="Street"> <sql name="StreetRef"/> </field> </class> </mapping> // City.java ========================================================== import java.util.*; public class City { private int _id; private Collection _streets ; public int getId() { return _id; } public void setId(int id) { _id = id; } public void setStreets(Collection streets) { _streets = streets; } public Collection getStreets() { return _streets; } } // Street.java ======================================================== import java.util.*; public class Street { private int _id; private City _city; private Collection _houses; public int getId() { return _id; } public void setId(int id) { _id = id; } public void setCity(City city) { _city = city; } public City getCity() { return _city; } public void setHouses(Collection houses) { _houses = houses; } public Collection getHouses() { return _houses; } } // House.java ======================================================== import java.util.*; public class House { private int _id; private Street street; public int getId() { return _id; } public void setId(int id) { _id = id; } public void setStreet(Street street) { _street = street; } public Street getStreet() { return _street; } } // Hypersonic SQL Script ================================================= CREATE TABLE City ( Id INTEGER NOT NULL PRIMARY KEY ); CREATE TABLE Street ( Id INTEGER NOT NULL PRIMARY KEY, CityRef INTEGER NOT NULL, FOREIGN KEY ( CityRef ) REFERENCES City (Id) ); CREATE TABLE House ( Id INTEGER NOT NULL PRIMARY KEY, StreetRef INTEGER NOT NULL, FOREIGN KEY ( StreetRef ) REFERENCES Street (Id) ); ----------------------------------------------------------- If you wish to unsubscribe from this mailing, send mail to [EMAIL PROTECTED] with a subject of: unsubscribe castor-dev ----------------------------------------------------------- If you wish to unsubscribe from this mailing, send mail to [EMAIL PROTECTED] with a subject of: unsubscribe castor-dev
