Compiling SQL statement before execute

[Leslie Chaim]

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]

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]

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]

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]

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]

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]

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:

public boolean prepareStatements (StatementUnit[] stmts) throws SQLException, BadDataException
{
   // ...
   // Preapre all the statements ...
   // Ommited for clarity
   // ...
   Statement validateSQL = null;
   for ( int i = 0; i < numStatements; i++ )
   {
      try
      {
         if ( doDelete == true )
         {
            validateSQL = conn.createStatement();
            validateSQL.executeUpdate("EXPLAIN PLAN FOR " + allStatements[i].getDeleteStatement());
            validateSQL.close();
            validateSQL = null;
            sqlDeleteHandles[i] = conn.prepareStatement(allStatements[i].getDeleteStatement());
         }
         if ( doInsert == true )
         {
            validateSQL = conn.createStatement();
            validateSQL.executeUpdate("EXPLAIN PLAN FOR " + allStatements[i].getInsertStatement());
            validateSQL.close();
            validateSQL = null;
            sqlInsertHandles[i] = conn.prepareStatement(allStatements[i].getInsertStatement());
         }
      }
      catch (SQLException sqle)
      {
         throw new BadDataException ("Error while doing " + allStatements[i].getTableName(), sqle);
      }
   }
   validateSQL = conn.createStatement();
   validateSQL.executeUpdate("truncate table plan_table");
   validateSQL.close();
   validateSQL = null;
   // Clean up any mess from all objects used to validateSQL
   System.gc();
   return true;
}

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]

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