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]>
