Jakob
I have a test case to show my problem
public void testReportQuery() {
testDeleteContractVersionEffectiveness();
createTestData();
Query query = QueryFactory.newReportQuery(Version.class, new
String[]{"PK","contract.PK","contract.contractValue1"}, null, false);
Iterator iter = broker.getReportQueryIteratorByQuery(query);
int rowCount = 0;
while ( iter.hasNext() ) {
rowCount++;
}
assertTrue("Expected to get " + COUNT + " rows in the report query.
Got " + rowCount + " instead.", rowCount == COUNT);
}
Add it to the ContractVersionEffectivenessTest class. I had to chage the
primary key column names to PK from pk in
reposotory_junit.xml for the three mappings involved thus:
<!--
Definitions for contract-version-effectiveness testing
Contract-Version-Effectiveness is a common model in business
see martin fowler's pages on temporal patterns.
-->
<class-descriptor
class="org.apache.ojb.broker.Contract"
table="Contract"
>
<field-descriptor
name="pk"
column="PK"
jdbc-type="VARCHAR"
primarykey="true"
autoincrement="false"
/>
<field-descriptor
name="contractValue1"
column="contract_value1"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="contractValue2"
column="contract_value2"
jdbc-type="INTEGER"
/>
<field-descriptor
name="contractValue3"
column="contract_value3"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="contractValue4"
column="contract_value4"
jdbc-type="TIMESTAMP"
/>
<field-descriptor
name="fkToRelated"
column="fk_to_related"
jdbc-type="VARCHAR"
/>
<reference-descriptor
name="relatedToContract"
class-ref="org.apache.ojb.broker.RelatedToContract"
>
<foreignkey field-ref="fkToRelated"/>
</reference-descriptor>
</class-descriptor>
<class-descriptor
class="org.apache.ojb.broker.RelatedToContract"
table="RelatedToContract"
>
<field-descriptor
name="pk"
column="PK"
jdbc-type="VARCHAR"
primarykey="true"
autoincrement="false"
/>
<field-descriptor
name="relatedValue1"
column="relatedValue1"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="relatedValue2"
column="relatedValue2"
jdbc-type="INTEGER"
/>
<field-descriptor
name="relatedValue3"
column="relatedValue3"
jdbc-type="TIMESTAMP"
/>
</class-descriptor>
<class-descriptor
class="org.apache.ojb.broker.Version"
table="Version"
>
<field-descriptor
name="pk"
column="PK"
jdbc-type="VARCHAR"
primarykey="true"
autoincrement="false"
/>
<field-descriptor
name="versionValue1"
column="version_value1"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="versionValue2"
column="version_value2"
jdbc-type="INTEGER"
/>
<field-descriptor
name="versionValue3"
column="version_value3"
jdbc-type="TIMESTAMP"
/>
<field-descriptor
name="fkToContract"
column="fk_to_contract"
jdbc-type="VARCHAR"
/>
<reference-descriptor
name="contract"
class-ref="org.apache.ojb.broker.Contract"
>
<foreignkey field-ref="fkToContract"/>
</reference-descriptor>
</class-descriptor>
<class-descriptor
class="org.apache.ojb.broker.Effectiveness"
table="Effectiveness"
>
<field-descriptor
name="pk"
column="PK"
jdbc-type="VARCHAR"
primarykey="true"
autoincrement="false"
/>
<field-descriptor
name="effValue1"
column="eff_value1"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="effValue2"
column="eff_value2"
jdbc-type="INTEGER"
/>
<field-descriptor
name="effValue3"
column="eff_value3"
jdbc-type="TIMESTAMP"
/>
<field-descriptor
name="fkToVersion"
column="fk_to_version"
jdbc-type="VARCHAR"
/>
<reference-descriptor
name="version"
class-ref="org.apache.ojb.broker.Version"
>
<foreignkey field-ref="fkToVersion"/>
</reference-descriptor>
</class-descriptor>
<!-- end mappings for contract-version-effectiveness -->
If I use the field names in the report query (e.g. lower case 'pk') if works
fine. Using upper case values (e.g. 'PK')
and the database (in my case SQLServer) I get the following stack trace:
[org.apache.ojb.broker.accesslayer.JdbcAccessImpl] ERROR: SQLException
during the execution of the query (for a org.apache.ojb.broker.Version):
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Ambiguous column name
'pk'.
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Ambiguous column name
'pk'.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Ambiguous column name 'pk'.
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(Unknown
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
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl
.java:291)
at
org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:175)
at
org.apache.ojb.broker.accesslayer.ReportQueryRsIterator.<init>(ReportQueryRs
Iterator.java:86)
at
org.apache.ojb.broker.singlevm.ReportRsIteratorFactoryImpl.createRsIterator(
ReportRsIteratorFactoryImpl.java:96)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getRsIteratorFromQuery(
PersistenceBrokerImpl.java:2147)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getReportQueryIteratorF
romQuery(PersistenceBrokerImpl.java:2195)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getReportQueryIteratorB
yQuery(PersistenceBrokerImpl.java:2124)
at
org.apache.ojb.broker.singlevm.DelegatingPersistenceBroker.getReportQueryIte
ratorByQuery(DelegatingPersistenceBroker.java:273)
at
org.apache.ojb.broker.ContractVersionEffectivenessTest.testReportQuery(Contr
actVersionEffectivenessTest.java:184)
and the following generated SQL:
[junit] [org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl]
DEBUG: SQL: SELECT PK,PK,A1.contract_value1 FROM Version A0 LEFT OUTER JOIN
Contract A1 ON A0.fk_to_contract=A1.PK
I hope that helps!
Mark
BTW -- what is the "policy" on selecting our by field name or column name
and filtering by the same?
-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Friday, February 21, 2003 4:41 PM
To: OJB Users List
Subject: Re: Report queries on extents
hi mark,
yes, please send me the testcase. if it's based on our testclasses it
would be perfect.
jakob
Mark Rowell wrote:
>Hi Jakob
>
>Some news on this problem -- it turns out it is a case sensitivity issue.
>E.g. If I query on the column name in the report
>query (e.g. upper case 'ID') it does not work, if I query on the field name
>(e.g. lower case 'id') it works.
>
>N.B. this therefore is only a problem where we have 2 columns in the query
>with the same names on different tables. E.g. if I query by
>column name rather than field name the sql generator strips off any
>qualification I put in.
>
>I do have some new classes/data/tests that show this.
>Do you want me to send them to you?
>
>Mark
>-----Original Message-----
>From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, February 19, 2003 6:25 PM
>To: OJB Users List
>Subject: Re: Report queries on extents
>
>
>hi mark,
>
>i forgot to ask whether you coukd adapt my test case so it matches the
>one you have problems with ?
>
>jakob
>
>Mark Rowell wrote:
>
>
>
>>Hi Jakob
>>
>>Thanks for that but my generated SQL does not qualify the columns I was
>>selecting and hence
>>I get a non unique name for the ID column reported by SQL Server. One
>>difference between the example you give and
>>my example is that my criteria filters on a couple of values in a
>>
>>
>collection
>
>
>>attribute of the class I am querying.
>>E.g. to extend the example you give:
>>
>> public void testReportPathExpressionForExtents()
>> {
>> ArrayList list = new java.util.ArrayList();
>>
>> Criteria crit = new Criteria();
>> crit.addEqualTo("groupId", new Integer(5));
>> crit.addEqualTo("allArticlesInGroup.articleName", "some name");
>>
>> ReportQueryByCriteria q =
>>QueryFactory.newReportQuery(ProductGroup.class, crit, true);
>> q.setColumns(new String[]{"groupId", "groupName",
>>"allArticlesInGroup.articleId", "allArticlesInGroup.articleName"});
>>
>> Iterator iter = broker.getReportQueryIteratorByQuery(q);
>> while (iter.hasNext())
>>
>>Additionally, is ProductGroup an extent? The class I query is the top
level
>>of an extent.
>>
>>I will make a change to the test you give so it looks like my query and
>>
>>
>then
>
>
>>see what results I get.
>>I will also send you the generated SQL.
>>
>>Regards & thanks
>>
>>Mark
>>
>>
>>-----Original Message-----
>>From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
>>Sent: Wednesday, February 19, 2003 4:48 PM
>>To: OJB Users List
>>Subject: Re: Report queries on extents
>>
>>
>>hi mark,
>>
>>yes, report queries are extent aware. the method below is taken from the
>>testcase QueryTest and it produces the query shown further down.
>>
>> public void testReportPathExpressionForExtents()
>> {
>> ArrayList list = new java.util.ArrayList();
>>
>> Criteria crit = new Criteria();
>> crit.addEqualTo("groupId", new Integer(5));
>>
>> ReportQueryByCriteria q =
>>QueryFactory.newReportQuery(ProductGroup.class, crit, true);
>> q.setColumns(new String[]{"groupId", "groupName",
>>"allArticlesInGroup.articleId", "allArticlesInGroup.articleName"});
>>
>> Iterator iter = broker.getReportQueryIteratorByQuery(q);
>> while (iter.hasNext())
>> {
>> list.add(iter.next());
>> }
>>
>> // 7 Articles, 2 Books, 3 Cds
>> assertEquals("check size", list.size(), 7);
>> }
>>
>>SELECT DISTINCT
>>A0.Kategorie_Nr,A0.KategorieName,A1.Artikel_Nr,A1.Artikelname FROM
>>Kategorien A0 LEFT OUTER JOIN Artikel A1 ON
>>A0.Kategorie_Nr=A1.Kategorie_Nr LEFT OUTER JOIN BOOKS A1E0 ON
>>A0.Kategorie_Nr=A1E0.Kategorie_Nr LEFT OUTER JOIN CDS A1E1 ON
>>A0.Kategorie_Nr=A1E1.Kategorie_Nr WHERE A0.Kategorie_Nr = '5'
>>
>>hth
>>jakob
>>
>>Mark Rowell wrote:
>>
>>
>>
>>
>>
>>>Hi
>>>
>>>I was wondering if report queries on extents work where I am selecting
out
>>>a/some column(s) from a table
>>>related to the extent classes. A concrete example may help (!)
>>>
>>>I have an extent class called Bond with concrete implementations called
>>>FixedCouponBond and FRN.
>>>There is a 1 to n relationship a single Bond and the priceDetails class
>>>(which is concrete). In my
>>>repository_user.xml file they mappings are setup as follows (I only show
>>>themapping for the FixedCouponBond class
>>>as it is identical for the FRN class)
>>>
>>> <class-descriptor
>>> class="com.credittrade.instruments.securities.BBGBondModel"
>>>
>>> table="Bonds"
>>> >
>>> <field-descriptor id="1"
>>> name="id"
>>> column="ID"
>>> jdbc-type="INTEGER"
>>> primarykey="true"
>>> autoincrement="true"
>>> />
>>> etc
>>> <collection-descriptor
>>> name="priceDetails"
>>> element-class-ref="com.credittrade.market.PriceDetail"
>>> auto-retrieve="true"
>>> auto-update="false"
>>> auto-delete="false"
>>> proxy="true"
>>> >
>>> <inverse-foreignkey field-id-ref="2"/>
>>> </collection-descriptor>
>>> etc
>>> </class-descriptor>
>>>
>>>priceDetail is mapped as follows:
>>>
>>> <class-descriptor
>>> class="com.credittrade.market.PriceDetail"
>>> table="PriceDetails">
>>> <field-descriptor id="1"
>>> name="id"
>>> column="ID"
>>> jdbc-type="INTEGER"
>>> primarykey="true"
>>> autoincrement="true"
>>> />
>>> <field-descriptor id="2"
>>> name="instrumentID"
>>> column="InstrumentID"
>>> jdbc-type="INTEGER"
>>> />
>>> <field-descriptor id="3"
>>> name="priceValueType"
>>> column="typeID"
>>> etc...
>>> </class-descriptor>
>>>
>>>
>>>I run the following report query to retrieve the ISIN field from the
>>>FixedCouponBond and the
>>>ID of the associated price details. I do this using the OJB API thus:
>>>
>>>// Java snippet
>>> Criteria criteria = new Criteria();
>>> criteria.addEqualTo("priceDetails.PriceSource", ps.getLabel());
>>> criteria.addEqualTo("priceDetails.TypeID",
>>>PriceValueType.price.toString());
>>> criteria.addEqualTo("priceDetails.PriceTypeID",
>>>PriceType.market.toString());
>>> criteria.addIn("priceDetails.QuoteTypeID", Arrays.asList( new
>>>String[]{
>>> QuoteType.bid.toString(),
>>> QuoteType.offer.toString(),
>>> QuoteType.high.toString(),
>>> QuoteType.low.toString()}));
>>> criteria.addIn("ISIN", isins);
>>>
>>> Iterator iter = broker.getReportQueryIteratorByQuery(
>>> QueryFactory.newReportQuery(
>>> Bond.class,
>>> new String[]{"ISIN", "priceDetails.ID",
>>>"priceDetails.QuoteTypeID"},
>>> criteria,
>>> false));
>>>
>>>Note in the method call to newReportQuery I ask for the ISIN field from
>>>
>>>
>the
>
>
>>>Bond class (no qualifier as it is the
>>>class in the query), and the ID and QuoteTypeID columns from the related
>>>priceDetails instances.
>>>
>>>The problem I get is that the generated SQL strips out the identifiers
and
>>>because I have an ID column on my
>>>Bond tables I get a SQL error complaining that ID is not a unique column
>>>name.
>>>
>>>I do this sort of report query elsewhere, on concrete classes directly
and
>>>it works -- hence I assume it is a problem with
>>>the SQL generator (?) for extents.
>>>
>>>I can get round it by specifying A1 as the qualifier for the ID and
>>>QuoteTypeID columns (as this, I noticed, is the
>>>table alias used by the SQL generator for the PriceDetails table.
>>>
>>>So, the questions I have are:
>>>
>>>(a) is this a bug, or
>>>(b) am Idoing something wrong...
>>>
>>>Regards,
>>>
>>>Mark Rowel
>>>
>>>----------------------------------------------------------------
>>>Mark Rowell
>>>CreditTrade
>>>
>>>T: +44 (020) 7400 5078
>>>M: mailto:[EMAIL PROTECTED]
>>>
>>>CreditTrade Limited is regulated by the FSA. (c) CreditTrade 2002. All
>>>
>>>
>>>
>>>
>>rights reserved. The information and data contained in this email is
>>provided for the information purposes of the addressee only and should not
>>be reproduced and/or distributed to any other person. It is provided
>>
>>
>without
>
>
>>any warranty whatsoever and unless stated otherwise consists purely of
>>indicative market prices and other information.
>>
>>
>>
>>
>>>Any opinion or comments expressed or assumption made in association with
>>>
>>>
>>>
>>>
>>the data or information provided in this email is a reflection of
>>CreditTrades judgement at the time of compiling the data and is subject to
>>change. CreditTrade hereby makes no representation and accepts no
>>responsibility or liability as to the completeness or accuracy of this
>>email.
>>
>>
>>
>>
>>>The content of this email is not intended as an offer or solicitation
for,
>>>
>>>
>>>
>>>
>>or recommendation of, the purchase or sale of any financial instrument, or
>>as an official confirmation of any transaction, and should not be
construed
>>as investment advice.
>>
>>
>>
>>
>>>---------------------------------------------------------------------
>>>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]
>>
>>CreditTrade Limited is regulated by the FSA. (c) CreditTrade 2002. All
>>
>>
>rights reserved. The information and data contained in this email is
>provided for the information purposes of the addressee only and should not
>be reproduced and/or distributed to any other person. It is provided
without
>any warranty whatsoever and unless stated otherwise consists purely of
>indicative market prices and other information.
>
>
>>Any opinion or comments expressed or assumption made in association with
>>
>>
>the data or information provided in this email is a reflection of
>CreditTrades judgement at the time of compiling the data and is subject to
>change. CreditTrade hereby makes no representation and accepts no
>responsibility or liability as to the completeness or accuracy of this
>email.
>
>
>>The content of this email is not intended as an offer or solicitation for,
>>
>>
>or recommendation of, the purchase or sale of any financial instrument, or
>as an official confirmation of any transaction, and should not be construed
>as investment advice.
>
>
>>---------------------------------------------------------------------
>>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]
>
>CreditTrade Limited is regulated by the FSA. (c) CreditTrade 2002. All
rights reserved. The information and data contained in this email is
provided for the information purposes of the addressee only and should not
be reproduced and/or distributed to any other person. It is provided without
any warranty whatsoever and unless stated otherwise consists purely of
indicative market prices and other information.
>
>Any opinion or comments expressed or assumption made in association with
the data or information provided in this email is a reflection of
CreditTrades judgement at the time of compiling the data and is subject to
change. CreditTrade hereby makes no representation and accepts no
responsibility or liability as to the completeness or accuracy of this
email.
>
>The content of this email is not intended as an offer or solicitation for,
or recommendation of, the purchase or sale of any financial instrument, or
as an official confirmation of any transaction, and should not be construed
as investment advice.
>
>---------------------------------------------------------------------
>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]
CreditTrade Limited is regulated by the FSA. (c) CreditTrade 2002. All rights
reserved. The information and data contained in this email is provided for the
information purposes of the addressee only and should not be reproduced and/or
distributed to any other person. It is provided without any warranty whatsoever and
unless stated otherwise consists purely of indicative market prices and other
information.
Any opinion or comments expressed or assumption made in association with the data or
information provided in this email is a reflection of CreditTrades judgement at the
time of compiling the data and is subject to change. CreditTrade hereby makes no
representation and accepts no responsibility or liability as to the completeness or
accuracy of this email.
The content of this email is not intended as an offer or solicitation for, or
recommendation of, the purchase or sale of any financial instrument, or as an official
confirmation of any transaction, and should not be construed as investment advice.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
