Why does this CMR code not work?

[robertparis]

OK, I have a BookBean that contains a collection of ChapterBeans. Now when
you call BookHome.create(...), in ejbPostCreate, it does the following:

public void ejbPostCreate(String title, Collection chapters)
{
 Collection newChapters = new ArrayList();
 try
 {
 ChapterHomeLocal homeLocal = ( ChapterHomeLocal ) new
InitialContext().lookup( "ChapterHomeLocal" );

 //THESE ARE REMOTE INTERFACES
 Iterator it = chapters.iterator();
 while ( it.hasNext() )
 {
 ChapterRemote c = ( ChapterRemote ) it.next();
 ChapterLocal cL = homeLocal.findByPrimaryKey( ( Integer )
c.getPrimaryKey() );

 newChapters.add( cL );
 }
 //NOW ADDING TO CURR. COLLECTION
 Collection curr = getChapters();

 //THE ERROR OCCURS HERE
 curr.addAll( newChapters );
 }
 catch (....elided)
}

When I do that, it throws a TransactionRolledbackException (at the
curr.AddAll()) with the message:

"Unexpected token in statement [SELECT TITLE, CHAPTER_NUMBER, BOOK_ID FROM
CHAPTER WHERE (ID=?)]"

HOWEVER, if I do the below code instead, it works!

public void ejbPostCreate(String title, Collection chapters)
{
 Collection newChapters = new ArrayList();
 try
 {
 ChapterHomeLocal homeLocal = ( ChapterHomeLocal ) new
InitialContext().lookup( "ChapterHomeLocal" );
 //CREATE NEW CHAPTER
 ChapterLocal newC = homeLocal.create( "NEW CHAP", 1 );
 newChapters.add( newC );

 //NOW ADDING TO CURR. COLLECTION
 Collection curr = getChapters();
 curr.addAll( newChapters );
 }
 catch (....elided)
}

So this tells me it's not a relationship-code/descriptor problem. Is it a
transaction issue? However, when I change the methods' transaction
attributes to Never (just to check), it then throws an
IllegalStateException (as expected), so I don't think it's a transaction-
scope thing. What am I not understanding here?

Thank you for ANY help you can give!

[aloubyansky]

CMR works only with local interfaces (read the spec). I don't know why the exception is about sql though.

[robertparis]

Yeah, but look at my code again. I DO use Local interfaces only. I take the remote object, get the primary key and then use that to get a Local object via localFactory.findByPrimaryKey. Then I associate the local objects.

So I'm still stumped.

[aloubyansky]

Ahh.. right. Sorry. So find out why the driver doesn't like the sql.

[robertparis]

It says it doesn't like it because "null" is being passed in for ID (I believe), but I don't know why that is getting passed

[robertparis]

I figured it out!! Apparently, the word "POSITION" was causing the error. (I had been storing the position of the chapter in the book collection) It was being interpreted as a keyword. I didn't know Position was a SQL keyword. Where is that from?

Anyways, when I changed it to chapterPosition, everything worked!