Connect to MySQL via ADO

rsaritzky

Hi,

I’m trying to connect from NAV2009 to a MySQL database. I’ve installed the MySQL ODBC driver and also the MySQL Connector/NET driver.

The ODBC connection works successfully when I use the following connection string (assigned to a variable of type 'Microsoft ActiveX Data Objects 2.8 Library'.Connection):

ADOConnection.ConnectionString('Driver={MySQL ODBC 5.1 Driver}; server=127.0.0.1;PORT=3306;database=testdb;User=navread;Password=navreadpwd');

However, I’d like to connect via Connector/Net. I’ve tried the following connection string:

ADOConnection.ConnectionString('Provider=MySQLProv;Data Source=localhost;Initial Catalog=testdb;User ID=navread;Password=navreadpwd;');

However, I get an error from NAV when this is run:

---

Microsoft Dynamics NAV Classic

---

This message is for C/AL programmers: The call to member Open failed. ADODB.Connection returned the following message: Provider cannot be found. It may not be properly installed.


I've verified that MySQL Connector/NET has been installed. All the examples indicate that the Provider Name is supposed to be "MySQLProv", but I don't know if there's a way to verify that. I'm using MySQL Connector/Net 6.4

Has anyone done this and been successful?

Thanks

Ron

anilkumar

Hi,

Have you installed MDAC latest version on machine?

rsaritzky

anilkumar wrote:

Have you installed MDAC latest version on machine?

The version of MDAC is 2.8.1132, which I'm reasonably certain is 2.8 SP1, the latest version of MDAC. I didn't think that MDAC has changed in the last few years - the Microsoft Download site says that MDAC 2.8 was released in 2005, so I'm assuming that the current install is the latest. But I will try to verify that 2.8 SP1 is 2.8.1132.

Ron

jlandeen

I've had problems with providing NAV access to MySQL with ADO in the past. One of the problems I experienced is that when you make a connection to MySQL it's all wrapped up in the transaction that NAV has started and the MySQL ADO implementation does not fullly support distributed transactions. Because NAV has started the transaction MDAC and the ADO connection are trying to keep everything flowing within the context of the transaction that was started within NAV and an error is raised.

Just something to watch out for.

rsaritzky

Thanks for the advice. The process flow for me is pretty simple - do a SELECT and dump the data into a NAV table, so I'm not too concerned - just how to construct the connection string to attach to the database...

DigiTecKid

Hey Ron,

Any luck with that error?

"Provider cannot be found. It may not be properly installed."

I'm getting the same thing...

rsaritzky

DigiTecKid wrote:

Hey Ron,
Any luck with that error?
"Provider cannot be found. It may not be properly installed."
I'm getting the same thing...

I ended up going with the ODBC connector - it's been working fine for more than a year.

However, if you want to try some other strings, here's some from a great website I found: http://www.connectionstrings.com - it has sample connection strings with dozens of optional parameters. If you want to get into some esoteric combinations, try that website - it has strings for MySQL, SQLServer, Oracle, Sybase, DB2 and others.


Standard
Server=testserver;Database=dbtest;Uid=userid;Pwd=userpwd;

Specifying TCP port
Server=testserver;Port=3306;Database=dbtest;Uid=userid;Pwd=userpwd;


Good luck!

Ron