SQL92, OUTER JOIN, ODBC, MsTxSrvr and ORACLE

We are attempting to write a database vendor neutral product that uses ODBC and Microsoft's Transaction Server (MTS). Microsoft claims (Q191168) that we must use the MS Oracle ODBC driver (we have version 2.573.3513). However, this driver appears to mangle the LEFT OUTER JOINs. The Oracle 8 ODBC driver handles the translation correctly, but I have been unable to use it and MTS. ("Fail to Enlist on Calling Object's Transaction").

Here's the pre-ODBC SQL:

SELECT
name.Name,
title.Dsc,
type.Dsc
FROM
{oj Prsn_Name name LEFT OUTER JOIN Name_Title title ON name.Title_Id =
title.Title_Id },
{oj Prsn_Name name2 LEFT OUTER JOIN Name_Type type ON name2.Type_Id =
type.Type_Id }
WHERE
name.Name_Id = name2.Name_Id

Here's what Oracle sees when going through MS Oracle ODBC driver:

SELECT
name.Name,
title.Dsc,
type.Dsc
FROM
Prsn_Name name,
Name_Title title,
{oj Prsn_Name name2 LEFT OUTER JOIN Name_Type type ON name2.Type_Id =
type.Type_Id }
WHER}
name.Name_Id = name2.Name_Id
where
name.Title_Id (+) = title.Title_Id

Note the corruption of the keyword WHERE, the lack of translating the second LOJ.

If I remove one of the LOJs (and any columns that are part of the join) the command appears to work, although running it a second time it also is corrupted.

What I'm looking for is:
a) Does MS have an Oracle driver that works? (with MTS and LOJs) b) Is there a way to use Oracle's driver with MTS?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 01 1999 - 13:05:13 CST