?
Solved

Generic SQL syntax verification via ADO or ODBC

Posted on 2006-07-03
7
Medium Priority
?
309 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

801 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