Debugging Help Excel on Macos

[idontgetoutmuch]

This is a question which folks might be able to help on. I apologise for raising it as an issue and if there is a better place to ask this question then please direct me there.

I have built iODBC Adminstrator on macos (11.5.1) and it works on my DSN

Here is the DSN

[Laetitia]
Driver      = /Library/ODBC/psqlodbc/lib/psqlodbcw.so
Description = Laetitia from slack
Servername  = 127.0.0.1
Port        = 5432
Database    = oildb
Username    = dom
Password    = xxx

I can use the supplied test program to query the database

dom@MacBook-Pro iODBC % /Library/Application\ Support/iODBC/bin/iodbctest "DSN=Laetitia"
/Library/Application\ Support/iODBC/bin/iodbctest "DSN=Laetitia"
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.1521.0705
Driver: 13.01.0000 (psqlodbcw.so)

SQL>select count(*) from production_xxxxxx;
select count(*) from production_xxxxxx;

count               
--------------------
542802              

 result set 1 returned 1 rows.


SQL>
dom@MacBook-Pro iODBC % 

If I test the DSN from excel I get

But if I try to use it via Excel I get

and clicking on run does nothing.

I have tracing turned on and for Excel I see lots of messages like this

Microsoft Excel 11B59BE00 EXIT  SQLAllocHandle with return code -2 (SQL_INVALID_HANDLE)

I have raised this with a Microsoft forum but they suggest it is my driver but afaics iodbctest proves that this is working.

Does anyone have any ideas or suggestions about how I might make progress?

[idontgetoutmuch]

#29 provides fascinating reading.

[HughWilliams]

It would appear your PostgreSQL Native ODBC Driver (psqlodbcw.so) is successfully connecting to the database with MS Query, which is then probably making ODBC metadata calls to obtain the table list to populate the left frame and is failing to do so with the Microsoft Excel 11B59BE00 EXIT SQLAllocHandle with return code -2 (SQL_INVALID_HANDLE) errors indicated, and looks like a problem with the psqlodbcw.so driver which PostgreSQL should look into.

Have you actually made an ODBC trace, to see what MS Query is doing and which ODBC call is resulting in those errors ?

[idontgetoutmuch]

Hi @HughWilliams thanks for this - as an experiment, I installed mysql and I can connect to that

But with postgres I get this

Here's the full log file:

odbctrace-dom-69846-20210827-124726.log

[TallTed]

@idontgetoutmuch -- Testing against a different DBMS, through a different driver, with entirely different DSN attributes, is not usually very helpful, as there are so many variables in play. That said, this test does suggest that the issue is probably not in any of the common components -- those being, Microsoft Excel, Microsoft Query, and the iODBC driver manager.

Better would be to try using a different driver against the original DBMS, i.e., PostgreSQL, such as one of our own, which you can download immediately, along with a free 30-day trial license.

If this test succeeds, you can decide whether it makes more sense for you to spend unknown time, energy, and potentially money, working and waiting for a fix to the driver that currently fails; or to spend a little money and far less time or energy buying a license for the driver that currently succeeds. Some special offers are currently available for licenses for the driver I linked above.

[TallTed]

@idontgetoutmuch -- I also noticed that you said you built iODBC Administrator locally.

While this should be fine, and your reported issue doesn't appear to be with the iODBC components, I wonder why you chose not to install the pre-built iODBC binaries that ship as part of the 3.52.15 Runtime & SDK for macOS?

(Worth noting — the iODBC Runtime & SDK is also installed by default with all ODBC Drivers for macOS from OpenLink Software.)