Oh, that explains it :)
As a quick query why does the ODBC driver report them as available? As this
information is normally used by programs to determine if outer joins can be
used then surely they should be reported as not working, IMHO (I'm guessing
that humans read the docs to find a db's features). If this where the case
then IIRC Access '97 would use its own method of performing the join (which
is inefficient, but works).
- Stuart
> -----Original Message-----
> From: Jean-Michel POURE
> Sent: Wednesday, April 11, 2001 6:18 AM
> To: Henshall, Stuart - WCP
> Subject: Re: [ODBC] Access '97 {oj ...} Left Join
>
> Hello,
>
> LEF JOINS are not yet implemented in the odbc driver.
> Use pass-through queries instead.
> I will post a small how-to in a few days explaining things in detail.
>
> Best regards,
> Jean-Michel POURE
>
> At 15:57 10/04/01 +0100, you wrote:
> >Has any one else had problems Access '97 changing the left join
> syntax,eg:
> >SELECT jobs_pg.id, edition_pg.edid
> >FROM jobs_pg LEFT JOIN edition_pg ON jobs_pg.id = edition_pg.id;
> >to:
> >SELECT "jobs","id","edition","edid" FROM {oj "jobs LEFT OUTER JOIN
> "edition"
> >ON ("jobs"."id"="edition"."id") }
> >Where id and edid are the primary keys.
> >Is there an easy way around this that I've missed?
> >Having a quick look at the ODBC 3.0 SDK it seems this is an ODBC thing.
> It
> >appears that ODBC use an escape sequence for outer joins. Here's the BNF
> >(according to the ODBC SDK 3.0):
> >ODBC-outer-join-escape ::=
> > ODBC-esc-initiator oj outer-join ODBC-esc-terminator
> >outer-join ::= table-name [correlation-name] {LEFT | RIGHT | FULL}
> > OUTER JOIN{table-name [correlation-name] | outer-join} ON search-
> > condition
> >correlation-name ::= user-defined-name
> >ODBC-esc-initiator ::= {
> >ODBC-esc-terminator ::= }
> >
> >It also mentions that it looks to SQLGetInfo with SQL_OJ_CAPABILITIES.
> But I
> >couldn't find any thing usefull (bar totally turning them off :( )
> >Theres a couple of alternatives that I can see, views or ODBC Pass
> Through.
> >If any one has a better solution (I am being blind and missing an option
> or
> >whatever) please share.
> >(Incase you're wondering why I'm not using Pass through queries and views
> >from the start, its because I've just converted an Access '97 file based
> db,
> >and I'm guessing if I've come up against this problem, others might).
> >- Stuart
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org