Verifying an sql statement is syntactically correct

[William Wild]

Hi all,
I am trying to find a java api that can verify whether a sql statement has been written syntactically correct. My sql statements will be dynamically created at run time, and I would like to have them verified proir to sending to the db, rather than wasting time running the search and then finding the statement contained flaws.
My db is Oracle9iRelease2.
Regards
Bill

[Mark Spritzler]

Thats kind of tough because it is dynamically created. Testing you code is one way to make sure that the query built will always be correct. Catching the Exceptions will also work, or have you tried using the dynamic query package in Oracle, dbms_sql.
Question at AskTom that might help:
First.
or do a search at AskTom, there are lots of them in his archives.
Mark

[SJ Adnams]

i don't see what is wrong with catching the syntax errors?
I'd say writing an sql syntax checker is a bigger waste of time.

[William Wild]

Simon,
What I am doing is to create an sql statement from an xpath statement. So I need the verification (principly during the construction of the parser) so I can check the resultant.
Mark
Having a look at it all now. Thanks for the pointer to AskTom, didn't even know that was there :-)
Cheers
Simon

[Avi Abrami]

Hi William,
The way we do that is to prefix the SQL statement with "EXPLAIN PLAN FOR ", and then invoke the "executeUpdate()" method (of class "[Callable/Prepared]Statement". This merely parses the SQL statement (and throws an "SQLException" if it is incorrect).
Does this help you?
Good Luck,
Avi.

[Mark Spritzler]

Avi, that is a great idea. that way you not only have the SQL parsed if it is correct, but you can also get the syntax part down.
Mark