Thread: Access '97 {oj ...} Left Join
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
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
"Henshall, Stuart - WCP" wrote:
>
> 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).
It seems you are right.
I would change the driver report after the release of
7.1. Or hopefully I may be able to implement the {oj
functionality.
regards,
Hiroshi Inoue
> -----Original Message-----
> From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
> Sent: 11 April 2001 11:42
> To: Henshall, Stuart - WCP
> Cc: 'Jean-Michel POURE'; 'pgsql-odbc@postgresql.org'
> Subject: Re: [ODBC] Access '97 {oj ...} Left Join
>
>
> "Henshall, Stuart - WCP" wrote:
> >
> > 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).
>
> It seems you are right.
> I would change the driver report after the release of
> 7.1. Or hopefully I may be able to implement the {oj
> functionality.
The driver reports that it is OJ capable if it is connected to 7.1 or
higher. When I made the mods to the driver I tested successfully the OJs did
work (this *may* not be the case if the Parse Statements option is
selected).
What doesn't work is the {oj...} style syntax as Stuart reported (which
would have made my life easier when re-coding the SQL Wizard in pgAdmin!),
but this is because PostgreSQL doesn't support it, not the driver. The
following style syntax works a treat via ODBC:
SELECT * FROM foo
LEFT OUTER JOIN bar ON foo.col1 = bar.col1
Regards, Dave.
Dave Page wrote:
[snip]
>
> The driver reports that it is OJ capable if it is connected to 7.1 or
> higher. When I made the mods to the driver I tested successfully the OJs did
> work (this *may* not be the case if the Parse Statements option is
> selected).
>
> What doesn't work is the {oj...} style syntax as Stuart reported (which
> would have made my life easier when re-coding the SQL Wizard in pgAdmin!),
> but this is because PostgreSQL doesn't support it, not the driver.
As Henshall pointed out, {oj ...} style syntax seems a
ODBC thing and the driver has to map the syntax to
DBMS-specific syntax. In fact psqlodbc driver already
supports {fn ..}, {dt ..} syntax etc ...
In my simple test case, mapping the escape part as-is
seems to work.
regards,
Hiroshi Inoue
> -----Original Message-----
> From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
> Sent: 12 April 2001 01:34
> To: Dave Page
> Cc: Henshall, Stuart - WCP; 'Jean-Michel POURE';
> 'pgsql-odbc@postgresql.org'
> Subject: Re: [ODBC] Access '97 {oj ...} Left Join
>
>
> Dave Page wrote:
>
> [snip]
>
> >
> > The driver reports that it is OJ capable if it is connected
> to 7.1 or
> > higher. When I made the mods to the driver I tested
> successfully the OJs did
> > work (this *may* not be the case if the Parse Statements option is
> > selected).
> >
> > What doesn't work is the {oj...} style syntax as Stuart
> reported (which
> > would have made my life easier when re-coding the SQL
> Wizard in pgAdmin!),
> > but this is because PostgreSQL doesn't support it, not the driver.
>
> As Henshall pointed out, {oj ...} style syntax seems a
> ODBC thing and the driver has to map the syntax to
> DBMS-specific syntax. In fact psqlodbc driver already
> supports {fn ..}, {dt ..} syntax etc ...
> In my simple test case, mapping the escape part as-is
> seems to work.
So the ODBC driver has to support syntax that isn't supported by the
backend? Hmm, I wasn't aware of that (not that that's unusual :-)).
Regards, Dave.
Dave Page wrote:
>
> > -----Original Message-----
> > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
> > Sent: 12 April 2001 01:34
> > To: Dave Page
> > Cc: Henshall, Stuart - WCP; 'Jean-Michel POURE';
> > 'pgsql-odbc@postgresql.org'
> > Subject: Re: [ODBC] Access '97 {oj ...} Left Join
> >
> >
> > Dave Page wrote:
> >
> > [snip]
> >
> > >
> > > The driver reports that it is OJ capable if it is connected
> > to 7.1 or
> > > higher. When I made the mods to the driver I tested
> > successfully the OJs did
> > > work (this *may* not be the case if the Parse Statements option is
> > > selected).
> > >
> > > What doesn't work is the {oj...} style syntax as Stuart
> > reported (which
> > > would have made my life easier when re-coding the SQL
> > Wizard in pgAdmin!),
> > > but this is because PostgreSQL doesn't support it, not the driver.
> >
> > As Henshall pointed out, {oj ...} style syntax seems a
> > ODBC thing and the driver has to map the syntax to
> > DBMS-specific syntax. In fact psqlodbc driver already
> > supports {fn ..}, {dt ..} syntax etc ...
> > In my simple test case, mapping the escape part as-is
> > seems to work.
>
> So the ODBC driver has to support syntax that isn't supported by the
> backend? Hmm, I wasn't aware of that (not that that's unusual :-)).
>
I don't know any DBMS which supports {oj syntax directly.
What I meant is to pass the outer join syntax inside the
{oj .. } as-is to 7.1 PostgreSQL servers.
regards,
Hiroshi Inoue
FYI Here's a little snippet from the ODBC 3.0 SDK:
Applications can submit statements using ODBC or DBMS-specific
grammar. If a statement uses ODBC grammar that is different from
DBMS-specific grammar, the driver converts it before sending it to the data
source. However, such conversions are rare as most DBMSs already use
standard SQL grammar.
I think the Idea is to make a client program using ODBC able to use
any backend as long as it supports the features that are being used.
Btw, if any one makes a build that solves the {oj ...} problem (wether by
supporting it or disabling it) please could they pop me a copy (or make such
a copy available) as I don't have M$VC,
Thanks,
- Stuart
> -----Original Message-----
> From: Hiroshi Inoue
> Sent: Wednesday, April 18, 2001 9:47 AM
> To: Dave Page
> Cc: Henshall, Stuart - WCP; 'Jean-Michel POURE';
> 'pgsql-odbc@postgresql.org'
> Subject: Re: [ODBC] Access '97 {oj ...} Left Join
>
> Dave Page wrote:
> >
> > > -----Original Message-----
> > > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
> > > Sent: 12 April 2001 01:34
> > > To: Dave Page
> > > Cc: Henshall, Stuart - WCP; 'Jean-Michel POURE';
> > > 'pgsql-odbc@postgresql.org'
> > > Subject: Re: [ODBC] Access '97 {oj ...} Left Join
[SNIP]
> > >
> > > As Henshall pointed out, {oj ...} style syntax seems a
> > > ODBC thing and the driver has to map the syntax to
> > > DBMS-specific syntax. In fact psqlodbc driver already
> > > supports {fn ..}, {dt ..} syntax etc ...
> > > In my simple test case, mapping the escape part as-is
> > > seems to work.
> >
> > So the ODBC driver has to support syntax that isn't supported by the
> > backend? Hmm, I wasn't aware of that (not that that's unusual :-)).
> >
>
> I don't know any DBMS which supports {oj syntax directly.
> What I meant is to pass the outer join syntax inside the
> {oj .. } as-is to 7.1 PostgreSQL servers.
>
> regards,
> Hiroshi Inoue
> -----Original Message-----
> From: Henshall, Stuart - WCP
> [mailto:SHenshall@westcountrypublications.co.uk]
> Sent: 18 April 2001 10:27
> To: 'Dave Page'; 'Hiroshi Inoue'
> Cc: 'Jean-Michel POURE'; 'pgsql-odbc@postgresql.org'
> Subject: RE: [ODBC] Access '97 {oj ...} Left Join
>
>
> FYI Here's a little snippet from the ODBC 3.0 SDK:
> Applications can submit statements using ODBC or DBMS-specific
> grammar. If a statement uses ODBC grammar that is different from
> DBMS-specific grammar, the driver converts it before sending
> it to the data
> source. However, such conversions are rare as most DBMSs already use
> standard SQL grammar.
> I think the Idea is to make a client program using ODBC
> able to use
> any backend as long as it supports the features that are being used.
> Btw, if any one makes a build that solves the {oj ...}
> problem (wether by
> supporting it or disabling it) please could they pop me a
> copy (or make such
> a copy available) as I don't have M$VC,
> Thanks,
> - Stuart
If Hiroshi is fixing it (are you - you mentioned some test code?) then at a
convenient point (when everyone isd happy) I'll do a build and package it up
ready to go on ftp.postgresql.org - I'll also announce it on the ODBC list.
Regards, Dave.
Dave Page wrote:
>
> > -----Original Message-----
> > From: Henshall, Stuart - WCP
> >
> > FYI Here's a little snippet from the ODBC 3.0 SDK:
> > Applications can submit statements using ODBC or DBMS-specific
> > grammar. If a statement uses ODBC grammar that is different from
> > DBMS-specific grammar, the driver converts it before sending
> > it to the data
> > source. However, such conversions are rare as most DBMSs already use
> > standard SQL grammar.
> > I think the Idea is to make a client program using ODBC
> > able to use
> > any backend as long as it supports the features that are being used.
> > Btw, if any one makes a build that solves the {oj ...}
> > problem (wether by
> > supporting it or disabling it) please could they pop me a
> > copy (or make such
> > a copy available) as I don't have M$VC,
> > Thanks,
> > - Stuart
>
> If Hiroshi is fixing it (are you - you mentioned some test code?) then at a
> convenient point (when everyone isd happy) I'll do a build and package it up
> ready to go on ftp.postgresql.org - I'll also announce it on the ODBC list.
>
I mailed a patched dll to Stuart and it seems to have worked.
I would commit it together with other patches in a convenient
point.
regards,
Hiroshi Inoue
> -----Original Message-----
> From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
> Sent: 18 April 2001 12:43
> To: Dave Page
> Cc: 'Henshall, Stuart - WCP'; 'Jean-Michel POURE';
> 'pgsql-odbc@postgresql.org'
> Subject: Re: [ODBC] Access '97 {oj ...} Left Join
<snip>
> >
> > If Hiroshi is fixing it (are you - you mentioned some test
> code?) then at a
> > convenient point (when everyone isd happy) I'll do a build
> and package it up
> > ready to go on ftp.postgresql.org - I'll also announce it
> on the ODBC list.
> >
>
> I mailed a patched dll to Stuart and it seems to have worked.
> I would commit it together with other patches in a convenient
> point.
OK, if you can increment the version number to 07.01.0005 in all the
relevant places as well (psqlodbc.h & psqlodbc.res x 3 (?)) then I'll do a
build for others to download & test.
Regards, Dave.
Dave Page <dpage@vale-housing.co.uk> writes:
> So the ODBC driver has to support syntax that isn't supported by the
> backend? Hmm, I wasn't aware of that (not that that's unusual :-)).
AFAIK *all* of the squiggly-brace constructs are ODBC-specific, and must
be translated to real SQL by the driver before they are sent to the
DBMS.
regards, tom lane