Re: pgodbc + Excel + msquery + background refresh

On Wed, 2007-10-10 at 10:22 -0500, Scott Marlowe wrote:
> On 10/10/07, Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> wrote:
> > Just wonder if anyone here uses Excel to connect to PG via ODBC.
> >
> > I'm using it extensively as my platform to get data from PG/MSSQL
> > directly into excel. (Excel uses the msqry32.exe file which is like a
> > stripped down sql query tool and returns data directly into excel)
> >
> > When using mssql, connecting from excel to mssql, I can get the query to
> > run in the background. Hence, a long running query will not interfere
> > with normal running of other excel works. Eg: Create new sheets,
> > graphing etc.
>
> Basically, MS has programmed Excel to use a cursor when it connects to
> MSSQL. The generic PGODBC connection in excel doesn't know how to do
> this apparently.

I don't think this is true.. This was logged in the PG instance.

LOG: duration: 73510.302 ms statement: BEGIN;declare "SQL_CUR00FC9B68"
cursor with hold for Select * from
foo('8/1/2007','10/1/2007','{AAA,BBB,CCC,DDD,EEE,FFF,GGZ}');fetch 100 in
"SQL_CUR00FC9B68"

Additionally, the config for psqlodbc has this

Use Declare/Fetch cursors which is set to 100 by default.

>
> I'm not sure if the problem is in pgodbc or excel. I'd tend to guess
> excel. Microsoft might be willing to work on making Excel work better
> with PostgreSQL, but I wouldn't expect it to be a priority, as they
> sell a competing product and this just makes it look better.

Hear.. hear.. but, this is where I would expect that headway be made
since Excel is (fortunately/unfortunately, take your pick) the main BI
app that is ubiquitious enough to make a dent.

http://andyonenterprisesoftware.com/2007/07/the-price-of-failure/

See point #3