We have a new community! Please visit support.timextender.com
Follow

Connect to MySQL using ADO.Net

MySQL ADO.Net

This article is going to explain how you connect to a MySQL data source through the Any Source ADO connector.

While the ADO.net provider is recommended, ODBC is also available here.

Install the MySQL provider

Download a .Net mysql provider. I have used MySQL for NET. Just download the provider and install it. If you have our program opened when installing this, you need to restart it to see it.

Setting up the any source ado connection

Start by adding a new data source. Right click on Data Sources, go to Data Sources and click on Add Any Source ADO.Net.

01.PNG

You then need to choose that it is a MySQL connection. Press the Provider drop down menu and choose MySQL Data Provider (.Net Framework Data Provider for MySQL).

2.PNG

Find the Connection area. In the field Database add the database name. In the Server field you add the server name. It can be a ip address as well. I have added port 3306 in the Port field. It is not necessary in this case, as it is the expected default and my MySQL server is on localhost.

3.PNG

Find the Security area. In the User Id field add a user that has read rights on the database. I use root as it is the admin. In the Password field add that users password.

4.PNG

Then we press the Advanced Settings button.

5.PNG

Change the Prefix and Suffix to ` and press OK to close this menu and then OK again to add the data source.

6.PNG

That is it. Synchronize with the data source to see the fields.

If you want to remove some of the system fields from the list, you can set it up to only show a specific schema.

Start by going to the Advanced Data Source Properties menu. To see the existing schema's press the Table Schema button. In the Object filtering area set the Object Type to Schema Name, the Filter Type to String Equal and the Filter Value to the schema name you chose.

7.PNG

Press OK in both menus to save the settings and synchronize with the data source to remove all tables that does not exist in the schema you chose.

Common issues with MySQL data

Date fields smaller than 1753-01-01

In MySQL the smallest allowed date is 0001-01-01 in MSSQL that is not the case for datetime fields.

If that is the case you will get the following error message.

8.PNG

To fix this you can use the override data type feature: Using the Override Data Type feature.

In this case you would use the General Rule where DateTime fields are converted to DateTime2 fields. Remember that it differentiates between upper case and lower case.

9.PNG

Apply the override and synchronize with the data source. You should get a notification about which fields were affected by the change.

 

 

 

Was this article helpful?
0 out of 0 found this helpful

5 Comments

  • 0
    Avatar
    Anders T. Grønlund

    Running TX ver. 18.10.8, but doesn't see the MySQL option under ADO.net. 

    Where do I find it, or should I enable something in the back before it works?

  • 0
    Avatar
    Thomas Lind

    Hi Anders

    It should be present under ADO.Net if you have installed the mysql driver.
    If not it is an issue.

    Since I wrote this we have added another way to connect to MySQL. We can now also use CData to do so.

    The setup is very similar, except you don't need to fill out port or database, they can be left empty or as default.

     

    Edited by Thomas Lind
  • 0
    Avatar
    Anders T. Grønlund

    I wasn't aware that a driver installation was needed for MySQL. Installed it now, and I can see the option under ADO.NET.

  • 0
    Avatar
    Marius Gundersen

    I have set up my connection according to this guide, but when running Execute I get this error:

    I am able to preview tables in the source database, but loading data from the MySQL datasource fails.

    Setup:

    Discovery Hub 19.2.9.64
    MySQL 8.0
    MySQL Connector NET 8.0.16

    Any guidance on how to solve this?

     

    Update: Using CData instead works fine

    Edited by Marius Gundersen
  • 0
    Avatar
    Lorenzo Lo-Fo-Wong

    Hi, I have problems connecting to a mysql DB.
    When I use an odbc I'm able to get the objects but reading data takes ages!
    When I use Ado.Net, test connection succeeded but when I try to get the objects I don't get any. What could be the problem?

    Using CDATA somehow does not work, get an error with test connection : 
    [HY000] Connections using insecure transport are prohibited while --require_secure_transport=ON.
    Module: System.Data.CData.MySQL
    This I need to check with our system administrator.
    I hope someone can help.

Please sign in to leave a comment.