I'm currently building a suite of excel spreadsheets to run against
postgresql. Excel is a great tool, however they force you to use ms
query to bind your spreadsheets to the database. I have concluded that
ms query is garbage. It 'helps' you with your queries by parsing them
before they are sent to the database. If you try to use any features
that ms query does not understand, for example the ~* operator for text
searches, ms query will not allow you to return to the spreadsheet *if*
you use the parameterized version.

Example:
select * from i_hate_ms_query where postgresql ~* 'great'

Works fine but

select * from i_hate_ms_query where postgresql ~* [param1]

borks.

Also, complex query forms with subqueries or inline views will of course
completely blow the fuses of ms query. By the way, this problem is not
limited to postgresql, trying to cust complex to sql server will give
you similar headaches.

That being said, I discovered that by saving the spreadsheet as xml you
can edit the sql source inside the spreadsheet and do just about
anything you want with it. I understand that office 2003 has some new
ways to do this, but is there some simple thing that I am missing?

Merlin




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

--- Merlin Moncure <merlin.moncure (AT) rcsonline (DOT) com>
wrote:

Yep yep yep.

Well, don't use Excel then ;-)

You could just do the whole thing in code within
Excel, and avoid MS Query altogether. ADO is my
preferred choice for this. Parameters could be
captured by a dialog box that could run on opening the
spreadsheet. You would have to handle the laying out
of the data yourself, but there are various easy ways
to do this. Search the MS Knowledge Base for "getting
data from a database query into Excel" or some such,
and you will find articles which discuss this subject
pretty fully.

Alternatively you could try interposing MS Access,
i.e. define your queries in an Access database, and
use those queries as the datasource for the Excel
report. Note that I haven't done this myself, I just
know that it's doable, and I suspect that the tight
integration of MS Office products will allow you to
avoid using MS Query altogether. But you may find
yourself having to use pass-through queries, with the
need to code the capturing of parameters, so that may
not be a win in the end.




__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Unfortunately, that's not an option. Our clients almost universally
have MS Office installed and have semi-professional office workers that
would like to cut their own reports to our database. I'm basically
setting up example templates to show what the server can do. Why MS did
not set up sql-passthrough directly into excel completely blows my mind.
Excel 2003 Pro allows you to edit the spreadsheet properties but most of
our users are around the 2000/2002 level.

What's really surprising me is how much trouble I'm having getting
decent information on binding excel to a database. I would have thought
this a more common approach to presenting data in an office.

That's will probably work, I'll take a look. I wanted to avoid using a
coding approach to things but this be the only way.

Another good idea. Actually, I prefer this to cutting VB code into the
spreadsheet. However, I'll still run into parameterization problems in
Excel, namely when using ~* and ilike in parameterized queries.

Another tricky solution would be to redefine some operators on the
server to expand what can be done inside ms query. Maybe using
set-returning functions might also work. My queries are generally
already wrapped in views so my problems are mostly with the various
operators.

Merlin

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)