Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

statement.executeUpdate() hangs

 
Rajesh MadhanGopal
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
i am using a simple piece of code to update a single row in Oracle DB, the query updates just a single row but still the execution hangs on stmt.executeUpdate().

it does not throw any exception, but the control just hangs there..
I am lost here as the same piece of code worked fine earlier.

 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sounds like something may be blocking your update. Have a look at DBA_BLOCKERS, see is that is it.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4020
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you really need to make a compile-time reference to an Oracle JDBC class? Usually, something like "Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();" is more common since it reduces compile-time dependencies on drivers that won't be determined until runtime. Also, use PreparedStatements to set your variables.
 
Rajesh MadhanGopal
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the replies..

@Scott: I tried both the above mentioned steps before posting the question here and they were not of much help. I finally resolved the issue by indexing the 2 columns in my Where clause and now the update works like a charm.

I always thought that indexing would help in select queries and in complex joins and did not think that indexing would be needed for such a simple query. There is no question of too much load as there are less than 100 rows in the table and the update query will never update more than 1 row at a time.

Any thoughts are welcome !!
 
Vijitha Kumara
Bartender
Posts: 3914
9
Chrome Fedora Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rajesh MadhanGopal wrote:I always thought that indexing would help in select queries and in complex joins and did not think that indexing would be needed for such a simple query. There is no question of too much load as there are less than 100 rows in the table and the update query will never update more than 1 row at a time.

I don't think indexing made the difference there, since you have only around 100 records in it. Indexing makes the searching for records faster..
 
Jeunne Seunne
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rajesh MadhanGopal wrote:Thanks for the replies..

@Scott: I tried both the above mentioned steps before posting the question here and they were not of much help. I finally resolved the issue by indexing the 2 columns in my Where clause and now the update works like a charm.

I always thought that indexing would help in select queries and in complex joins and did not think that indexing would be needed for such a simple query. There is no question of too much load as there are less than 100 rows in the table and the update query will never update more than 1 row at a time.

Any thoughts are welcome !!



Hi,

I am having the same problem. Can you post your solution? How did you resolved it? I've been working on that problem for weeks now trying to make it work but it just hangs up. Thanks for your help in adv.

 
Fatih Keles
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There may be an other database session that has locked the row or table. You may check it with the below query. Index solution does not make any sense since the number of records in the table is very small in the former problem. Can you update the row with any sql editor?



Regards,

Fatih.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rajesh MadhanGopal wrote:
I always thought that indexing would help in select queries and in complex joins and did not think that indexing would be needed for such a simple query. There is no question of too much load as there are less than 100 rows in the table and the update query will never update more than 1 row at a time.

Any thoughts are welcome !!


Indices will potentially slow down updates, not speed them up. Are you sure this change was not just coincidental?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34837
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:Indices will potentially slow down updates, not speed them up.

Depends on the query of course. If you have a million rows in the table and need to find the one to update, an index could help.

Paul Sturrock wrote: Are you sure this change was not just coincidental?

That said, I think coincidence is more likely as 100 rows is too small for an index to matter much.
 
Rajesh MadhanGopal
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I want to beleive this is just a coincidence as it beats any logic. I also want to mention that the update worked fine all through from an SQL editor while it was failing from the code, this also means that there is no blocking on the table.

I am in a situation where the issue is resolved and not completely sure how it was resolved
 
Scott Selikoff
author
Saloon Keeper
Posts: 4020
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's also possible your database was corrupt. It does happen, more commonly with MySQL in my experience. As Jeanne said, an index can speed up UPDATE statements if you have a large enough record set but 100 isn't very big.
 
Jeunne Seunne
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So if the resolution for this problem was just coincidental, it looks like i need to find my own solution then. So hopeless
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic