ANSI SQL COMPATIBILITY

[ma...@metratech.com]

Hello All

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.

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.

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

Please either respond to this post or mail me at ma...@metratech.com,
whichever is convenient.

Thanks

Raju Matta

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[David]

You need to separate SQL and Stored Modules (Orcale PL/SQL, MS Transact SQL)
when dealing with standards, because with the later developing standard code
is almost impossable. As more and more vedors (including MS) supply Java
Stored Modules this problem will go away.

This is quite a large and complex area I would recommend reading :

SQL Reference 8.0 : Section 1-3 and Appendix A

to get more information on Oracle's compliance. Remember always use the FIPS
flags to ensure you are writing Standard SQL.

Regards
David Russell

ma...@metratech.com wrote in message <71su9l$3hv$1...@nnrp1.dejanews.com>...

[Barbara Kennedy]

The problem is not writing standard SQL. That is the easy part! The problem
is to get the best performance you have to know something of how the API
works and think about what you are going to do.
The most common problem I have seen in large GUI systems is data efficiency.
I mean retrieving only what you need the minimum number of times you need
it. You would be surprised when you get more than two software engineers on
a project how they don't realize that.
Jim

[Neil Pike]

Raju,

> 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)

> 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.

> 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)

[Bernhard Mandl]

Oracle has only very few Datatypes, in fact you must limit yourself to char,
decimal and date if you want to be oracle / MS-SQL / db-2 compatible.

ma...@metratech.com wrote in message <71su9l$3hv$1...@nnrp1.dejanews.com>...
>Hello All
>

>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.
>

>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.
>

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

[Alan Macro]

Bernhard Mandl wrote in message <71va96$5km$1...@fleetstreet.Austria.EU.net>...

>Oracle has only very few Datatypes, in fact you must limit yourself to
char,
>decimal and date if you want to be oracle / MS-SQL / db-2 compatible.
>
>ma...@metratech.com wrote in message <71su9l$3hv$1...@nnrp1.dejanews.com>...
>>Hello All
>>
>>Our system needs to be database vendor independent. We will be mainly
>>talking to MS SQL Server, Oracle, Sybase and maybe IBM-DB/2.

<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
Al...@no-luncheon-meat.hpdi.demon.co.uk
"no-luncheon-meat."(i.e no spam) should be removed from email address