How to validate sql query syntax?

java 1.4 Sql server 2000

i am taking input of sql query (for validation of field value against values retrieved by executing sql query) from admin user which will be stored in database and later i will executi开发者_JS百科ng sql query corresponding to field.Before inserting sql query in database i want to validate its syntax in java code.

Fields         Sql Query

stateCode      select statecode from states
district code  select district code from districts

Create a PreparedStatement with the query string; if this works, the query string is ok (but nothing is executed yet)

dont think there is any (easy) way to validate sql

Sql syntax is complex and allows for alot of different ways to enter a statement.

Think you best shot would be to just execute the sql statent and if you have a SQl exception see if its a bad syntax thats causing it.

you can prepend some sql to avoid from actually executing the query

in sybase it would be SET NOEXEC ON

Why would you let them enter whole sql-statements?

Just provide to fields and let them enter either the statecode or the districtcode.

Then check if the entered value is a number. And run the appropriate query with the entered value.

A possible solution would could be to get the explain plan of the query, if it manages to explain the query I guess it must be valid. Down side is that it won't like parametrised queries.

You could do SET FMTONLY ON and then execute the query and see if it works. Just remember to do SET FMTONLY OFF in a finally block, since it's a connection-level setting.

You may need a full SQL Parser to do such a vendor-specific offline SQL syntax check.

Take a look at this demo which including some Java and C# code:

http://www.dpriver.com/blog/list-of-demos-illustrate-how-to-use-general-sql-parser/vendor-specific-offline-sql-syntax-check/