Re: Problem with simple report query

Armin Waibel <[email protected]>
Newsgroups gmane.comp.jakarta.ojb.user
Message-ID <[email protected]> 
Hi Aaron,

 > 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 don't have problems to perform a similar report query (with latest 
from SVN OJB_1_0_RELEASE branch).
Think the problem is that you use the column names instead of the field 
names (the Criteria query api is java field base, try to avoid column 
names in queries), please replace "id" with "_id" and try again.

regards,
Armin


Aaron Dixon wrote:
> 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]
> 
>
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.