db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <fisc...@seitenbau.net>
Subject RE : Problem with setDistinct()
Date Wed, 02 Mar 2005 14:52:32 GMT




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" <Frederic.Kerneuzet@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" <Frederic.Kerneuzet@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" <Frederic.Kerneuzet@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


Mime
View raw message