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

Compiling SQL statement before execute

 
Leslie Chaim
Ranch Hand
Posts: 336
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Friends,
How can I get the Connection.prepareStatment method to throw a SQLExecption if the SQL syntax is wrong or invalid.
I have an array of String with SQL statements that I prepare in a loop. Then, in a second loop, I get the data from my source and for each pstmts[i] I do an executeUpdate().
How can I detect if I have bad SQL in my first loop when I prepare the stmts?
I am connecting to an Oracle database.
Thanks,
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Leslie,
Since you say you are using an Oracle database, a "trick" that works with an Oracle database is to prepend "explain plan for " to your SQL, and invoke the "executeUpdate()" method (of your "PreparedStatement") -- this will simply parse your SQL statement and throw an "SQLException" if it is incorrect.
Hope this helps.
Good Luck,
Avi.
 
Leslie Chaim
Ranch Hand
Posts: 336
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Avi this is great. Although, I already did a work-around the "hard way" (Check the executeUpdate) I will undo my changes and use this "trick".
One more question, can you please point me to where I can find more about explain plans. I have heard of them, but I want to know more.
Thanks,
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Leslie,
The entire Oracle documentation is available from:
http://tahiti.oracle.com
Other Oracle related Web sites I often visit are:
http://technet.oracle.com
http://asktom.oracle.com
Note that, for the first two URLs, above, you need to register -- but registration is free.
Good Luck,
Avi.
 
Leslie Chaim
Ranch Hand
Posts: 336
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Avi,
I undid my changes as did as you suggested and it works like a charm.
Until... my multi-threaded server runs only one thread
If I run more then one session (thread) I get the following:
java.sql.SQLException: ORA-00054: resource busy and acquire with NOWAIT specified
This problem comes up only if I start two session simultaneously, which is when I am using the explain plan thing.
I think of two possible causes: either Oracle locks the PLAN_TABLE with a NOWAIT (then I don't know what to do ) Or, the problem may be in my code! After I loop through, I TRUNCATE the PLAN_TABLE. (then I do know what to do )
Or, is it entirely something else
Please advise.
Thanks,
Leslie
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Leslie,
I don't know if this is relevant (since your problem description is not very clear to me), but, in my experience (Oracle 8.1.7.4 on SUN [sparc] Solaris 7 wit JDK 1.3.1 and Oracle [thin] JDBC driver), you cannot simultaneously invoke more than one "PreparedStatement.executeUpdate()" on the same "Connection".
On the other hand, if you are trying to access the same Oracle object from two, separate "Connection"s, then Oracle may not allow this.
You should be able to find more information from the links I posted in my previous reply.
Hope this helps.
Good Luck,
Avi.
 
Leslie Chaim
Ranch Hand
Posts: 336
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Avi,
Let me first briefly describe my problem:
I have a server process, which is multi-threaded. Clients send their command(s) to the uniquely created (for each client) Request objects. Each request object has a Connection to the database.
The first command is always to prepare the statements which is when I am using the explain plan as follows:

I do have a separate connection (encapsulated in each Request object) for each client. However, they do all access the same Oracle object: the PLAN_TABLE.
If I start client A in the background, followed immediately by client B, the second client (Client B) fails with an: ORA-00054: resource busy and acquire with NOWAIT specified. However, if I wait 10 seconds before starting Client B it runs fine.
Question: does Oracle lock the PLAN_TABLE when doing explain plan, or is the problem where I truncate?
Thanks for your help thus far,
Leslie
 
Leslie Chaim
Ranch Hand
Posts: 336
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just to make this thread complete:
I was TRUNCATEing the table based on some advice. I modified my code to do a DELETE followed by a conn.commit() and all is fine.
Thanks again Avi for your help.
Cheers,
Leslie
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic