db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tobias Hilka" <thi...@vps.de>
Subject AW: Limiting joins in Oracle
Date Tue, 10 Oct 2006 08:23:45 GMT
Hi Thomas,

The example I gave you was just a simple query string representing my problem. In reality
we are populating the objects from all tables involved in the query.

I would really appreciate if you could look at the problem.

Another question we are facing: Is there any special reason why the methods generated in all
subclasses of BasePeer (e.g. populateObject, row2Object...) and BaseObject are not declared
abstract in the superclasses so they can be used in a generic way? 
We are holding objects of a subclasses of BasePeer and we want to call populateObject on these
objects but we do not know which subclass it. I know you can not override methods in a static
way, but if you want to call a method like this there is no way to do this at the moment.
We found a way out of this situation by modifying the generator template Peer.vm. Now it generates
instance methods like populateObject2() which just call the static method (poulateObject())
of the right class. Do you think this could be a useful feature for other torque users as


-----Urspr√ľngliche Nachricht-----
Von: Thomas Fischer [mailto:tfischer@apache.org] 
Gesendet: Sonntag, 8. Oktober 2006 10:35
An: Apache Torque Developers List
Betreff: AW: Limiting joins in Oracle

Hi Tobias,

Thanks for reporting bach the result of my suggestions. I see that none of them has really
worked out, sorry for that.

I'd not recommend to change the poulateObject method. This would be coding around the error
after the harm is done, and changing the internals of Torque. This could produce unwanted

I have not looked at the code in detail, but from the example you gave you are using only
the selected rows from one table and not from several tables to populate your objects. Is
it not possible to use the asColumn for the tables which are not used for populating the objects

However, in my eyes, the only real solution would be to fix the error in the code which creates
oracle's join commands. I intend to have a go at this in the next few days, so if this works,
it'll be in the 3.2.1 release.


On Thu, 5 Oct 2006, Tobias Hilka wrote:

> Hello Thomas,
> Thank you for your help. But there are still some problems left:
> I tried out your second suggestion (the one with the asColumn).
> Since our tables are quite large (more than 100 columns), joining two 
> or more table could easily end up in having more than 200 select columns.
> Escaping each of this columns with an asColumn entry may cause oracle 
> to fail because the statement produced by torque is too long.
> Now my idea was to check for equaly named columns in the joined tables 
> and use the Criteria.AsColumn solution only for them. This would help 
> us with the length of the statement since only the necessary columns 
> are handled seperately.
> But if we do this, the order of the select columns in the results 
> returned from the database is not the original one (since torque first 
> processes the select columns and afterwards the asColumns [see 
> SQLBuilder.buildQueryClause()]). Therefore we can not use 
> populateObject to fill the java object with the values from the database.
> Actually I hoped to replace the original select column when adding the 
> same column to the Criteria.AsColumn list.
> Can you give me some advice on how to continue? I think writing my own 
> populateObject method is not a good idea.
> Concerning your other suggestions:
> - renaming the column names is not possible (as you already supposed).
> - Using the Criteria.CUSTOM: This would end up in writing pure sql 
> code which can be quite different for MSSQL and Oracle. Not a practical solution.
> As you told me, the problem with the oracle database is already known. 
> Will this problem be solved in your new release 3.2.1?
> Best regards,
> Tobias
> -----Urspr√ľngliche Nachricht-----
> Von: Thomas Fischer [mailto:tfischer@apache.org]
> Gesendet: Freitag, 22. September 2006 10:35
> An: Apache Torque Developers List
> Betreff: Re: Limiting joins in Oracle
> Sorry, I forgot to say what you might do to resolve your problem
> - Use Criteria.CUSTOM to create your Query manually (not nice, but 
> this will work for sure)
> - I'm not sure whether aliasing one conflicting Column name using 
> Criteria.AsColumn solves your problem, but it might be worth trying. 
> This would be the cleanest solution.
> - Just for the sake of completeness: you can rename one of the 
> conflicting column names. I'm not really proposing this.
>     Thomas
> On Fri, 22 Sep 2006, Thomas Fischer wrote:
>> I am rather sure this is a known problem, see
>> http://issues.apache.org/jira/browse/TORQUE-10
>> Are you sure you copied the query correctly ? The problem used to be 
>> the "select A.*" or "select B.*" (Cannot remember which one, maybe 
>> both are
>> needed) in the query which used to run on two fieldnames like 
>> mytablealias1.FIELDNAME and mytablealias2.FIELDNAME. This resulted in 
>> to equal fieldnames, as the error says.
>>    Thomas
>> On Thu, 21 Sep 2006, Tobias Hilka wrote:
>>> Hi everybody,
>>> We are facing the following problem:
>>> We would like to execute a limited query over a join of two (or 
>>> more) database tables in oracle.
>>> We build our Criteria like this (more or less):
>>> crit.setOffset(0);
>>> crit.setLimit(50);
>>> crit.addJoin(mytablealias1.FIELDNAME, mytablealias2.FIELDNAME, 
>>> Critiera.INNER_JOIN); crit.addSelectColumn(mytablealias1.FIELDNAME);
>>> crit.addAlias(mytablealias1, TABLE1); crit.addAlias(mytablealias2, 
>>> TABLE2);
>>> The SQL-query string that is generated looks like this:
>>>      FROM T_TABLE1 mytablealias1 INNER JOIN TABLE mytablealias2 ON 
>>> mytablealias1.FIELDNAME=mytablealias1.FIELDNAME
>>>    ) A
>>> When executing this query, torque (and the tool we are using TOAD)) 
>>> tells us the following error code:
>>> ORA-00918 column ambiguously defined.
>>> It seems that Oracle takes all fields from the innermost select and 
>>> cuts off the alias names, resulting in two columns with the name 
>>> "FIELDNAME". I understand the problem oracle is having at this 
>>> point, but is there any solution to this problem?
>>> Thanks in advance.
>>> Best regards,
>>> Tobias Hilka
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
>> For additional commands, e-mail: torque-dev-help@db.apache.org
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-dev-help@db.apache.org

View raw message