I have two OJB classes, VersionBean and Namespace. VersionBean has a
foreign key reference to a namespace; it's fields are:
===[BEGIN VersionBean.java]===
...
/**
* @ojb.field column="id"
* primarykey="true"
*/
private Integer _id;
/**
* @ojb.field column="value"
*/
private String _value;
/**
* @ojb.field column="namespaceId"
* nullable="true"
*/
private Integer _namespaceId;
/**
* @ojb.reference
* auto-update="none"
* auto-delete="false"
* class-ref="com.webify.prototype.domain.Namespace"
* foreignkey="_namespaceId"
*/
private Namespace _namespace;
...
===[END VersionBean.java]===
Namespace fields are like so:
===[BEING Namespace.java]===
...
/**
* @ojb.field column="id"
* primarykey="true"
* autoincrement="ojb"
*/
private Integer _id;
/**
* @ojb.field column="namespace"
*/
private String _namespace;
...
===[END Namespace.java]===
I want to perform a report query for the ids of VersionBeans that
reference namespaces with a given namespace value. I've tried this:
===[BEGIN Query #1]===
Criteria c = new Criteria();
c.addGreaterOrEqualThan("id", new Integer(1));
c.addEqualTo("_namespace.namespace", NS);
ReportQueryByCriteria query =
QueryFactory.newReportQuery(VersionBean.class, c);
query.setAttributes(new String[] {"id"});
Iterator i = broker.getReportQueryIteratorByQuery(query);
===[END Query #1]===
I get the following exception when this code executes:
===[BEGIN error from Query #1]===
org.apache.ojb.broker.PersistenceBrokerSQLException:
* Can't prepare statement:
* sql statement was 'SELECT id FROM p_version A0 INNER JOIN
p_namespace A1 ON A0.namespaceId=A1.id WHERE (id >= ?) AND namespace =
?'
* Exception message is [Column name 'ID' is in more than one table in
the FROM list.]
* Vendor error code [30000]
* SQL state code [42X03]
at
org.apache.ojb.broker.util.ExceptionHelper.generateException(ExceptionHelper.java:256)
... (full stack at bottom of this email message)
===[END error from Query #1]===
As I understand this, I need to disambiguate the id attribute using aliases:
===[BEGIN Query #2]===
Criteria c = new Criteria();
c.setAlias("VER");
c.addGreaterOrEqualThan("id", new Integer(1));
c.addEqualTo("_namespace.namespace", NS);
ReportQueryByCriteria query =
QueryFactory.newReportQuery(VersionBean.class, c);
query.setAttributes(new String[] {"VER.id"});
Iterator i = broker.getReportQueryIteratorByQuery(query);
===[END Query #2]===
===[BEGIN error from Query #2]===
[org.apache.ojb.broker.accesslayer.StatementsForClassImpl] ERROR:
* Can't prepare statement:
* sql statement was 'SELECT VER.id FROM p_version A0 INNER JOIN
p_namespace A1 ON A0.namespaceId=A1.id WHERE (id >= ?) AND namespace =
?'
* Exception message is [Column 'VER.ID' is either not in any table in
the FROM list or appears within a join specification and is outside
the scope of the join specification or appears in a HAVING clause and
is not in the GROUP BY list. If this is a CREATE or ALTER TABLE
statement then 'VER.ID' is not a column in the target table.]
* Vendor error code [30000]
* SQL state code [42X04]
* The root stack trace is -->
* ERROR 42X04: Column 'VER.ID' is either not in any table in the FROM
list or appears within a join specification and is outside the scope
of the join specification or appears in a HAVING clause and is not in
the GROUP BY list. If this is a CREATE or ALTER TABLE statement then
'VER.ID' is not a column in the target table.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
... (full stack at bottom of this email message)
===[END error from Query #2]===
Which of the APIs am I misunderstanding?
Thanks,
Aaron
Full error stacks for reference:
===[BEGIN error from Query #1]===
org.apache.ojb.broker.PersistenceBrokerSQLException:
* Can't prepare statement:
* sql statement was 'SELECT id FROM p_version A0 INNER JOIN
p_namespace A1 ON A0.namespaceId=A1.id WHERE (id >= ?) AND namespace =
?'
* Exception message is [Column name 'ID' is in more than one table in
the FROM list.]
* Vendor error code [30000]
* SQL state code [42X03]
at
org.apache.ojb.broker.util.ExceptionHelper.generateException(ExceptionHelper.java:256)
at
org.apache.ojb.broker.util.ExceptionHelper.generateException(ExceptionHelper.java:53)
at
org.apache.ojb.broker.accesslayer.StatementsForClassImpl.getPreparedStmt(StatementsForClassImpl.java:176)
at
org.apache.ojb.broker.accesslayer.StatementManager.getPreparedStatement(StatementManager.java:609)
at
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:269)
at
org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72)
at
org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:187)
at
org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(ReportQueryRsIterator.java:44)
at
org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(ReportRsIteratorFactoryImpl.java:59)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2073)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(PersistenceBrokerImpl.java:2120)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(PersistenceBrokerImpl.java:2047)
at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getReportQueryIteratorByQuery(DelegatingPersistenceBroker.java:362)
at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getReportQueryIteratorByQuery(DelegatingPersistenceBroker.java:362)
at com.webify.prototype.Driver.scenario4(Driver.java:124)
at com.webify.prototype.Driver.run(Driver.java:65)
at com.webify.prototype.Driver.main(Driver.java:46)
Caused by: SQL Exception: Column name 'ID' is in more than one table
in the FROM list.
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at
org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:168)
at $Proxy1.prepareStatement(Unknown Source)
at
org.apache.ojb.broker.accesslayer.StatementsForClassImpl.prepareStatement(StatementsForClassImpl.java:263)
at
org.apache.ojb.broker.accesslayer.StatementsForClassImpl.getPreparedStmt(StatementsForClassImpl.java:171)
... 14 more
===[END error from Query #1]===
===[BEGIN error from Query #2]===
[org.apache.ojb.broker.accesslayer.StatementsForClassImpl] ERROR:
* Can't prepare statement:
* sql statement was 'SELECT VER.id FROM p_version A0 INNER JOIN
p_namespace A1 ON A0.namespaceId=A1.id WHERE (id >= ?) AND namespace =
?'
* Exception message is [Column 'VER.ID' is either not in any table in
the FROM list or appears within a join specification and is outside
the scope of the join specification or appears in a HAVING clause and
is not in the GROUP BY list. If this is a CREATE or ALTER TABLE
statement then 'VER.ID' is not a column in the target table.]
* Vendor error code [30000]
* SQL state code [42X04]
* The root stack trace is -->
* ERROR 42X04: Column 'VER.ID' is either not in any table in the FROM
list or appears within a join specification and is outside the scope
of the join specification or appears in a HAVING clause and is not in
the GROUP BY list. If this is a CREATE or ALTER TABLE statement then
'VER.ID' is not a column in the target table.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown
Source)
at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(Unknown
Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown
Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown
Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
Source)
at org.apache.derby.impl.sql.compile.ReadCursorNode.bind(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.bind(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at
org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:168)
at $Proxy1.prepareStatement(Unknown Source)
at
org.apache.ojb.broker.accesslayer.StatementsForClassImpl.prepareStatement(StatementsForClassImpl.java:263)
at
org.apache.ojb.broker.accesslayer.StatementsForClassImpl.getPreparedStmt(StatementsForClassImpl.java:171)
at
org.apache.ojb.broker.accesslayer.StatementManager.getPreparedStatement(StatementManager.java:609)
at
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:269)
at
org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72)
at
org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:187)
at
org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(ReportQueryRsIterator.java:44)
at
org.apache.ojb.broker.core.ReportRsIteratorFactoryImpl.createRsIterator(ReportRsIteratorFactoryImpl.java:59)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2073)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorFromQuery(PersistenceBrokerImpl.java:2120)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getReportQueryIteratorByQuery(PersistenceBrokerImpl.java:2047)
at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getReportQueryIteratorByQuery(DelegatingPersistenceBroker.java:362)
at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getReportQueryIteratorByQuery(DelegatingPersistenceBroker.java:362)
at com.webify.prototype.Driver.scenario4(Driver.java:124)
at com.webify.prototype.Driver.run(Driver.java:65)
at com.webify.prototype.Driver.main(Driver.java:46)
===[END error from Query#2]===
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
