Hi all,
I have a many to many relationship that I am updating. Cayenne is
duplicating primary key entries.
I have a Projects, Employees, and a ProjectsEmployees table in the database.
DB schema is below: the *Id fields are Primary Keys
Employee
employeeId | firstName | lastName
Projects
projectId | name
ProjectsEmployees
projectId | employeeId
I have tuples in the employee and projects table already.
Via code, I was "connecting" tuples in one table to the other by executing
aProject.addMember(anEmployee);
context.commitChanges;
My Cayenne model is using a pass-through relationship via the itermediary
ProjectsEmployees table (since that table contains no additional business
object info, other than being a middleman table for joins). For example, the
Employee object in my Cayenne model has a relationship "projects" that is
backed by the db-relationship path TO_projectmembers.TO_projects
This works fine until some condition crops up that I am unaware is occuring.
The log shows what employees and projects tuples I was trying to "connect".
First I have to find the employees I wish to assign (the SELECT call), and
then I execute aProject.addMember(anEmployee) which results in the INSERT
calls that modify the ProjectsEmployees table.
When the error occurs, I have connected employees Philips and Paules to
project SBIR-80. The next transaction is begun where I am trying to add
employees Paules and Corrigan to project Anvil Phase II. For some reason, on
context.commitChanges for these two changes, Cayenne is attempting to re-add
Philips to project SBIR-80. Why? What's more, is that QueryLogger reports
"will run 4 queries" at that point. But there aren't 4 queries to run. There
are 3. And there shouldn't even be that many. There should be two. What
gives?
<errorlog>
WARN QueryLogger: --- will run 1 query.
WARN QueryLogger: SELECT t0.firstName, t0.lastName, t0.title, t0.managerId,
t0.employeeId FROM employee t0 WHERE (UPPER(t0.lastName) LIKE UPPER(?)) OR
(UPPER(t0.lastName) LIKE UPPER(?)) [params: 'Paules', 'Philips']
WARN QueryLogger: === returned 2 rows. - took 0 ms.
found 2 employees
Adding Philips to project SBIR-80
Adding Paules to project SBIR-80
WARN QueryLogger: --- will run 2 queries.
WARN QueryLogger: INSERT INTO projectmembers (projectId, employeeId) VALUES
(?, ?) [params: 1, 4]
WARN QueryLogger: === updated 1 row.
WARN QueryLogger: INSERT INTO projectmembers (projectId, employeeId) VALUES
(?, ?) [params: 1, 1]
WARN QueryLogger: === updated 1 row.
WARN QueryLogger: +++ transaction committed.
WARN QueryLogger: --- will run 1 query.
WARN QueryLogger: SELECT t0.firstName, t0.lastName, t0.title, t0.managerId,
t0.employeeId FROM employee t0 WHERE (UPPER(t0.lastName) LIKE UPPER(?)) OR
(UPPER(t0.lastName) LIKE UPPER(?)) [params: 'Paules', 'Corrigan']
WARN QueryLogger: === returned 2 rows. - took 10 ms.
found 2 employees
Adding Paules to project Anvil Phase II
Adding Corrigan to project Anvil Phase II
WARN QueryLogger: --- will run 4 queries.
WARN QueryLogger: INSERT INTO projectmembers (projectId, employeeId) VALUES
(?, ?) [params: 2, 1]
WARN QueryLogger: === updated 1 row.
WARN QueryLogger: INSERT INTO projectmembers (projectId, employeeId) VALUES
(?, ?) [params: 2, 5]
WARN QueryLogger: === updated 1 row.
WARN QueryLogger: INSERT INTO projectmembers (projectId, employeeId) VALUES
(?, ?) [params: 1, 4]
WARN QueryLogger: *** error.
java.sql.SQLException: Invalid argument value: Duplicate entry '1-4' for key
1
</errorlog>
Please help.
Dave Paules
Quantum Leap Innovations
www.leapworks.com
This archive was generated by hypermail 2.0.0 : Fri Jan 03 2003 - 17:18:02 EST