Has anyone gotten a connection to a MySQL ODBC driver to work??

[dljacobson]

I love my PowerGadgets, but I have a lot of MySQL database applications.  Has anyone successfully gotten a connection string for the MySQL 3.51 ODBC connection to work with the PowerGadgets "ODBC" database invoke-sql provider?  If I use the correct MySQL connection string, I get a "missing provider error" from PowerGadgets.  Is there another MySQL connector that PowerGadgets will work with.  I would appreciate any help you might provide.  Best,

David

[marco.shaw]

Hmmm...  It would seem to me that MySQL.com is saying that the default .NET framework install doens't have support for MySQL connecting, but there are workarounds though to get MySQL support in .NET:

http://dev.mysql.com/tech-resources/articles/dotnet/

From there, you might have to build your own PowerShell SQL queries from scratch.

I can try to help futher if you're really stuck.  MySQL on Windows or remote on UNIX/Linux?

[dljacobson]

Hi Marco,

Thank you for responding to my post.  I'm looking for MySQL on Windows.  I'm not suprised the OLEDB wouldn't work--MySQL dropped it a long time ago.  I was a bit suprised that the relatively new ODBC Connector (.13 release) would not work with the PowerGadgets invoke-sql, especially since you all just added a generic ODBC connection option (at least into the PowerGadgets Creator). 

I did not find your article in my search (thanks for sending it); it looks pretty straight-forward, so I'll give it a whirl (still has the look of an ASP.NET 1.1 vs. 2.0 doc) and I'll post the code/procedure if it works.  Lot's of MySQL zealots out there that still love Microsoft products. 

The article offered a fix off the ODBC and the ADO connector.  Which has the best chance as a jumping off point for successfully working with PowerGadgets?  I'll try both, if need be.

Finally, just an FYI.  If you are using the PowerGadgets Creator GUI to create a map and attempt to change the colors or thresholds of the conditional values series, the designer will let you do it once and save it.  The .pgf will run repeatedly, but if you attempt to modify those or any settings subsequently you will get an "index of gadget out of limits error" and the Creator will just shut off.  (I can get you a screen capture if you want).  It's not a critical issue for me and probably not for most, because almost all maps require drill-down and, as I understand it, you can't do drilldown using the Creator. 

 Thanks again!

 David 

[JuanC]

David,

We added the "native" ODBC connection for this purpose, allowing connections not supported by OleDB. We have not tested MySql yet (we did test access to Excel files as well as connecting to MYOB). We will post here as soon as we finish our tests.

JuanC

[JuanC]

This configuration works, I include the version numbers just for reference as I am sure other versions will work as well

Server:- MySql 5.0.37

Client:- MySql ODBC Driver 3.51.14- Create a DSN using the Control Panel-Data Sources (ODBC) applet. We used a System DSN but I suspect it would work the same with a User DSN. In this DSN we specified the following: Data Source Name, Server, User, Password and Database.- Use the following connection string"odbc;dsn=mysqltest;driver=MySQL ODBC 3.51 Driver;server=mysql;database=test"Note that in the connection string we had to specify again the driver, server and database, we tried other combinations and got errors from MySql. I think the ODBC spec force you to specify the DSN and driver but we don't know why server and database are required here if they are set in the DSN configuration.

Regards,

JuanC

 

[dljacobson]

Hey Juan,

Thank you very much for replying to my post.  Did you get this to work through the PowerGadgets creator or through the Powershell invoke-sql script?  I assumed since you didn't include user name and password in the connection string that it was from the creator.  I tried that (using the creator to add user name and password) and it got close but gave me an indexing problem. 

If you were using powershell script, then I am just assuming you added a user name and password argument or no?  I tried through shell script and got a permissions error, making me presume that I needed to duplicate those arguments in the password string as well, despite the fact they live in the machine DSN configuration. 

I guess my main question is did this work for you in Creator or shell script, or both?  Then I know where to start debugging from.

Thank you very much for your help! 

 Regards,

 David 

[JuanC]

It works in both. When I use powershell, I tested it using the following command

PS C:\Temp> invoke-sql -ConnectionString "odbc;dsn=mysqltest;driver=MySQL ODBC 3.51 Driver;server=mysql;database=test" -sql "SELECT * FROM TestData" Name   Value----   -----Name1 10Test2 14

When using Creator I used the same statement in the ODBC connection string and provided NO user or password. I guess this works because I provided the credentials when I created the DSN. I also tried removing the credentials from the ODBC DSN settings and it still worked but I am not sure if this depends on the MYSQL security settings I used when setting up the server.

Regards,

JuanC

[medievalhellspawn999]

To add the user and password to this:

invoke-sql-ConnectionString "odbc;dsn=mysqltest;driver=MySQL ODBC 3.51Driver;server=mysql;database=test;user=root;pass=''" -sql "SELECT * FROM TestData"

Note for the password, I am using two single quotes '.