Generic SQL syntax verification via ADO or ODBC

Is there a way through ADO or ODBC to check the SQL syntax before executing it. I know certain
databases have this functionality built in however is there some way to do this in a program without
knowing in advance which database is to be used?

Thanks
const71Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Sure.  Providing the provider supports asynchronous operations.  The real problem is trying to make it generic, unfortunately what usually happens here is that you end up with the lowest common denominator.  For example, if you were to restrict it just to MS SQL Server, than you could add the following and it would be equivalent of checking syntax:
SET FMTONLY ON

No doubt Oracle has something similar.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see what you want, but unfortunately, there is no way that I know of.
0
 
Anthony PerkinsCommented:
>>however is there some way to do this in a program without knowing in advance which database is to be used?<<
Not without a significant investment.  Each SQL dialect is different. The very best you can do is try and execute it using ADO and catch any errors.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
const71Author Commented:
acperkins

the problem with this method is that a correctly parsed statement may take a long time to execute. I suppose one way is to run the query asynchronously and wait for the event signalling it is executing and cancel the query.
0
 
const71Author Commented:
True,

Although asynchronous operation can be generically coded. I see your point though. I guess I will
look at all the different ways the various vendors do it (that is if they do it) and use some kind of
switch depending on what  db is used.

Thanks acperking
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you COULD run the sql with a added where clause part like 1=0, which will return 0 rows in all cases, be very fast, but return syntax errors in case there are...

0
 
const71Author Commented:
angelIII,

I would use a vendor specific approach first but if none exists that certainly
is a great quick and dirty method to get the job done!

Thanks for the suggestions all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.