How to write database independent SQL?

  • Thread starter Thread starter Heinz Kiosk
  • Start date Start date
H

Heinz Kiosk

ODBC includes numerous scalar functions and calls such as SQLGetInfo that
allow an application to generate SQL that works regardless of the targetted
back-end database (within reason). Is there any equivalent of this in ADO.
How do you generate database independent SQL in ADO?

Tom
 
For most SQL, using ANSI-92 standard SQL will work across all databases.
There are some exceptions I can think of with Access (sure there are
others), but these exceptions generally deal with the way Access handles
JOINs. I have not tested with the latest MDAC, so the exceptions may be a
thing of the past.

If you move from T-SQL to ANSI-92 SQL, you should have few exceptions with
the data providers.

I wish I had a more promising answer that would be 100% compatible with all
databases, but ANSI-92 is the best I can aim for.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
Thank you Gregory,

Do you know any way of finding out exactly what SQL-92 consists of short of
bunging $90 at 5 URLs like
http://www.techstreet.com/cgi-bin/detail?product_id=101885

eg I'm interested in the exact rules for things like date constants and
conversion functions. And exactly what version of OJ/IJ syntax should apply,
and quoted identifiers; stuff like that. Then I can check these against a
few of my proposed targets to make sure that all is well at the moment.

Tom
 
Cowboy \(Gregory A. Beamer\) said:
For most SQL, using ANSI-92 standard SQL will work across all databases.
There are some exceptions I can think of with Access (sure there are
others), but these exceptions generally deal with the way Access handles
JOINs. I have not tested with the latest MDAC, so the exceptions may be a
thing of the past.

If you move from T-SQL to ANSI-92 SQL, you should have few exceptions with
the data providers.

I wish I had a more promising answer that would be 100% compatible with all
databases, but ANSI-92 is the best I can aim for.
Click to expand...


The SQL-92 standard is divided into the following levels: entry,
intermediate and full SQL-92, along with a transitional level (defined
by FIPS) covering some features of intermediate level. Entry level
contains the set of features that define basic SQL-92 compliance.

When vendors claim they are SQL-92 compliant it is important to know
at which level.

The SQL Validator is an on-line tool verifying standard compliance.
Its a great time-saver when writing portable applications.

http://developer.mimer.com/validator/

It has support for SQL-92, SQL-99, and even SQL-2003 (draft).

Sticking to entry level and transitional level SQL-92 will probably
save you a lot of trouble. AFAIK no database product supports full
SQL-92.


Regards,
Jarl
 
The standards doc that I have consulted is here:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt. I would suggest
going to ANSI.org, but they make you pay to view the docs. There are
numerous books that cover ANSI SQL-92 on the market that are much cheaper
than joining the ANSI site.

For many apps, the type of database is queried on the setup and the proper
DLL version for that database is hooked up to the application. It is a bit
more painful, but you are much more open to use SQL functions for the
particular database. Generic SQL is easier, but you will likely be
restricted to the base level of the ANSI-92 standard to meet most databases
(I do not say all, as there are a lot of strange children out there; you
will get the main vendors, along with most minor vendors with SQL-92
standard).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
Back
Top