Problem with MSSQL and Criteria.addSelectColumn

[Kalyani Kale <ka...@ascindia.com>]

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