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