Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: neutral (nike.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; charset=UTF-8; format=flowed
Date: Tue, 06 May 2008 10:17:40 +0200
From: Daniel Doppmeier <ddoppmei@cebitec.uni-bielefeld.de>
Subject: many-to-many mapping resulting in incomplete SQL queries
To: user@cayenne.apache.org
Message-id: <48201424.6060504@cebitec.uni-bielefeld.de>
User-Agent: Thunderbird 2.0.0.12 (X11/20080227)

Hello everyone!

I am new to cayenne and experiencing some problems, when modelling a 
many-to-many relationship. After I could not solve the problem for an 
application I am writing at the moment, I set up the following example 
application, which is producing the same problems:

I modelled a small bookstore application, with two tables "Book" and 
"Person". After that I created a join table "Person_is_author_of_book", 
to map from "Person" entries to "Book" entries. On the object side of 
the application, this would mean, that every book may have several 
authors, whereas one author may have written more than one book.

Inserting and fetching data from the DB seems to work just fine, but 
when it comes to deletion there occures my problem. This is what happens 
in my code:

       DataContext context = DataContext.createDataContext();
      
       Book book1 = (Book) context.newObject(Book.class);
       book1.setTitle("The secret life of Donald D");

       Person author1 = (Person) context.newObject(Person.class);
       author1.setName("Huey");
      
       Person author2 = (Person) context.newObject(Person.class);
       author2.setName("Dewey");

       context.commitChanges();

       author1.removeFromBooks(book1);
       context.commitChanges();

Everything is fine until the second commitChanges(), when I try to 
delete book1 from author1's list.
This is what the logger says about it:

INFO  QueryLogger: --- will run 1 query.
INFO  QueryLogger: --- transaction started.
INFO  QueryLogger: DELETE FROM Person_is_author_of_book WHERE
INFO  QueryLogger: *** error.

As you can see, there is missing some SQL syntax after the WHERE clause. 
Does anybody have an idea, what may cause this malformed SQL?

I am using the latest stable cayenne version 2.0.4, MySQL 5.0.51a-3ubuntu5 and java "1.6.0_06"


Thanks a lot for any help!