You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by KERNEUZET FREDERIC <Fr...@pr-gicm.com> on 2005/03/02 15:07:19 UTC
RE : RE : Problem with setDistinct()
Hi Thomas, and thanks for your answer.
First, in my console, the following statement is succesful :
SELECT
DISTINCT (SALARIE.CD) FROM TURBINE_ROLE, SALARIE, TURBINE_USER_GROUP_ROLE WHERE
TURBINE_ROLE.ROLE_NAME NOT IN ('Correspondant') AND SALARIE.
USER_ID=TURBINE_USER_GROUP_ROLE.USER_ID AND TURBINE_ROLE.
ROLE_ID=TURBINE_USER_GROUP_ROLE.ROLE_ID
Of course, the same statement without any column specified is not recognized by the SQL parser.
So, for me, the real problem is why no column name appear ?
I succesfully used almost the same thing in another place :
===========================================================
criteria.addJoin(EntitePeer.GROUP_ID, TurbineUserGroupRolePeer.GROUP_ID);
criteria.add(TurbineUserGroupRolePeer.USER_ID, ((OpriskUser)user).getId());
criteria.addAscendingOrderByColumn(EntitePeer.CD);
criteria.addJoin(TurbineRolePeer.ROLE_ID, TurbineUserGroupRolePeer.ROLE_ID);
if(data.getParameters().getString("incident","").equals("true"))
{
log.info("Login de type incident");
data.getParameters().setString("ORIGINE","GTO");
criteria.add(TurbineRolePeer.ROLE_NAME,roleIncident);
}
else
{
log.info("Login de type classique");
criteria.addNotIn(TurbineRolePeer.ROLE_NAME,roleExcluList);
}
criteria.setDistinct();
===========================
Here in the SQL request generated, the Distinct() method is applied to a particular column ...
I don't manage to find the difference between my two source code.
I'll try adding each column manually to see if it's better.
Frederic.
-----Message d'origine-----
De : Thomas Fischer [mailto:fischer@seitenbau.net]
Envoyé : mercredi 2 mars 2005 14:44
À : Apache Torque Users List
Objet : RE : Problem with setDistinct()
Hi Frederic,
It is not the primary keys of the table, it is all columns of the table
that are compared by a DISTINCT. So if any column is a text, ntext or
image, you have a problem.
Just to make sure it is not a JDBC problem: did you try to execute the SQL
statement in a console ? If yes, did it give the same message ?
To find the reason why no column names appear (which could be the reason
for the problem if the Salarie table does not contain text, ntext or
image), which Torque version are you using ? You could also try to add
select fields "by hand" and see what happens.
Thomas
"KERNEUZET FREDERIC" <Fr...@pr-gicm.com> schrieb am 02.03.2005
12:57:06:
> Primary keys of concerned tables are not text, ntext or image as the
> Exception message says...
>
> It's just INTEGERS !
>
> I've avoided the problem with a really bad solution in order to give
> me more time to find a good one : deleting doubled item in my result list
...
>
>
> Frederic.
>
>
> -----Message d'origine-----
> De : Thomas Fischer [mailto:fischer@seitenbau.net]
> Envoyé : mercredi 2 mars 2005 11:50
> À : Apache Torque Users List
> Objet : RE: Problem with setDistinct()
>
>
>
>
>
> Hi,
>
> its just as it says: some datasets do not support DISTINCT with some
> datatypes. No problem of Torque, but of the database.
> I got no clean solution, only if your content is short enough then you
can
> use VARCHAR instead of whatever datatype you use and it should work then.
>
> Thomas
>
> "KERNEUZET FREDERIC" <Fr...@pr-gicm.com> schrieb am
01.03.2005
> 14:17:48:
>
> > Hi everyone,
> >
> > I've got a problem with something quite "easy", and besides, already
> > used in another place in my application ...
> >
> > The source code is :
> > ====================
> > ArrayList listRole = new ArrayList();
> > listRole.add("Correspondant");
> >
> > criteria.addJoin( SalariePeer.USER_ID, TurbineUserGroupRolePeer.USER_ID
> );
> > criteria.addJoin(TurbineRolePeer.ROLE_ID, TurbineUserGroupRolePeer.
> > ROLE_ID);
> > criteria.addNotIn(TurbineRolePeer.ROLE_NAME,listRole);
> >
> > criteria.setDistinct();
> >
> > List lstSalarie = SalariePeer.doSelect(criteria);
> > ====================
> >
> > It gives me an sql request without any column selected :
> > SELECT DISTINCT FROM TURBINE_ROLE, SALARIE, TURBINE_USER_GROUP_ROLE
> > WHERE TURBINE_ROLE.ROLE_NAME NOT IN ('Correspondant') AND SALARIE.
> > USER_ID=TURBINE_USER_GROUP_ROLE.USER_ID AND TURBINE_ROLE.
> > ROLE_ID=TURBINE_USER_GROUP_ROLE.ROLE_ID
> >
> > And an java.sql.Exception in thrown :
> > java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]
> > [SQLServer]Data type like text, ntext or image cannot be selected
> asDISTINCT.
> >
> > (error message translated from french)
> >
> > As someone got an idea ?
> >
> >
> > Thanks !
> > ==================================
> > Frédéric Kerneuzet
> > ==================================
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE : Problem with setDistinct()
Posted by Thomas Fischer <fi...@seitenbau.net>.
Hi Frederic,
I am 99% sure that the distinct in the statement is not for a single column
in a table, but for the whole bunch of columns where you select from.
Therefore your criteria might not do what you want.
You can also compare the error messages in the console to the torque error
message. If they are different, chances are that the statement without
select columns is not the statement that is actually executed by Torque. I
supoose you got the statement from Torque's debug.log, in which case I
would suspect that the debug.log statement is incorrect.
Be careful that, if you specify the columns by hand, strange results might
appear if you do not specify all columns of the Salaries Table (no idea how
the other Fields of the salaries object are filled)
Thomas
"KERNEUZET FREDERIC" <Fr...@pr-gicm.com> schrieb am 02.03.2005
15:07:19:
> Hi Thomas, and thanks for your answer.
>
> First, in my console, the following statement is succesful :
> SELECT
> DISTINCT (SALARIE.CD) FROM TURBINE_ROLE, SALARIE,
> TURBINE_USER_GROUP_ROLE WHERE
> TURBINE_ROLE.ROLE_NAME NOT IN ('Correspondant') AND SALARIE.
> USER_ID=TURBINE_USER_GROUP_ROLE.USER_ID AND TURBINE_ROLE.
> ROLE_ID=TURBINE_USER_GROUP_ROLE.ROLE_ID
>
> Of course, the same statement without any column specified is not
> recognized by the SQL parser.
>
> So, for me, the real problem is why no column name appear ?
>
> I succesfully used almost the same thing in another place :
> ===========================================================
> criteria.addJoin(EntitePeer.GROUP_ID, TurbineUserGroupRolePeer.GROUP_ID);
> criteria.add(TurbineUserGroupRolePeer.USER_ID,
((OpriskUser)user).getId());
> criteria.addAscendingOrderByColumn(EntitePeer.CD);
>
> criteria.addJoin(TurbineRolePeer.ROLE_ID,
TurbineUserGroupRolePeer.ROLE_ID);
>
> if(data.getParameters().getString("incident","").equals("true"))
> {
> log.info("Login de type incident");
> data.getParameters().setString("ORIGINE","GTO");
> criteria.add(TurbineRolePeer.ROLE_NAME,roleIncident);
> }
> else
> {
> log.info("Login de type classique");
> criteria.addNotIn(TurbineRolePeer.ROLE_NAME,roleExcluList);
> }
>
> criteria.setDistinct();
>
> ===========================
> Here in the SQL request generated, the Distinct() method is applied
> to a particular column ...
>
> I don't manage to find the difference between my two source code.
>
>
> I'll try adding each column manually to see if it's better.
>
>
> Frederic.
>
>
> -----Message d'origine-----
> De : Thomas Fischer [mailto:fischer@seitenbau.net]
> Envoyé : mercredi 2 mars 2005 14:44
> À : Apache Torque Users List
> Objet : RE : Problem with setDistinct()
>
>
>
>
>
> Hi Frederic,
>
> It is not the primary keys of the table, it is all columns of the table
> that are compared by a DISTINCT. So if any column is a text, ntext or
> image, you have a problem.
>
> Just to make sure it is not a JDBC problem: did you try to execute the
SQL
> statement in a console ? If yes, did it give the same message ?
>
> To find the reason why no column names appear (which could be the reason
> for the problem if the Salarie table does not contain text, ntext or
> image), which Torque version are you using ? You could also try to add
> select fields "by hand" and see what happens.
>
> Thomas
>
>
> "KERNEUZET FREDERIC" <Fr...@pr-gicm.com> schrieb am
02.03.2005
> 12:57:06:
>
> > Primary keys of concerned tables are not text, ntext or image as the
> > Exception message says...
> >
> > It's just INTEGERS !
> >
> > I've avoided the problem with a really bad solution in order to give
> > me more time to find a good one : deleting doubled item in my result
list
> ...
> >
> >
> > Frederic.
> >
> >
> > -----Message d'origine-----
> > De : Thomas Fischer [mailto:fischer@seitenbau.net]
> > Envoyé : mercredi 2 mars 2005 11:50
> > À : Apache Torque Users List
> > Objet : RE: Problem with setDistinct()
> >
> >
> >
> >
> >
> > Hi,
> >
> > its just as it says: some datasets do not support DISTINCT with some
> > datatypes. No problem of Torque, but of the database.
> > I got no clean solution, only if your content is short enough then you
> can
> > use VARCHAR instead of whatever datatype you use and it should work
then.
> >
> > Thomas
> >
> > "KERNEUZET FREDERIC" <Fr...@pr-gicm.com> schrieb am
> 01.03.2005
> > 14:17:48:
> >
> > > Hi everyone,
> > >
> > > I've got a problem with something quite "easy", and besides, already
> > > used in another place in my application ...
> > >
> > > The source code is :
> > > ====================
> > > ArrayList listRole = new ArrayList();
> > > listRole.add("Correspondant");
> > >
> > > criteria.addJoin( SalariePeer.USER_ID,
TurbineUserGroupRolePeer.USER_ID
> > );
> > > criteria.addJoin(TurbineRolePeer.ROLE_ID, TurbineUserGroupRolePeer.
> > > ROLE_ID);
> > > criteria.addNotIn(TurbineRolePeer.ROLE_NAME,listRole);
> > >
> > > criteria.setDistinct();
> > >
> > > List lstSalarie = SalariePeer.doSelect(criteria);
> > > ====================
> > >
> > > It gives me an sql request without any column selected :
> > > SELECT DISTINCT FROM TURBINE_ROLE, SALARIE, TURBINE_USER_GROUP_ROLE
> > > WHERE TURBINE_ROLE.ROLE_NAME NOT IN ('Correspondant') AND SALARIE.
> > > USER_ID=TURBINE_USER_GROUP_ROLE.USER_ID AND TURBINE_ROLE.
> > > ROLE_ID=TURBINE_USER_GROUP_ROLE.ROLE_ID
> > >
> > > And an java.sql.Exception in thrown :
> > > java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]
> > > [SQLServer]Data type like text, ntext or image cannot be selected
> > asDISTINCT.
> > >
> > > (error message translated from french)
> > >
> > > As someone got an idea ?
> > >
> > >
> > > Thanks !
> > > ==================================
> > > Frédéric Kerneuzet
> > > ==================================
> > >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: torque-user-help@db.apache.org
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: torque-user-help@db.apache.org
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org