db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kalyani Kale" <kaly...@ascindia.com>
Subject RE: Problem with MSSQL and Criteria.addSelectColumn
Date Thu, 16 Mar 2006 05:58:47 GMT
See my comments in blue

 

> That seems like a very dangerous implied join between the two tables.


 

I pasted just a part of the query to give an idea of what's happening.
The actual query does contain proper joins.

 

> That said, you might want to dump the specific query

Yes, I did that. I pasted the output in SQL Server's query analyzer and
it didn't work which is why I modified it.

 

1) This is the original version of the query

 

SELECT 

      CSAuthoToGroup.CSAUTHOGROUPREGISTRYID, 

      CSAuthorizationRegistry.NAME, 

      CSAuthorizationRegistry.TYPE, 

      CSAuthorizationRegistry.DESCRIPTION, 

      CSAuthorizationRegistry.ID, 

      CSAuthorizationRegistry.CSCOMMONSERVICEREGISTRYID 

FROM 

CSProfileAuthorization, CSAuthorizationRegistry 

LEFT JOIN CSAuthoToGroup ON
CSProfileAuthorization.CSAUTHORIZATIONREGISTRYID=CSAuthoToGroup.CSAUTHOR
IZATIONREGISTRYID 

WHERE
CSProfileAuthorization.CSAUTHORIZATIONREGISTRYID=CSAuthorizationRegistry
.ID 

AND CSProfileAuthorization.CSPROFILEREGISTRYID=5000;

 

 

2) The query was later modified to make it work on SQL Server

 

SELECT 

      ag.CSAUTHOGROUPREGISTRYID, 

      ar.NAME, 

      ar.TYPE, 

      ar.DESCRIPTION, 

      ar.ID, 

      ar.CSCOMMONSERVICEREGISTRYID 

FROM 

CSAuthorizationRegistry ar, CSProfileAuthorization pa

LEFT JOIN CSAuthoToGroup ag ON
pa.CSAUTHORIZATIONREGISTRYID=ag.CSAUTHORIZATIONREGISTRYID 

WHERE pa.CSAUTHORIZATIONREGISTRYID=ar.ID 

AND pa.CSPROFILEREGISTRYID=5000;

 

3) The error I was getting in SQL Server is:

The column prefix 'CSProfileAuthorization' does not match with a table
name or alias name used in the query.

 

 

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

 

The reason, I'm calling BasePeer's doSelect is, I am using
Criteria.addSelectColumn while creating a query which forces me to call
BasePeer.doSelect directly.

 

Kalyani

 

 

-----Original Message-----
From: Greg Monroe [mailto:Greg.Monroe@DukeCE.com] 
Sent: Wednesday, March 15, 2006 10:18 PM
To: Apache Torque Users List
Subject: RE: Problem with MSSQL and Criteria.addSelectColumn

 

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
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message