Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Java/Oracle JDBC transaction management and committing sessions

 
O. Ziggy
Ranch Hand
Posts: 430
Android Debian VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Posts: 430
Android Debian VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
O. Ziggy
Ranch Hand
Posts: 430
Android Debian VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic