Is there a way to verify SQL Syntax before executing?

[Becks25Not]

Hi All,

I was wondering if there was a way to check the syntax of an SQL statement from within .NET much like the check Syntax in Query Analyzer without executing the statement and waiting for errors?

Thanks!!!
Becca

 

[Dimandja]

The only way I know of is to actually execute the statement, but trap the errors. Use the Try...Catch statement.

If the is a way to invoke a compiler, or syntax checker in .NET I'd like to know to.

__________________________________________
Try forum1391 for lively discussions

 

[ThatRickGuy]

I'd doubt it. SQL varies from database engine to database engine. In addition, different versions can have different SQL. And it's the provider(driver)'s job to figure it out. So a generic oledb connection won't be able to validate the SQL because it doesn't know what it's working with.

Specific provider classes (ie: sql server objects) may be able to as they know the engine and driver that are being used.

-Rick

----------------------

http://www.ringdev.com

 

[ca8msm]

Becks25Not,

ThatRickGuy is correct in saying that it differs between db engines.

The OracleCommand (if you are working with an Oracle db) has a Prepare method (e.g. OracleCommand.Prepare) that allows you to create a prepared (or compiled) version of the command at the data source.

I don't know off hand if there is such a thing for another other database types.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[Becks25Not]

Thanks for the responses. I am using SQL Server. I'll check if there is a Prepare method there. I'll let ya know

 

[ca8msm]

BTW - I've just check the SQLClient and there is a Prepare method so you may be able to use this.

Hope this helps

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[chrissie1]

If CommandType is set to StoredProcedure, the call to Prepare should succeed, although it may result in a no-op.

Before you call Prepare, specify the data type of each parameter in the statement to be prepared. For each parameter that has a variable length data type, you must set the Size property to the maximum size needed. Prepare returns an error if these conditions are not met.

If you call an Execute method after calling Prepare, any parameter value that is larger than the value specified by the Size property is automatically truncated to the original specified size of the parameter, and no truncation errors are returned.

Output parameters (whether prepared or not) must have a user-specified data type. If you specify a variable length data type, you must also specify the maximum Size.

this is what msdn says about prepare. So I don't think this will really help.
I do What dimandja said.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[ca8msm]

If CommandType is set to StoredProcedure, the call to Prepare should succeed, although it may result in a no-op.

I don't think Becks25Not is using a SP as he said "a way to check the syntax of an SQL statement" so that section of the article may not be relevant.

We'll have to see if it does work - it would be much easier if it does as simply using a Try...Catch will actually run the command. If it is a query that takes a long time to run, then it would be much better if yo could actually check if the syntax is correct before starting the statement.



----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[ThatRickGuy]

We'll have to see if it does work - it would be much easier if it does as simply using a Try...Catch will actually run the command. If it is a query that takes a long time to run, then it would be much better if yo could actually check if the syntax is correct before starting the statement.

But be careful, a cartesian join is valid syntax

-Rick

----------------------

http://www.ringdev.com

 

[ca8msm]

But be careful, a cartesian join is valid syntax

Very true! Just because the syntax is valid doesn't mean the SQL statement is correct!

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[Becks25Not]

Sorry for the late update ...

I was trying trying to validate the syntax (if there statement is wrong, then that's there problemo!!)

Anyway, I never found a way to validate the syntax. So if anyone every finds a way, I'd love to know!!

Thanks ~ Becca

 

[ca8msm]

Did the Prepare method not say if the SQL was valid then (casting aside the potential of the SQL just being written to return unexpected results)?

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[Becks25Not]

I wasn't using Stored Procedures.

 

[ca8msm]

I didn't say you were....???

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[jebenson]

Perhaps a quicker way to do a test is to do as Dimandja suggested and run the query and trap any errors, but add "WHERE 1=0" to the WHERE clause (don't add an extra WHERE, of course). This would return 0 rows if successful (i.e., no errors) and should be much quicker than executing a query that actually returns rows. You can then run the "real" command without the "1=0", or take other action if the "1=0" query fails.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[MattBeas]

I'd use the following syntax:

Try
execute sql statement
catch
msgbox ex.message
exit sub
end try

The ex.message will return any SQL errors within the sql statement.

Hope this helps!

 

[ca8msm]

If the Prepare method doesn't work with Dynamic SQL (i.e. not a Stored Procedure - did you even try this???) then I personally like jebenson's method as it will always return 0 rows and should run fairly quickly.

MattBeas - the only problem with that method is that if the user enters a SQL statement that takes a while to run, it will execute the statement and the idea was just to see if it could be run. Don't get me wrong, a Try/Catch should be used as well to trap any other errors but I just don't think it would be the best method for what the poster wants.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[Becks25Not]

Thanks for all the responses ...

I use a try - catch to test the execution. The queries do take a while to run (part of the reason for the code). The series took 9+ hours to run all the queries. And they are not always the same queries. Because of this, they are usually started as the user is going home. As each one runs, I display on a form which query ran and how long it took or if it failed. The concern was/is, if several of them fail, they will have to be run again. The time frame is pretty tight so serveral failures could cause the user to miss a deadline.

Unfortunately, b/c the code is done and they only had 1 failure in the first run, mgmt has switched my priorities (until several fail and the deadline is missed) ... we all know how that goes.

 

[ThatRickGuy]

Wouldn't it be easier to ensure that the queries have proper syntax when they are writen? Then when in your code you don't have to make a pre-call (where 1=0) to ensure the syntax is correct. But using a Try-Catch block will allow you to catch any errors and is a good idea any time you interact with a database.

-Rick

----------------------

http://www.ringdev.com

 

[Becks25Not]

Wouldn't it be easier to ensure that the queries have proper syntax when they are writen?

Yes it would but I have no control!!