2 Replies Latest reply on Jul 18, 2002 11:31 PM by Lamar Channell

    Incorrect EJB-QL to SQL translation

    Lamar Channell Newbie

      Hi,

      I have serveral tables. One table called "project" keeps track of unique projects. One table called "project_tie" ties an individual project to other data stored in other tables. Multiple tables exist to store project data, say project_data1, project_data2, etc.

      project
      Pk ProjectName
      1 Project 1
      2 Project 4

      project_tie
      Pk ProjectPk TieToTable TieToPk
      3 1 project_data1 7
      4 2 project_data1 9

      project_data1
      Pk Value
      7 project data for project 1
      9 project data for project 2

      I'm using JBoss 3.0 with CMP. I have EJB's for project, project_tie, project_data1. However, I do not have any relations defined yet because there are approx. 50 project_dataX tables. I would expect to be able to do a standard type query with EJB-QL.

      My EJB-QL for getting infomation from the project_data1 table for a given project looks like:

      select object(p) from project_data1 p, project_tie t where
      t.TieToTable = "project_data1" and
      t.TieToPk = p.Pk and
      t.ProjectPk = ?1

      The generated SQL is:

      select t0_p.Pk from project_data1 t0_p, project_tie t1_t where
      (t1_t.TieToTable = "project_data1") and
      (t1_t.TieToPk = t0_p.TieToPk) and
      (t1_t.ProjectPk = ?)

      An 'Invalid column name' exception is thrown when the query gets executed. The t.TieToPk = p.Pk in the where clause was translated incorrectly to t1_t.TieToPk = t0_p.TieToPk but the TieToPk column does not exist in the project_data1 table.

      Any ideas?

      Thanks,
      Lamar