Solved

Generic SQL syntax verification via ADO or ODBC

Posted on 2006-07-03
7
306 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:const71
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17034951
I see what you want, but unfortunately, there is no way that I know of.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17034964
>>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
 

Author Comment

by:const71
ID: 17034969
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 17034991
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
 

Author Comment

by:const71
ID: 17035004
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17035028
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
 

Author Comment

by:const71
ID: 17035037
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question