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

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




Wed, 22 Oct 2003 02:05:07 GMT  
 VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax


Fri, 19 Jun 1992 00:00:00 GMT  
 VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax
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

Quote:

> 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





Fri, 24 Oct 2003 23:38:47 GMT  
 VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax
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



Quote:
> 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





Sat, 25 Oct 2003 00:36:30 GMT  
 VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax
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

Quote:

> 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





Wed, 29 Oct 2003 07:03:25 GMT  
 
 [ 5 post ] 

 Relevant Pages 

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

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

3. VB6 application using SQL Server 2000 migrating db to SQL Server 2

4. ADO Problem: VB6 hangs on sending a query to an SQL-Server 2000

5. Using ADO 2.5 Streams on SQL Server 7.0

6. problems with sql statement using access 2000, ado, vb6

7. SQL Connectivity - CR9 Developer Edition, VB6, SQL Server 2000

8. VB6/SQL Server 2000 ADO Best Practices question

9. ADO DLL VB6 - SQL Server 2000

10. know anything about Stored Proc and using SQL Server 2000, CR8.5 and VB6.0

11. ADO 2.5 Sql Server Ole Db Provider and Memory Loss

12. Help - SQL 7.0 , ADO 2.5 , Store Procedure , VB6 and MSDataGrid

 

 
Powered by phpBB® Forum Software