hi denis, actually it's my fault, i changed the implementation of getCount() using ReportQueries, but i didn't take care of m:n :-(
jakob ----- Original Message ----- From: "Ahearn, Denis" <[EMAIL PROTECTED]> To: "'OJB Users List'" <[EMAIL PROTECTED]> Sent: Tuesday, October 01, 2002 7:51 PM Subject: RE: Invalid SQL query executed when loading a proxy-based m-to-n collection > Thanks Jakob, I appreciate you taking time to look into this for me. > > Denis > > -----Original Message----- > From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, October 01, 2002 12:19 PM > To: OJB Users List > Subject: Re: Invalid SQL query executed when loading a proxy-based m-to-n > collection > > > hi denis, > > i can reproduce your problem using person and projects. i'll check it asap. > > jakob > > ----- Original Message ----- > From: "Ahearn, Denis" <[EMAIL PROTECTED]> > To: "'OJB Users List'" <[EMAIL PROTECTED]> > Sent: Tuesday, October 01, 2002 5:03 PM > Subject: Invalid SQL query executed when loading a proxy-based m-to-n > collection > > > > I'm experiencing a problem in OJB (0.9.6) with an m-to-n collection > > that uses proxy-based lazy loading. > > > > When I try to retrieve the iterator for the collection, I get the > following > > exception: > > > > 26219 [main] ERROR org.apache.ojb.broker.accesslayer.JdbcAccess - > > SQLException during the execution of the query (for a > > com.firepond.domain.QuoteDiscount): [Microsoft][SQLServer 2000 Driver > > for JDBC][SQLServer]The column prefix 'R_QUOTATION_QUOTE_DISCOUNT_L' > > does not match with a table name or alias name used in the query. > > java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for > > JDBC][SQLServer]The column prefix 'R_QUOTATION_QUOTE_DISCOUNT_L' does > > not match with a table name or alias name used in the query. at > > com.microsoft.jdbc.base.BaseExceptions.createException(Unknown > > Source) > > at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown > > Source) > > at > > com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown > > Source) > > at > > com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown > > Source) > > at > > com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unkno > > wn > > Source) > > at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown > > Source) > > at > > com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.openCursor(Unknown > Source) > > at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.execute(Unknown > Source) > > at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown > > Source) > > at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown > > Source) at > > com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source) > > at > > > com.p6spy.engine.spy.P6PreparedStatement.executeQuery(P6PreparedStatement.ja > > va:192) > > at > > > org.apache.ojb.broker.accesslayer.JdbcAccess.executeQuery(JdbcAccess.java:24 > > 1) > > at > > org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:23 > > 9) > > at > > > org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(ReportQueryRs > > Iterator.java:84) > > at > > > org.apache.ojb.broker.singlevm.ReportRsIteratorFactoryImpl.createRsIterator( > > ReportRsIteratorFactoryImpl.java:96) > > at > > > org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getRsIteratorFromQuery( > > PersistenceBrokerImpl.java:1965) > > at > > > org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getReportQueryIteratorF > > romQuery(PersistenceBrokerImpl.java:1982) > > at > > > org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getReportQueryIteratorB > > yQuery(PersistenceBrokerImpl.java:1907) > > at > > > org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCount(PersistenceBro > > kerImpl.java:1888) > > at > > > org.apache.ojb.broker.accesslayer.CollectionProxy.loadSize(CollectionProxy.j > > ava:110) > > at > > > org.apache.ojb.broker.accesslayer.CollectionProxy.size(CollectionProxy.java: > > 159) > > at > > > org.apache.ojb.broker.accesslayer.CollectionProxy.isEmpty(CollectionProxy.ja > > va:170) > > at > > > org.apache.ojb.broker.accesslayer.CollectionProxy.loadData(CollectionProxy.j > > ava:130) > > at > > > org.apache.ojb.broker.accesslayer.CollectionProxy.getData(CollectionProxy.ja > > va:315) > > at > > > org.apache.ojb.broker.accesslayer.CollectionProxy.iterator(CollectionProxy.j > > ava:187) > > at test.TestQuoteDomain.dumpQuote(TestQuoteDomain.java:799) > > at > > test.TestQuoteDomain.testCreateDeleteQuote(TestQuoteDomain.java:121) > > at java.lang.reflect.Method.invoke(Native Method) > > at junit.framework.TestCase.runTest(TestCase.java:166) > > at junit.framework.TestCase.runBare(TestCase.java:140) > > at junit.framework.TestResult$1.protect(TestResult.java:106) > > at junit.framework.TestResult.runProtected(TestResult.java:124) > > at junit.framework.TestResult.run(TestResult.java:109) > > at junit.framework.TestCase.run(TestCase.java:131) > > at junit.framework.TestSuite.runTest(TestSuite.java:173) > > at junit.framework.TestSuite.run(TestSuite.java:168) > > at > > > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRu > > nner.java:329) > > at > > > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner. > > java:218) > > at > > > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner > > .java:151) > > > > This is the collection descriptor from repository.xml for my > > collection: > > > > <collection-descriptor name="discount" > > element-class-ref="com.firepond.domain.QuoteDiscount" > > indirection-table="R_QUOTATION_QUOTE_DISCOUNT_L" proxy="true" > > auto-update="true" > > > <fk-pointing-to-this-class column="QUOT_ID"/> > > <fk-pointing-to-element-class column="DISCOUNT_ID"/> > > </collection-descriptor> > > > > This is the invalid SQL query as reported by p6spy: > > > > 1033480357984|16|0|statement|SELECT count(*) FROM R_QUOTATION_DISCOUNT > > 1033480357984|16|0|statement|A0 > > WHERE (R_QUOTATION_QUOTE_DISCOUNT_L.QUOT_ID = ? ) AND > > R_QUOTATION_QUOTE_DISCOUNT_L.DISCOUNT_ID = A0.DISCOUNT_ID|SELECT > > count(*) FROM R_QUOTATION_DISCOUNT A0 WHERE > > (R_QUOTATION_QUOTE_DISCOUNT_L.QUOT_ID = '4000000100352' ) AND > > R_QUOTATION_QUOTE_DISCOUNT_L.DISCOUNT_ID = A0.DISCOUNT_ID > > > > It seems that the name of the indirection table is missing from the > > FROM portion of the SQL query. The SQL query needs to be structured > > like the > > following: > > > > SELECT count(*) FROM R_QUOTATION_DISCOUNT A0, > > R_QUOTATION_QUOTE_DISCOUNT_L B0 WHERE (B0.QUOT_ID = ? ) AND > > B0.DISCOUNT_ID = A0.DISCOUNT_ID > > > > When I set proxy="false" on the collection, the problem goes away as > > you would expect. However for performance reasons we would really > > like to use proxy loading. Is this a known bug and if so, any idea > > when it will be fixed? > > > > Thanks, > > Denis > > > > > -- > To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> > For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> > > -- > To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> > For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> > -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
