wood burning stoves
The moose likes JDBC and Relational Databases and the fly likes problem using subqueries in jdbc..... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "problem using subqueries in jdbc....." Watch "problem using subqueries in jdbc....." New topic

problem using subqueries in jdbc.....

Syed Zackriya Hussainy
Ranch Hand

Joined: Jun 24, 2002
Posts: 30
I am using statement interface to insert values into a oracle database. The insert query contains 3 simple subqueries, but when i try to execute the program hangs. I am testing using tomcat3.2 and oracle8i.
Is it that JDBC doesn't work efficiently with subqueries or what, because when i executed the same query in oracle editor it executes perfectly and that too very fast, but why is it that i am not able to execute the same query using JDBC. I am sure its the problem of using sub queries. but what is that problem, i don't know.......
If somebody could help me, i will be greatly obliged. its sort of urgent
eagerly waiting for reply
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

sounds like you have a threading or database locking issue. JDBC is an interface, so "Is it that JDBC doesn't work efficiently with subqueries or what" does not really apply in a general sense. JDBC driver implementations usually don't interpret the sql logic, but only relay it to the database. So, if your database supports it, then you should be OK. The general rule is that if you can do it from your sql editor, then you can do it from JDBC. Usually if there is a problem, an SQLException is thrown. Which leads me to believe that this is a threading or locking issue. Your insert statement may be waiting for someone to release the record you are trying to update.
Syed Zackriya Hussainy
Ranch Hand

Joined: Jun 24, 2002
Posts: 30
thanx jamie,
I think you r right on this,because after an hour it was working perfectly. Actually lot of ppl are using the same table, maybe a locking problem here.
Thanx for enlightening me about the problem,so that in future i don't have to get bogged up.
Ken Robinson
Ranch Hand

Joined: Jul 25, 2002
Posts: 52
I have had the same problem and believe it or not, it had to do with the fact I tested the query in Oracle's SQL+ before testing it in my code.
It could be possible that by default SQL+ does not have AutoCommit enabled. If not, the query you ran will still hold a lock on that record until you execute an explicit 'COMMIT' through SQL+. Until you do this, it is very possible that your java code is waiting for the Oracle Server to either process or reject the query, depending on any timeouts that may be set.
Hope this helps.
Syed Zackriya Hussainy
Ranch Hand

Joined: Jun 24, 2002
Posts: 30
hi ken,
I tried to test what you had told, but it comes otherwise, means see first i disabled the autocommit(set autocommit off) and then tried to run the query using JDBC, but it working fine, this time there is no problem, i have NOT given any commit statement as well in JDBC. But its fine,
So whats happening there could u put some light on it
see u pal
Have you checked out Aspose?
subject: problem using subqueries in jdbc.....
It's not a secret anymore!