Options

Connect to MySQL via ADO

rsaritzkyrsaritzky Member Posts: 469
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
Ron

Comments

  • Options
    anilkumaranilkumar Member Posts: 136
    Hi,

    Have you installed MDAC latest version on machine?
    Anil Kumar Korada
    Technical Consultant
  • Options
    rsaritzkyrsaritzky Member Posts: 469
    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
    Ron
  • Options
    jlandeenjlandeen Member Posts: 524
    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.
    Jeff Landeen - Sr. Consultant
    Epimatic Corp.

    http://www.epimatic.com
  • Options
    rsaritzkyrsaritzky Member Posts: 469
    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...
    Ron
  • Options
    DigiTecKidDigiTecKid Member Posts: 46
    Hey Ron,

    Any luck with that error?

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

    I'm getting the same thing...
  • Options
    rsaritzkyrsaritzky Member Posts: 469
    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
    Ron
Sign In or Register to comment.