VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL
Syntax

We are trying to find a common standard for Oracle 8i and SQL Server 2000
for SQL commands in ADO 2.5 objects and VB6 code. They use different a
syntax for outer joins (one uses a (+) and the other a *:

Oracle 8 apparently also requires that the table name is sent between quotes
(although documentation says otherwise), whereas SQL 2000 forbids it.

Ex.

Oracle: select "t1".id from t1,t2 where t1.field1 (+)= f2.field2

SQL Server: select ti.id from t1,t2 where t1.field1 *= f2.field2

Can anyone help?

Thanks in advance!

Jorge Ferreira

Hello,

    I think you are going to find even more differences between Oracle and
SQL-Server.  I have used both for some time now.  I have been using the
Microsoft OLEDB Provider for Oracle and I have not had to enclose the table
names within quotes.  Using the example below, if the outer-join were on the
other side of the equaision the format is different again:

Oracle: select t1.id from t1,t2 where t1.field1 = f2.field2 (+)

SQL Server: select t1.id from t1,t2 where t1.field1 =*  f2.field2

    Faced with this problem, I have moved to using stored procedures, you
could still use SQL, and an XML document, one for Oracle and another for SQL
Server.  It has a common name and then the code necessary for the SQL/stored
procedure.  eg.

ORACLE:
<DataDictionary>
    <Proc>
        <Name>GetStuff</Name>
        <Text>select t1.id from t1,t2 where t1.field1 (+) = f2.field2</Text>
    </Proc>
</DataDictionary>

SQLServer:
<DataDictionary>
    <Proc>
        <Name>GetStuff</Name>
        <Text>select ti.id from t1,t2 where t1.field1 *= f2.field2</Text>
    </Proc>
</DataDictionary>

Or you could combine them into one document:
<DataDictionary>
    <Proc>
        <Name>GetStuff</Name>
        <SQLServer>select t1.id from t1,t2 where t1.field1 *=
f2.field2</SQLServer>
        <Oracle>select t1.id from t1,t2 where t1.field1 (+) =
f2.field2</Oracle>
    </Proc>
</DataDictionary>

    You would then create a function that you would pass a value of <Name>
and database type, and it could return the statement for the Oracle or
SQLServer.  If you have fields that you want to declare dynamically for the
where clause you could use some form of delimiter and then search and
replace them once you get the SQL string back.  eg

<DataDictionary>
    <Proc>
        <Name>GetStuff</Name>
        <SQLServer>select t1.id from t1,t2 where t1.field1 *= f2.field2 AND
t1.SomeField = '[Var1]'</SQLServer>
        <Oracle>select t1.id from t1,t2 where t1.field1 (+) = f2.field2 AND
t1.SomeField = '[Var1]'</Oracle>
    </Proc>
</DataDictionary>

    After getting back the SQL, you would search for the variable '[Var1]'
and replace that with your dynamic value.

Hope that helps

In His Love
scott

About outer join syntax, it seems that Oracle is not willing to support
SQL92 for now.

You can use the ODBC syntax for outer joins, { oj ...} , which is supported
by current OLEDB providers and ODBC drivers:

Example:

SELECT .. FROM { oj T1 LEFT OUTER JOIN T2 ON T1.A=T2.A AND T1.B = T2.B }
WHERE T1.C = 3

--
Best regards,

Carlos J. Quintero

MZ-Tools 2.0 freeware 'all-in-one' Add-In for VB6: Task List, Line
Numbering, TabIndex Assistant, Procedure Callers, Find and Replace in All
Projects, Close Windows, Review TabIndex and Collections, Add Procedure, Add
Error Handler, Add Procedure or Module Header, Clear Immediate Window,
Statistics, Copy/Paste/Rename/Remove controls with code, Default Controls
Properties and more:
www.mztools.com

Oracle only requires the quotes if the table name is not uppercase.  If the
tablename is upper case the quotes are not required and it is not case
sensitive.  This holds true for column names also.

If the table was created with quotes (create table "t1" ...) quotes would be
required.  If it was created (create table t1...), it would not.  Frequently
when tables are created from Access tables this is a problem.

PJ