Re: Problem with simple report query

Thu, 21 Sep 2006 13:14:42 -0700 

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]




---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to