Very Computer

Raju,

Quote:> Our system needs to be database vendor independent.  We will be mainly
> talking to MS SQL Server, Oracle, Sybase and maybe IBM-DB/2.  My questions
> are:

> 1) Are all these vendors following one standard for SQL coding, ie. ANSI SQL.
> I think MS and Sybase have their version of SQL called Transact-SQL.

 They all adhere (more or less) to ANSI-92 but have their own extensions, functions and languages.  (e.g. TSQL)

Quote:> 2) What are the common pitfalls of ANSI SQL coding?  I am sure it works well
> with native commands but might not work for little complicated stuff like
> datepart, etc...  Our queries (as of now) are very simple and will probably
> work for all kinds of vendors. However, I do not want to rule out the idea
> of having some tricky joins as well as using in-built functions.

 You'll either have to use the lowest common denominator query, or try coding everything via ODBC and hope that
 each vendor's odbc driver is intelligent enough to use their own extensions.

Quote:> 3) Is there a FAQ on this or some research that somebody has already done on
> it?

 Not that I know of, but you could try www.ansi.org

  Neil Pike MVP/MCSE
 Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)

<SNIP>

Bernard's response is IMHO a bit misleading. The following is extracted from
Oracle documentation:

ANSI SQL Datatype                                                  Oracle
Datatype
CHARACTER (n), CHAR(n)                                     CHAR(n)
NUMERIC (p, s), DECIMAL (p, s) DEC (p, a)        NUMBER (p, s)
INTEGER, INT, SMALLINT                                       NUMBER (38)
FLOAT (p), REAL, DOUBLE PRECISION             NUMBER
CHARACTER VARYING(n), CHAR VARYING(n)  VARCHAR(n)

Thus you can use any ANSI datatype but implementation will vary by RDBMS.
DECIMAL implemention varies most (IIRC as FLOAT in one - Informix?).

ORACLE NUMBER is implemented in "scientific notation" and can hold integers
and decimal numbers with a precision of up to 38 digits.

Alan Macro
Hill Price Davison, London

"no-luncheon-meat."(i.e no spam)  should be removed from email address

Oracle's ODBC driver (2.5.0103 with patch) is not intelligent
enough to do this, and Oracle Support says that the ODBC driver
for Oracle 8 as well supports only inner joins ("Oracle's ODBC
driver 8.0.X only support [sic] inner joins" -- excerpt from an
email from Oracle support, dated 11/10/98).

These are such egregious shortcomings in an ODBC driver that I
interpret them as intentional: an attempt to discourage
development with and use of desktop applications that rely upon
ODBC.  Not that Microsoft doesn't resort to similar tactics. But
independent software developers and customers get caught in the
cross-fire.

We standardized on Oracle and use Objects for OLE with Visual
Basic. There are times when one wants to use a slick third-party
VB add-on (e.g. for reports) that does not support oo4o, and so
one has no recourse but to use ODBC via RDO or OLEDB(ADO), or to
subtract the "R" from RAD and work in "unbound" mode.

Tim Romano

Neil Pike wrote regarding join syntax and multi-vendor
compatibility:

Quote:> [...]
>  You'll either have to use the lowest common denominator query, or try coding everything via ODBC and hope that
>  each vendor's odbc driver is intelligent enough to use their own extensions. [...]

Correction to my posting.
That should be 2.5.0301 (not 0103).
Tim

Quote:>for Oracle 8 as well supports only inner joins ("Oracle's ODBC
>driver 8.0.X only support [sic] inner joins" -- excerpt from an
>email from Oracle support, dated 11/10/98).

as we're possibly forced to use oracle/odbc: querying views with outer
joins etc via odbc is not limited, is it?

What you have suggested is indeed how we circumvent the current
ODBC driver problems: we create views with whatever joins we need.
However, as you know, it is not unheard of (actually it's fairly
common) that a developer--one hired to write reports, for
example--will not have been granted the right to create views on
the server. And sometimes it's not even a developer writing these
queries/reports, but a lesser mortal, working with a desktop query
tool. So, the use-a-view approach is often not a viable
alternative.

Tim