Feel like I should know this....

Does anyone know of a way to test a query for syntax without actually running the query?

I have an asp page that creates a series of nearly 50 INSERT statements from about 20 parameters entered by the user.

I don't want to even begin the process of doing the inserts unless all the queries have correct syntax and will work without error. (Reversing the process, if an error occurs on Insert statement #43 would be a real pain.)

My plan is to create ALL the Insert statements in an array... and then run them through some sort of syntax test PRIOR to actually submitting them to the database.

Some of the Inserts will get pretty complex... and depending on the user's parameters... some of which are based on retrieved data... there is a possibility of many odd syntax problems.

Any ideas?

Tim

Many databases have a DESCRIBE, EXPLAIN or similar statement that will show you the execution plan of a query. If your query has a syntax error the statement will throw an error too.
Another possibility would be to encapsulate all statements within a big transaction and roll it back if one statement fails, this saves you the work of undoing the queries yourself.
The "clean" way would be to check all parameters for validity _before_ executing any query. This is also strongly advisable from a security standpoint to prevent SQL injection attacks.

sorry for editing ur post ;-)

hey, u can try using objConn.BeginTrans at start of processing of INSERT statements and on error use Rollback. I donno how helpful this is

btw .try this page for some help
http://www.w3schools.com/ado/met_conn_begintrans.asp

Instead of acting on the SQL statements you could have your Web page simply print the statements. Then you could separately test them one by one by copying and pasting them into something like the query builder area of Access changing any % to be * instead.

Thanks.... but I'm not sure I understand you're suggestion.

The point is that I'm already certain of the basic structure of the SQL... that's no problem. My issue is with the data that a user might enter.

I don't want to wait until I actually submit the query to know if it will work ok. I'm looking for a way to test the query... just as if it were submitted... but not actually have it processed.

The issue is that these multiple queries rely on diverse and unpredictable data entry.... with untrusted users. In addition, if submitted, the queries willl take a LONG time to run, and will modifiy existing data..... which means reversing them will be a big of a nightmare.

What I'm now doing is simply checking for illegal characters before submitting the query.

Tim

This is the better way to check for illegal characters before submitting the query.

Yes, if the users are untrusted you MUST validate the entered data correctly before running the query. A malicious user might construct parameters that will actually pass your "execution test" but do harmful things to your data.
Imagine this simple query:
An attacker could pass something like:
as MyUntrustedParameter. The query would execute and delete all records in MyTable.

I'd suggest testing with sample data instead of the real thing (i.e. perhaps use a copy of the database or something if possible).