[prev in list] [next in list] [prev in thread] [next in thread] 

List:       turbine-torque-user
Subject:    RE: Problem with MSSQL and Criteria.addSelectColumn
From:       "Greg Monroe" <Greg.Monroe () DukeCE ! com>
Date:       2006-03-17 16:11:29
Message-ID: 8F5843B903F59D4C8C6806BB49A391190629F4 () dukece-mail3 ! dukece ! com
[Download RAW message or body]

Playing around with the supplied queries with Query Analyzer
on my SQL server, I got the same problem as you with the 
non-aliased query.  However, I also got problems running the 
aliased query.

In looking for a valid query syntax, I found some references 
to the fact that in order to be compliant with the SQL-92 
standard, MS SQL Server 2000 is more strict on implied joins.
E.g.

  WHERE CSProfileAuthorization.CSAUTHORIZATIONREGISTRYID=
  CSAuthorizationRegistry.ID 

I suspect that this is the reason that MS SQL doesn't work
and MySQL does.  IMHO, you'd have the same issue if you went
to Oracle or any DB that applies the standards more strictly.
I know I've hit problems like that in moving MySQL development 
code to MS SQL (especially foreign key contraint enforcement).

You might try restructuring the query to use two explicit 
joins to create a more DB vendor independent format.


> -----Original Message-----
> From: Kalyani Kale [mailto:kalyani@ascindia.com] 
> Sent: Thursday, March 16, 2006 12:59 AM
> To: Apache Torque Users List
> Subject: RE: Problem with MSSQL and Criteria.addSelectColumn
> 
> 
> 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=CSAuthoToGrou
> p.CSAUTHOR
> IZATIONREGISTRYID 
> 
> WHERE 
> CSProfileAuthorization.CSAUTHORIZATIONREGISTRYID=CSAuthorizati
> onRegistry
> .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
> 
> 
> 
> 
> 

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


[prev in list] [next in list] [prev in thread] [next in thread]