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:[EMAIL PROTECTED]
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:[EMAIL PROTECTED]
> Sent: Wednesday, March 15, 2006 10:11 AM
> To: [email protected]
> 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
