Hi all - here's an interesting one for you,

We're using engine 9.2.UC3 (I know it's old but our hands are tied) and
accessing it through ADO. We're trying to upgrade the client side
drivers from 2.81 to 3.82. The problem that we're getting with v3.82 is
this...

If you have ADO's cursor location to client side and fire a LEFT OUTER
JOIN, then it doesn't return the rows that don't match. For example,

SELECT *
FROM department AS D
LEFT JOIN staff AS S
ON D.ID = S.ID

should return all departments (and corresponding staff), however, it
only returns departments with staff. If I change the curser to
serverside, then it works correctly and returns all departments.

To take things a stage further (if this helps) when I then use ADO's
"getrows" function on the recordset it fails with an "eFail" error. I
don't get this when I'm using criteria that ensures that all rows are
joined.

It's the same when I'm using an XP or NT machine for the client, and
using v2.5 of the client driver works perfectly fine.

Anybody ever heard of anything similar? Any ideas as to what's going
on? Any ideas?

Thanks,
Andy

Andy wrote:

IB that the 2.81 drivers were for the pre-ANSI enabled IDS servers so it's
likely eating the LEFT keywork leaving you with an INNER join. Try changing
the query to specify the OUTER keyword explicitely:

SELECT *
FROM department AS D
OUTER JOIN staff AS S
ON D.ID = S.ID
;

By leaving out the OUTER keyword you are taking advantage of an ANSI SQL
feature making that keyword optional if LEFT, RIGHT, or FULL are specified
since these MUST be OUTER joins. Likewise, an OUTER JOIN where LEFT, RIGHT
or FULL are not specified is a LEFT OUTER JOIN.

Art S. Kagel


> Hi all - here's an interesting one for you,
>
> We're using engine 9.2.UC3 (I know it's old but our hands are tied) and
> accessing it through ADO. We're trying to upgrade the client side
> drivers from 2.81 to 3.82. The problem that we're getting with v3.82 is
> this...
>
> If you have ADO's cursor location to client side and fire a LEFT OUTER
> JOIN, then it doesn't return the rows that don't match. For example,
>
> SELECT *
> FROM department AS D
> LEFT JOIN staff AS S
> ON D.ID = S.ID
>
> should return all departments (and corresponding staff), however, it
> only returns departments with staff. If I change the curser to
> serverside, then it works correctly and returns all departments.
>
> To take things a stage further (if this helps) when I then use ADO's
> "getrows" function on the recordset it fails with an "eFail" error. I
> don't get this when I'm using criteria that ensures that all rows are
> joined.
>
> It's the same when I'm using an XP or NT machine for the client, and
> using v2.5 of the client driver works perfectly fine.
>
> Anybody ever heard of anything similar? Any ideas as to what's going
> on? Any ideas?
>
> Thanks,
> Andy

Changing it to use a LEFT OUTER JOIN instead of a LEFT JOIN worked
perfectly.

That's fantastic, thank you Art.

Andy



Art S. Kagel wrote:
> Andy wrote:
>
> IB that the 2.81 drivers were for the pre-ANSI enabled IDS servers so
> it's likely eating the LEFT keywork leaving you with an INNER join. Try
> changing the query to specify the OUTER keyword explicitely:
>
> SELECT *
> FROM department AS D
> OUTER JOIN staff AS S
> ON D.ID = S.ID
> ;
>
> By leaving out the OUTER keyword you are taking advantage of an ANSI SQL
> feature making that keyword optional if LEFT, RIGHT, or FULL are
> specified since these MUST be OUTER joins. Likewise, an OUTER JOIN
> where LEFT, RIGHT or FULL are not specified is a LEFT OUTER JOIN.
>
> Art S. Kagel
>
>
>> Hi all - here's an interesting one for you,
>>
>> We're using engine 9.2.UC3 (I know it's old but our hands are tied)
>> and accessing it through ADO. We're trying to upgrade the client side
>> drivers from 2.81 to 3.82. The problem that we're getting with v3.82
>> is this...
>>
>> If you have ADO's cursor location to client side and fire a LEFT OUTER
>> JOIN, then it doesn't return the rows that don't match. For example,
>>
>> SELECT *
>> FROM department AS D
>> LEFT JOIN staff AS S
>> ON D.ID = S.ID
>>
>> should return all departments (and corresponding staff), however, it
>> only returns departments with staff. If I change the curser to
>> serverside, then it works correctly and returns all departments.
>>
>> To take things a stage further (if this helps) when I then use ADO's
>> "getrows" function on the recordset it fails with an "eFail" error. I
>> don't get this when I'm using criteria that ensures that all rows are
>> joined.
>>
>> It's the same when I'm using an XP or NT machine for the client, and
>> using v2.5 of the client driver works perfectly fine.
>>
>> Anybody ever heard of anything similar? Any ideas as to what's going
>> on? Any ideas?
>>
>> Thanks,
>> Andy