iODBC driver basics w/Excel 2008 and MySQL 5.x

S

suchter

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I installed the most recent iODBC driver (iodbc-sdk-3.52.7-macosx-10.5.dmg) and rebooted. I'm trying to do some simple "Get External Data" queries from a MySQL 5.x server. I've tested every component of my system and I think I've isolated that Excel itself is broken somehow.

My MySQL connection works via the "iODBC Demo" window with the user/pass I intend. I've tried running a test query in that window and it works fine. I've setup a "User DSN" and a "System DNS" (each with unique names), and both "test" properly when created. However, when I use Excel and try to "Get External Data => New Database Query" I notice two problems: first, it asks me for a user/pass every time I do this, even though I'm attempting to save the information permanently when I create the DNS and second, the query simply doesn't work.

As soon as I choose my previously created DSN I am asked to authenticate with a user/pass, which I do. I then get an immediate error from a program called "MicrosoftQuery" that says, "Could not establish connection" with a simple "ok" button. When I hit that button I get put back into Excel with an error, "Microsoft Excel did not receive any information from the Microsoft Query" and again just an "ok" button.

I run Little Snitch and I'm pretty sure Excel isn't even initiating an external connection in this case. Like I said, the drive's demo mode works fine so I suspect the problem is with Excel, not the driver itself. Perhaps I'm not setting something up right?

Here's the driver trace as well:

* Trace started on Wed Dec 09 17:09:00 2009 * Driver Manager: 03.52.0709.0909

Microsoft Query 00A605C0 ENTER SQLAllocEnv
                SQLHENV * 0xbfffe970

Microsoft Query 00A605C0 EXIT SQLAllocEnv with return code 0 (SQL_SUCCESS)
                SQLHENV * 0xbfffe970 (0x593af70)

Microsoft Query 00A605C0 ENTER SQLAllocConnect
                SQLHENV 0x593af70
                SQLHDBC * 0xbfffe96c

Microsoft Query 00A605C0 EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS)
                SQLHENV 0x593af70
                SQLHDBC * 0xbfffe96c (0x593b100)

Microsoft Query 00A605C0 ENTER SQLDriverConnect
                SQLHDBC 0x593b100
                SQLPOINTER 0x5923d10
                SQLCHAR * 0x68ab141
                SQLSMALLINT -3 (SQL_NTS)
                SQLCHAR * 0xbfffe974
                SQLSMALLINT 1024
                SQLSMALLINT * 0xbfffe968
                SQLUSMALLINT 3 (SQL_DRIVER_COMPLETE_REQUIRED)

Microsoft Query 00A605C0 ENTER SQLAllocHandle
                SQLSMALLINT 1 (SQL_HANDLE_ENV)
                SQLHANDLE 0x0 (SQL_NULL_HANDLE)
                SQLHANDLE * 0xbffe6b18

Microsoft Query 00A605C0 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
                SQLSMALLINT 1 (SQL_HANDLE_ENV)
                SQLHANDLE 0x0 (SQL_NULL_HANDLE)
                SQLHANDLE * 0xbffe6b18 (0x59622c0)

Microsoft Query 00A605C0 ENTER SQLSetEnvAttr
                SQLHENV 0x59622c0
                SQLINTEGER 200 (SQL_ATTR_ODBC_VERSION)
                SQLPOINTER 0x3
                SQLINTEGER * -5 (SQL_IS_UINTEGER)

Microsoft Query 00A605C0 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
                SQLHENV 0x59622c0
                SQLINTEGER 200 (SQL_ATTR_ODBC_VERSION)
                SQLPOINTER 0x3
                SQLINTEGER * -5 (SQL_IS_UINTEGER)

Microsoft Query 00A605C0 ENTER SQLDataSourcesW
                SQLHENV 0x59622c0
                SQLUSMALLINT 31 (SQL_FETCH_FIRST_USER)
                SQLWCHAR * 0xbffe6b50
                SQLSMALLINT 1024
                SQLSMALLINT * 0xbffe6b0a
                SQLWCHAR * 0xbffe7b50
                SQLSMALLINT 1024
                SQLSMALLINT * 0x0

Microsoft Query 00A605C0 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS)
                SQLHENV 0x59622c0
                SQLUSMALLINT 31 (SQL_FETCH_FIRST_USER)
                SQLWCHAR * 0xbffe6b50
                                  | terratrim_dbv2_03 |
                SQLSMALLINT 1024
                SQLSMALLINT * 0xbffe6b0a (17)
                SQLWCHAR * 0xbffe7b50
                                  | MySQL ODBC 5.1 Driver |
                SQLSMALLINT 1024
                SQLSMALLINT * 0x0

Microsoft Query 00A605C0 ENTER SQLDataSour
 
J

Jim Gordon Mac MVP

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor:
Intel I installed the most recent iODBC driver
(iodbc-sdk-3.52.7-macosx-10.5.dmg) and rebooted. I'm trying to do some
simple "Get External Data" queries from a MySQL 5.x server. I've tested
every component of my system and I think I've isolated that Excel itself
is broken somehow.

My MySQL connection works via the "iODBC Demo" window with the user/pass
I intend. I've tried running a test query in that window and it works
fine. I've setup a "User DSN" and a "System DNS" (each with unique
names), and both "test" properly when created. However, when I use Excel
and try to "Get External Data => New Database Query" I notice two
problems: first, it asks me for a user/pass every time I do this, even
though I'm attempting to save the information permanently when I create
the DNS and second, the query simply doesn't work.

As soon as I choose my previously created DSN I am asked to authenticate
with a user/pass, which I do. I then get an immediate error from a
program called "MicrosoftQuery" that says, "Could not establish
connection" with a simple "ok" button. When I hit that button I get put
back into Excel with an error, "Microsoft Excel did not receive any
information from the Microsoft Query" and again just an "ok" button.

I run Little Snitch and I'm pretty sure Excel isn't even initiating an
external connection in this case. Like I said, the drive's demo mode
works fine so I suspect the problem is with Excel, not the driver
itself. Perhaps I'm not setting something up right?

Here's the driver trace as well:

# * Trace started on Wed Dec 09 17:09:00 2009
# * Driver Manager: 03.52.0709.0909

Microsoft Query 00A605C0 ENTER SQLAllocEnv
SQLHENV * 0xbfffe970

Microsoft Query 00A605C0 EXIT SQLAllocEnv with return code 0 (SQL_SUCCESS)
SQLHENV * 0xbfffe970 (0x593af70)

Microsoft Query 00A605C0 ENTER SQLAllocConnect
SQLHENV 0x593af70
SQLHDBC * 0xbfffe96c

Microsoft Query 00A605C0 EXIT SQLAllocConnect with return code 0
(SQL_SUCCESS)
SQLHENV 0x593af70
SQLHDBC * 0xbfffe96c (0x593b100)

Microsoft Query 00A605C0 ENTER SQLDriverConnect
SQLHDBC 0x593b100
SQLPOINTER 0x5923d10
SQLCHAR * 0x68ab141
SQLSMALLINT -3 (SQL_NTS)
SQLCHAR * 0xbfffe974
SQLSMALLINT 1024
SQLSMALLINT * 0xbfffe968
SQLUSMALLINT 3 (SQL_DRIVER_COMPLETE_REQUIRED)

Microsoft Query 00A605C0 ENTER SQLAllocHandle
SQLSMALLINT 1 (SQL_HANDLE_ENV)
SQLHANDLE 0x0 (SQL_NULL_HANDLE)
SQLHANDLE * 0xbffe6b18

Microsoft Query 00A605C0 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 1 (SQL_HANDLE_ENV)
SQLHANDLE 0x0 (SQL_NULL_HANDLE)
SQLHANDLE * 0xbffe6b18 (0x59622c0)

Microsoft Query 00A605C0 ENTER SQLSetEnvAttr
SQLHENV 0x59622c0
SQLINTEGER 200 (SQL_ATTR_ODBC_VERSION)
SQLPOINTER 0x3
SQLINTEGER * -5 (SQL_IS_UINTEGER)

Microsoft Query 00A605C0 EXIT SQLSetEnvAttr with return code 0
(SQL_SUCCESS)
SQLHENV 0x59622c0
SQLINTEGER 200 (SQL_ATTR_ODBC_VERSION)
SQLPOINTER 0x3
SQLINTEGER * -5 (SQL_IS_UINTEGER)

Microsoft Query 00A605C0 ENTER SQLDataSourcesW
SQLHENV 0x59622c0
SQLUSMALLINT 31 (SQL_FETCH_FIRST_USER)
SQLWCHAR * 0xbffe6b50
SQLSMALLINT 1024
SQLSMALLINT * 0xbffe6b0a
SQLWCHAR * 0xbffe7b50
SQLSMALLINT 1024
SQLSMALLINT * 0x0

Microsoft Query 00A605C0 EXIT SQLDataSourcesW with return code 0
(SQL_SUCCESS)
SQLHENV 0x59622c0
SQLUSMALLINT 31 (SQL_FETCH_FIRST_USER)
SQLWCHAR * 0xbffe6b50
| terratrim_dbv2_03 |
SQLSMALLINT 1024
SQLSMALLINT * 0xbffe6b0a (17)
SQLWCHAR * 0xbffe7b50
| MySQL ODBC 5.1 Driver |
SQLSMALLINT 1024
SQLSMALLINT * 0x0

Microsoft Query 00A605C0 ENTER SQLDataSour
Click to expand...

Hi,

Try using the free demo driver from ActualTechnologies. It only gives 3
records in demo mode. If it works OK, then there's something about the
iODBC driver configuration that's amiss. If both drivers fail, then I'd
give check out the support by the driver makers. The authentication
dialogs would be coming from the ODBC driver. It sounds like Excel and
Microsoft Query are just sitting there waiting for the driver to connect.

-Jim
 
S

suchter

The ActualTechnologies driver works, however, keep in mind that the iODBC driver also works fine *in test mode* (where it opens a manual SQL window and uses the a DSN of your choice). The only problem I'm having is with Excel itself!

Perhaps this is a problem with iODBC+Excel 2008+Snow Leopard?
 
O

OpenLink_Software

Hi

The iODBC Driver Manager installer package ((iodbc-sdk-3.52.7-macosx-10.5.dmg) does not include any ODBC Drivers, thus you must have installed (or some other source) a MySQL ODBC Driver, possibly the free one from MySQL themselves (myodbc) ?

The OpenLink MySQL 5.x ODBC Driver works with Office Excel 2008 (MS Query) with the same iODBC 3.52.7 iODBC Driver Manager bundled with its installer. The OpenLink Software suite of ODBC drivers for Mac OS X Snow Leopard being what were used for testing the iODBC Driver Manager update prior to release. With Excel being one of the standard test applications.

The OpenLink MySQL 5.x Driver can be download for evaluation from:

http://tinyurl.com/yjvzmu9

With documentation on installation and configuration available from:

http://wikis.openlinksw.com/dataspace/owiki/wiki/UdaWikiWeb/InstallMySQLLiteOSX

Best Regards
Hugh Williams
Professional Services
OpenLink Software
Web: http://www.openlinksw.com
Support: http://support.openlinksw.com
Forums: http://boards.openlinksw.com/support
Twitter: http://twitter.com/OpenLink
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reverse engineering a MySQL database using MyODBC and Visio 1
Generating a data model with MyODBC and Visio 1
Excel 2004 crashes on update/insert/load query 0

Top