3 Replies Latest reply on Nov 18, 2005 8:39 AM by mburdman

    Syntax Error on Generated SQL

    michaellee

      Hi,

      The sql generated from my ejbSelect method is as follows:

      SELECT t0_r.name
      FROM operator t1_o, roleTable t0_r
      WHERE t1_o.name = ?
      AND
      (EXISTS
      (SELECT t3_o_roles_RELATION_TABLE.projectID,
      t3_o_roles_RELATION_TABLE.roleName
      FROM operatorRoles t3_o_roles_RELATION_TABLE
      WHERE t1_o.name=t3_o_roles_RELATION_TABLE.operatorName
      AND t0_r.projectID=t3_o_roles_RELATION_TABLE.projectID AND
      t0_r.name=t3_o_roles_RELATION_TABLE.roleName)
      )
      AND t0_r.name = ?
      AND t0_r.projectID = ?

      The backend database (Sybase ASE 12.5) complain about the ',' in Select clause WITHIN Exists. If only one column is specified or use '*', the statement is okay. (primary key for operatorRoles contain 2 columns and this may be the reason why Jboss generated 2 column in select).

      I am not sure this is the problem on Jboss or Sybase, but is there any mean i can control the SQL generated?

      TIA
      Michael

        • 1. Re: Syntax Error on Generated SQL
          triathlon98

          I would say this looks like a Sybase problem.

          You can not really control how JBoss "generates" SQL, but you can tell it the SQL code to use using "declared-sql". See CMP docs.

          Joachim

          • 2. Re: Syntax Error on Generated SQL
            michaellee

            Thank you for the suggestion. Using <declared-sql>, the problem is fixed.

            Regadrs,
            Michael

            • 3. Re: Syntax Error on Generated SQL
              mburdman

              Gracias!!!! Thanks!!

              with something like this my problem with the NOT EXISTS in Sybase was solved.

              XDoclet

              * @ejb.finder query=""
              * signature="java.util.Collection
              * findCabecerasById_cuenta(java.lang.Integer ointId_cuenta)"
              *
              * @jboss.declared-sql signature="java.util.Collection findCabecerasById_cuenta(java.lang.Integer ointId_cuenta)"
              * description="Fuerzo el query de not exists para que no use campo0, campo1"
              * where= "(H_COMPROBANTE.Id_cuenta = {0} AND
              * NOT EXISTS (SELECT 'comprobantes modificadores'
              * FROM H_MODIFICA comprobantesModificadores
              * WHERE H_COMPROBANTE.Id_comprobante=comprobantesModificadores.Comp_Modificador))"