db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Greg Monroe" <Greg.Mon...@DukeCE.com>
Subject RE: Problem with MSSQL and Criteria.addSelectColumn
Date Wed, 15 Mar 2006 16:47:42 GMT
Hmm, from your code sample it looks like you are trying
to do a query like:

select A.id, A.groupId, B.name from A, B where A.id = 1000

That seems like a very dangerous implied join between the
two tables.  A query like this would probably never be 
DB implimentation independent since I don't think the 
standards define what servers should do in this case.  You 
should probably have a specific join defined between the
tables to make it work on all servers.

That said, you might want to dump the specific query being
generated (call Criteria's toString() method after the query 
is executed) to see what the differences are between the 
criteria using table aliases and the original one.  This might
give some insight into what MS SQL is complaining about.

If the CSAuthoToGroupPeer.CSAUTHOGROUPREGISTRYID is a foreign
key field to CSAuthorizationRegistryPeer.ID, then you could
also use the much safer CSAuthoToGroupPeer.doSelect(criteria)
and then use the CSAuthoToGroupPeer.getCSAuthorizationRegistry()
method to get the name information.

Direct calls to basePeer methods can be tricky sometimes..

> -----Original Message-----
> From: Kalyani Kale [mailto:kalyani@ascindia.com] 
> Sent: Wednesday, March 15, 2006 10:11 AM
> To: torque-user@db.apache.org
> Subject: Problem with MSSQL and Criteria.addSelectColumn
> 
> 
> Hi,
> 
> We are using torque in our application. We were using MySQL 
> 4.1 as the database earlier. Recently we have shifted to SQL 
> Server (Version 8.0).
> 
> After the migration, we have encountered problems in the 
> torque code we are using. These problems are in the same type of code.
> 
> The problem is, wherever we have used 
> Criteria.addSelectColumn method without using table aliases, 
> the SQL Server threw an exception though it works fine on MySQL.
> 
> For e.x. following piece of code works fine for MySQL but not 
> for SQL Server.
> 
>  
> 
> criteria.clear();
> 
>  
> 
> criteria.add(CSAuthoToGroupPeer.ID,1000);
> 
>  
> 
> criteria.addSelectColumn(CSAuthoToGroupPeer.CSAUTHOGROUPREGISTRYID);
> 
> criteria.addSelectColumn(CSAuthorizationRegistryPeer.NAME);
> 
>  
> 
> List authosResultSet = BasePeer.doSelect(criteria, connection);
> 
>  
> 
> To make it to work on SQL Server, we need to add aliases to 
> all the tables involved. I have such type of a code at 
> multiple places in my application and I am looking for a 
> generic solution instead of changing the code everywhere.
> 
> Did anyone encounter this problem before? If yes, what was 
> the solution?
> 
> The solution that I have in mind is to
> 
> 1)       Clone the BasePeer.doSelect method. 
> 
> 2)       In that method, modify the criteria to replace the 
> table names
> with aliases
> 
> a.       While modifying the criteria, the select columns, order by,
> group by, having and where clause of the criteria, need to be 
> taken care of.
> 
> 3)       Call the BasePeer.doSelect method with the modified 
> criteria as
> a parameter
> 
>  
> 
> Does that look ok?
> 
>  
> 
> TIA,
> 
> Kalyani
> 
> 

Duke CE Privacy Statement
Please be advised that this e-mail and any files transmitted with it are confidential communication
or may otherwise be privileged or confidential and are intended solely for the individual
or entity to whom they are addressed.  If you are not the intended recipient you may not rely
on the contents of this email or any attachments, and we ask that you  please not read, copy
or retransmit this communication, but reply to the sender and destroy the email, its contents,
and all copies thereof immediately.  Any unauthorized dissemination, distribution or copying
of this communication is strictly prohibited.



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message