• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

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
0
const71
Asked:
const71
  • 3
  • 2
  • 2
1 Solution
 
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
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Anthony PerkinsCommented:
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
 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now