2 Replies Latest reply on Jul 15, 2002 6:52 PM by quang

    Urgen,  Please help: Jboss generates wrong SQL query

    quang Newbie


      I'm using Jboss server version 3.0.1RC1. It seems to me that this version does not generate correct SQL query.

      I have the following ejb-query in my ejb-jar.xml
      <ejb-ql><![CDATA[select object(P) from Permission p, IN (p.role.userRole) AS r where r.user.userName=?1]]></ejb-ql>

      the relationships between permission, role, userRole and users are:

      - one user has many userRole, and one userRole belongs to one user
      - one role has many userRole and one userRole belongs to one role
      - one role has many permissions, and one permission belongs to one role

      The generated SQL is:

      DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.biz/bsite/direct/entity/PermissionEJB.findAllUserPermission] SQL: SELECT t0_p.PermissionId FROM permission t0_p, userrole t2_r, users t1_r_user WHERE (t1_r_user.UserName = ?) AND (t2_r.userId=t1_r_user.UserId AND t4_p_role.RoleId=t2_r.roleId AND t0_p.roleId=t4_p_role.RoleId)

      As can be seen in the query above, the relation t4_p_role is not specified in the FROM clause causing SQL exception when the method is executed.

      Can some one please help me with this problem?