Re: PostgreSQL / ODBC / Ms-SQL 2000

> -----Original Message-----
> From: Thomas Knoop [mailto:Th(dot)Knoop(at)Talisma(dot)nl]
> Sent: 30 September 2005 10:12
> To: Dave Page; pgsql-odbc(at)postgresql(dot)org
> Subject: RE: [ODBC] PostgreSQL / ODBC / Ms-SQL 2000
>
> Hi Dave,
>
> >>> SELECT * FROM OPENQUERY(MYLINKEDSERVER, 'SELECT * FROM
> >>> public.mytable WHERE foo < 10 LIMIT 5')
>
> >> Well this does not work either, remember i'm creating a view....
>
> > CREATE VIEW foo AS SELECT * FROM OPENQUERY(MYLINKEDSERVER,
> > 'SELECT * FROM public.mytable WHERE foo < 10 LIMIT 5')
>
> ;) Yes, thanks, what i mean is that this view is limited to 5 records
>
> The application that needs to use this view treats this view
> as a table, and
> it will not get any result (or at least limited to the 5
> records) from it
> because it executes this command on the view:
>
> SELECT FLD1, FLD2 FROM foo WHERE (FDL1 = 20000)

That will always require an unrestricted select from the PostgreSQL
table then - there's nothing you can do about that because QA will need
to join the PostgreSQL and SQL tables locally.

> > However, SQL Server still doesn't like doing this with psqlODBC,
> > as others on the net have found it also doesn't work with Oracle.
>
> So does that mean then it will never work in this 'native'
> mode with MS-SQL
> and Oracle?

From what I've read, no it doesn't work with the Oracle ODBC driver
(when using SERVER..schema.table syntax) - the advice I've seen is to
use the Oracle OLE-DB driver instead. You could of course try that with
PostgreSQL.

> >> Would it be strange to suggest that we use a 'dummy'
> catalog in the
> >> ODBC driver for MS-SQL?
>
> > That won't work because the driver would then report the servers
> capabilities
> > incorrectly to applications which may then generate invalid queries.
>
> But the driver could ignore the catalog and rewrite those
> commands, could't
> it?

PostgreSQL query syntax can be very complex. The driver only makes
limited attempts to parse queries in some circumstances. It certainly
doesn't have a complete enough parser to start rewriting identifiers,
nor is it ever likely to have.

Regards, Dave.