aspose file tools*
The moose likes EJB and other Java EE Technologies and the fly likes EJB and SQL two phase commit problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "EJB and SQL two phase commit problem" Watch "EJB and SQL two phase commit problem" New topic
Author

EJB and SQL two phase commit problem

Justin Chu
Ranch Hand

Joined: Apr 19, 2002
Posts: 209
    
    1
I'm programming a transaction which does the following:
1. Create a new entity bean instance for a course, say "CompSci101"
2. JDBC insert 1000 students who have enrolled for the class (sql inserts for performance sake)

Note that step 2 requires foreign key created in step 1 because students are inserted for the class id.

For instance:
<transaction start>

course = courseHome.create("CompSci101");

con = getConnection();
con.setAutoCommit(false); // necessary?
Long courseId = course.getPrimaryKey();
insertStudent(courseId, "joe");
insertStudent(courseId, "John");
con.commit();
<transaction end>

However, I get JDBC error when inserting student.
[BEA][Oracle JDBC Driver][Oracle]ORA-02291: integrity constraint (COURSEDB.STUDENTS_FK1) violated - parent key not found

It seems that the parent key, primary key for "compsci101", is not realized in the database. How do I go about doing this?
[ October 19, 2006: Message edited by: Chu Tan ]
Ådne Brunborg
Ranch Hand

Joined: Aug 05, 2005
Posts: 208
Have you tried something like...

<transaction start>
course = courseHome.create("CompSci101");
<transaction end>

<transaction start>
con = getConnection();
con.setAutoCommit(false); // necessary?
Long courseId = course.getPrimaryKey();
insertStudent(courseId, "joe");
insertStudent(courseId, "John");
con.commit();
<transaction end>

To avoid messing too much around with starting and stopping transactions manually, which you really wish to avoid, try something like the following (in a SessionBean):



and have the tx setting for createCourse be RequiresNew. In theory you could set the tx setting for the CourseHome.create() method to RequiresNew, and it would have the same effect, but generally setting EntityBean tx settings to anything other than Mandatory - especially when doing write operations - is a bad idea.

The problem here is that if the SQL insert fails, the created course won't be deleted. You could delete the created course in a catch block under the SQL stuff, but if that fails too, you will have an empty course in your database.

But it is problematic mixing EntityBeans and SQL.
[ October 20, 2006: Message edited by: �dne Brunborg ]

Entia non sunt multiplicanda praeter necessitatem
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: EJB and SQL two phase commit problem