Обсуждение: Re: PostgreSQL / ODBC / Ms-SQL 2000

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Thomas Knoop
> Sent: 29 September 2005 17:26
> To: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] PostgreSQL / ODBC / Ms-SQL 2000
>
> >>> SELECT * FROM OPENQUERY(MYLINKEDSERVER, 'SELECT * FROM
> >>> public.mytable')
>
> >> As far I can see this tries to open the table complete,
> and fails in
> >> my case cause i have a ADSL connection and it tries to
> fetch all the
> records....
>
> > So modify the query as required...
>
> > 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')


> Let me give some background: I'm using the PostgreSQL as a
> datasource from
> an application that only connects to MS-SQL.
>
> So either I create a view in MS-SQL or I talk to a linked
> server in MS-SQL
> directly.
>
> But both don't work in the current version.
>
> - In the linked server setup i need to supply a catalog (of
> which the driver
> does not seem to support)

As I already pointed out, the correct syntax is SERVER..schema.table.
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.

> - In the view it also complains about the catalog that is not
> supplied...
>
> 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.

Regards, Dave

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)

> 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?

>> 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?

Thanks again for your thoughts on this!

Best regards,
  Thomas.