
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