Thread: Re: FUD!! ODBC will not be supported by Microsoft in the future
If you look in the pg 7.3 docs it seems to state that FETCH can fetch backwards. Does this mean that the backend needs to materialize the whole recordset but could return just the last n records to the client? If so then that would be different than the current psqlODBC state of affairs and would be a net gain as it would reduce network traffic. Any thoughts? Ben >From: Mike Mascari <mascarm@mascari.com> >To: Ben Trewern <ben_trewern@hotmail.com> >CC: pgsql-odbc@postgresql.org >Subject: Re: [ODBC] FUD!! ODBC will not be supported by Microsoft in the >future >Date: Wed, 01 Oct 2003 10:43:56 -0400 > >Ben Trewern wrote: > > > All > > > > I've been using (or trying to use) the pgODBC driver with 'Use > > Declare/Fetch' set to true and all goes well till you try to go to the > > end of a large recordset. The driver seems to load all the records > > instead of just the last 50 (or whatever). I'm using Delphi 7, BDE and > > psqlODBC version 7.03.01.08 to connect to Postgres 7.3.1. > > > > Any ideas? Could this be a BDE problem? Any one else out there using > > this configuration? > > > > Regards, > > > > Ben > >According to what I gather from the thread here: > >http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=7159b632785c579f&seekm=3E1BEFFE.88B57516%40tpf.co.jp#link13 > >PostgreSQL cannot perform a backward-scan and is forced to materialize > the result set in its entirety. So that is a backend issue >specifically, not an ODBC issue, I'm afraid. > >Mike Mascari >mascarm@mascari.com > > > > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org _________________________________________________________________ Tired of 56k? Get a FREE BT Broadband connection http://www.msn.co.uk/specials/btbroadband
Ben Trewern wrote: > I've been using (or trying to use) the pgODBC driver with 'Use > Declare/Fetch' set to true and all goes well till you try to go to > the end of a large recordset. The driver seems to load all the > records instead of just the last 50 (or whatever). I'm using Delphi > 7, BDE and psqlODBC version 7.03.01.08 to connect to Postgres 7.3.1. > > Any ideas? Could this be a BDE problem? Any one else out there > using this configuration? and > If you look in the pg 7.3 docs it seems to state that FETCH can fetch > backwards. Does this mean that the backend needs to materialize the > whole recordset but could return just the last n records to the client? > If so then that would be different than the current psqlODBC state of > affairs and would be a net gain as it would reduce network traffic. > > Any thoughts? > > Ben Well, looking at the ODBC sources shows that it is capable of using DECLARE/FETCH to move to the end the record set. PostgreSQL will still have to materialize the result set on the backend, of course. So I'd suggest performing an ODBC trace to see which ODBC API functions are being called on the client side and also turn logging on in postgresql.conf to see what the queries look like on the server. ODBC provides access to cursor operations through the use of SQLFetch(), which is a forward-only cursor and SQLFetchScroll() which is scroll-capable. Perhaps Delphi 7 is using SQLFetch() instead of SQLFetchScroll()? The ODBC trace will tell. Mike Mascari mascarm@mascari.com
Currently we can only use forward-only read-only
cursors with 'Use Declare/Fetch' option. BDE
may need scrollable (and holdable ?) cursors so
as to use the ODBC driver effectively though I'm
not sure.
Non read-only applications have had some troubles
with 'Use Declare/Fetch' option, because PostgreSQL's
cursors could not live across transactions. In 7.4 we
would have holdable cursors which could live outside
transactions, so we would be able to implement the
option properly for 7.4 servers.
I'm planning to implement static read-only cursors
with 'Use Desclre/Fetch' option.
regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/
Mike Mascari wrote:
>
> Ben Trewern wrote:
>
> > I've been using (or trying to use) the pgODBC driver with 'Use
> > Declare/Fetch' set to true and all goes well till you try to go to
> > the end of a large recordset. The driver seems to load all the
> > records instead of just the last 50 (or whatever). I'm using Delphi
> > 7, BDE and psqlODBC version 7.03.01.08 to connect to Postgres 7.3.1.
> >
> > Any ideas? Could this be a BDE problem? Any one else out there
> > using this configuration?
>
> and
>
> > If you look in the pg 7.3 docs it seems to state that FETCH can fetch
> > backwards. Does this mean that the backend needs to materialize the
> > whole recordset but could return just the last n records to the client?
> > If so then that would be different than the current psqlODBC state of
> > affairs and would be a net gain as it would reduce network traffic.
> >
> > Any thoughts?
> >
> > Ben
>
> Well, looking at the ODBC sources shows that it is capable of using
> DECLARE/FETCH to move to the end the record set. PostgreSQL will still
> have to materialize the result set on the backend, of course. So I'd
> suggest performing an ODBC trace to see which ODBC API functions are
> being called on the client side and also turn logging on in
> postgresql.conf to see what the queries look like on the server.
>
> ODBC provides access to cursor operations through the use of
> SQLFetch(), which is a forward-only cursor and SQLFetchScroll() which
> is scroll-capable. Perhaps Delphi 7 is using SQLFetch() instead of
> SQLFetchScroll()? The ODBC trace will tell.
>
> Mike Mascari
> mascarm@mascari.com
Since we're speaking on FUD, and the limitations of PgODBC under windows I thought I would add my $0.50 ... :) The SQL_ENLIST_IN_DTC directive is not supported. Having support for this feature would allow the ODBC connector to work synchronously within an MTS/COM+ transaction. I believe that the "Distributed Transaction" that "DTC" refers to is the transaction started under MTS/COM+. If THAT transaction fails, than all the open transactions are rolled back, including the database work for that particular COM+ transaction. Having said that, a PostgreSQL OLEDB component would support that implicitly (yes?) and would have the benfit of natively accessing PostgreSQL, thereby increasing the level of performance under ADO. It would also allow PostgreSQL to transcend the demise of ODBC under Windows. What're y'all's opinion of beginning new work, or continuing the unfinished work for a PgOLEDB driver? CG --- Hiroshi Inoue <Inoue@tpf.co.jp> wrote: > Currently we can only use forward-only read-only > cursors with 'Use Declare/Fetch' option. BDE > may need scrollable (and holdable ?) cursors so > as to use the ODBC driver effectively though I'm > not sure. > > Non read-only applications have had some troubles > with 'Use Declare/Fetch' option, because PostgreSQL's > cursors could not live across transactions. In 7.4 we > would have holdable cursors which could live outside > transactions, so we would be able to implement the > option properly for 7.4 servers. > > I'm planning to implement static read-only cursors > with 'Use Desclre/Fetch' option. > > regards, > Hiroshi Inoue > http://www.geocities.jp/inocchichichi/psqlodbc/ > > Mike Mascari wrote: > > > > Ben Trewern wrote: > > > > > I've been using (or trying to use) the pgODBC driver with 'Use > > > Declare/Fetch' set to true and all goes well till you try to go to > > > the end of a large recordset. The driver seems to load all the > > > records instead of just the last 50 (or whatever). I'm using Delphi > > > 7, BDE and psqlODBC version 7.03.01.08 to connect to Postgres 7.3.1. > > > > > > Any ideas? Could this be a BDE problem? Any one else out there > > > using this configuration? > > > > and > > > > > If you look in the pg 7.3 docs it seems to state that FETCH can fetch > > > backwards. Does this mean that the backend needs to materialize the > > > whole recordset but could return just the last n records to the client? > > > If so then that would be different than the current psqlODBC state of > > > affairs and would be a net gain as it would reduce network traffic. > > > > > > Any thoughts? > > > > > > Ben > > > > Well, looking at the ODBC sources shows that it is capable of using > > DECLARE/FETCH to move to the end the record set. PostgreSQL will still > > have to materialize the result set on the backend, of course. So I'd > > suggest performing an ODBC trace to see which ODBC API functions are > > being called on the client side and also turn logging on in > > postgresql.conf to see what the queries look like on the server. > > > > ODBC provides access to cursor operations through the use of > > SQLFetch(), which is a forward-only cursor and SQLFetchScroll() which > > is scroll-capable. Perhaps Delphi 7 is using SQLFetch() instead of > > SQLFetchScroll()? The ODBC trace will tell. > > > > Mike Mascari > > mascarm@mascari.com > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com
Chris Gamache wrote:
>
> Since we're speaking on FUD, and the limitations of PgODBC under
> windows I thought I would add my $0.50 ... :)
>
> The SQL_ENLIST_IN_DTC directive is not supported. Having support
> for this feature would allow the ODBC connector to work synchronously > within an MTS/COM+
> transaction. I believe that the "Distributed Transaction" that
> "DTC" refers to is the transaction started under MTS/COM+. If
> THAT transaction fails, than all
> the open transactions are rolled back, including the database
> work for that particular COM+ transaction.
>
> Having said that, a PostgreSQL OLEDB component would support
> that implicitly (yes?)
Maybe no. PostgreSQL doesn't have distributed transaction
functionality yet. However nice the interface is, it's
impossible to support it unless we tell a lie to MTS.
Recently 2-phase commit has been discussed on pgsql-hackers ML.
I don't know what kind of DTC PostgreSQL would support. If it
would support XA interface, the ODBC driver would be able to
support SQL_ENLIST_IN_DTC directive.
> and would have the benfit of natively accessing PostgreSQL,
> thereby increasing the level of performance under ADO.
Maybe yes as long as you use ADO to access PostgreSQL and
the implementation is not so bad.
> It
> would also allow PostgreSQL to transcend the demise of ODBC
> under Windows.
AFAIR what the subject means is that MS wouldn't support
MSDASQL OLEDB provider any longer.
Unfortunalety the life of the interfaces MS provides is
fairly short. How short the life of RDO was ! ADO.NET and
ADO are alike in appearance but quite different in nature.
Honestly I doubt which lives longer, ADO or ODBC.
regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/