Thread: pgodbc + Excel + msquery + background refresh

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.

However, when trying to achieve the same thing using PG, somehow it
either :

1. PG/PG_ODBC doesn't parse/handle the request to do the query in the
background
2. I'm doing something wrong.

I'm partial to #1 as it works find on mssql.

Here's a sample query macro which you can stick into Excel. (alt-F11,
Module, Insert-New-Modules)


The Keyword here is "Refresh BackgroundQuery = True"

Sub macro1()

SQL = "Select * from public.tablename limit 5000"
With
ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DRIVER={PostgreSQL
Unicode};DATABASE=public;SERVER=127.0.0.1;PORT=5432;UID=pguser;PWD=pguser")),Destination:=Range("A1")) 
                .CommandText = SQL
                .Name = ""
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .Refresh BackgroundQuery:=True
                End If
            End With
End Sub

I think this is like the last hurdle for me from moving from mssql to
PG.

Thanks and hopefully, there will be someone who uses it this way.

On 10/10/07, Ow Mun Heng <Ow.Mun.Heng@wdc.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'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.

On Wed, 2007-10-10 at 10:22 -0500, Scott Marlowe wrote:
> On 10/10/07, Ow Mun Heng <Ow.Mun.Heng@wdc.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

On Wed, 2007-10-10 at 12:01 -0400, Gauthier, Dave wrote:
> > -----Original Message-----
> >From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general->owner@postgresql.org] On Behalf Of Scott Marlowe
> >Sent: Wednesday, October 10, 2007 11:23 AM
> >To: Ow Mun Heng
> >Cc: pgsql-general@postgresql.org
> >Subject: Re: [GENERAL] pgodbc + Excel + msquery + background refresh
>
> >On 10/10/07, Ow Mun Heng <Ow.Mun.Heng@wdc.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


> I have.  Wasn't that tough.  I used the ANSI ODBC Driver (psqlODBC).  In
> Excel, I embed a macro that runs a query against the DB.  I let the "New
> Database Query" (via Data -> Import External Data) tool figure out the
> connection string.  And I also use an extension called QueryEditor (I'll
> try to attach) to code the query.  The stuff MS provides with regard to
> query development is pretty crude and limited.  QueryEditor is much more
> accommodating.  So you basically let "New Database Query" make the
> connection to the DB (select anything from any table doesn't matter),
> then edit the query (change it altogether) using QueryEditor.  I use it
> to make plpgsl function calls.


Thanks for the code..It came through. But unfortunately, it suffered
from the same fate. It's not asyncrhnous. Meaning, it hangs up excel
during the query. This is an issue because , well the long and short of
it is that it Hangs up Excel. Hence it's not a solution at all.

I've sent this same email to the odbc list, but seems like either it's
very low activity or, not many people uses odbc to connect to PG (or via
excel for that matter)