aspose file tools*
The moose likes JDBC and the fly likes  Java/Oracle JDBC transaction management and committing sessions Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark " Java/Oracle JDBC transaction management and committing sessions" Watch " Java/Oracle JDBC transaction management and committing sessions" New topic
Author

Java/Oracle JDBC transaction management and committing sessions

O. Ziggy
Ranch Hand

Joined: Oct 02, 2005
Posts: 430



Now every time i run the above i get the following error



I tried to debug the program and found that the if statement checking if rs.next() is true is never returning true so that bind variable is never set. The exception occurs at addChildStmt.executeUpdate(insertChild);

I dont want to issue a commit after i have inserted the parent record. My understanding is that i dont have to commit if i am on the same session. Will the second insert statement shown above not be in the same session as the first one? Why exactly is the rs.next() not returning any value even though i had just inserted the record?

Thanks
O. Ziggy
Ranch Hand

Joined: Oct 02, 2005
Posts: 430

I think the problem is with this line

addChildStmt.executeUpdate(insertChild);

im suprised the compiler did not complain about the above line. The PreparedStatement API is showing that is not valid.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Not sure I follow - that method call is just as defined by the API. Why do you think it should not compile?

The error you see is because not all defined parameters in your query are bound (i.e. you've not supplied values for some of them).

This call:

What happens if the cursor cannot be moved on in the ResultSet? What sets the first parameter?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
O. Ziggy
Ranch Hand

Joined: Oct 02, 2005
Posts: 430

What i meant was that according to the API, this line is supposed to be
I am not sure why the compiler did complain of the parameter because the PreparedStatement has no method executeUpdate that takes a String as a parameter. See http://download.oracle.com/javase/1.4.2/docs/api/java/sql/PreparedStatement.html

With regards to the if statement it should always return a value.

Thanks
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

I think you are maybe not quite reading the JavaDocs correctly, PreparedStatement inherits this method from Statement (see this).

Are you sure the if statement always returns true? What happens when you debug over that line?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

You're right that you need to use addChildStmt.executeUpdate(), that was the cause of the exception.

However, your code can be made much simpler and more performant. Instead of useand then erase lines 37 to 52 inclusive in your code. You'll then need to adjust the bind index on line 53 from 2 to 1, of course.

Explanation: the currVal value will return the value generated by nextVal in the previous statement. Then you don't need to reread the value generated for parent and don't need to set it in the child. This is how Oracle's sequences are supposed to be used. See also this documentation

Note that it must all happen in the same transaction, so autocommit must be set to false, but you're already doing it.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Java/Oracle JDBC transaction management and committing sessions