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.
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).
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.
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.
Then we press the Advanced Settings button.
Change the Prefix and Suffix to ` and press OK to close this menu and then OK again to add the data source.
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.
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.
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.
Apply the override and synchronize with the data source. You should get a notification about which fields were affected by the change.
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?
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.
I wasn't aware that a driver installation was needed for MySQL. Installed it now, and I can see the option under ADO.NET.
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.
Discovery Hub 188.8.131.52
MySQL Connector NET 8.0.16
Any guidance on how to solve this?
Update: Using CData instead works fine