ODBC MySQL connection

This article is going to explain how you connect to a MySQL data source through the Application Specific ODBC connector.

While the ODBC option as available, the ADO.net provider is recommended for improved performance.

Install and set up the provider

To use the odbc connection to MySQL you need to have a 64 bit ODBC provider. You can use MariaDB. Download and install it.

Open ODBC Data Sources Administrator (64-Bit) go to System DSN and press Add. Find the driver in the list and press Finish.

Give it a name and press next.

Add the server in the Server Name field, add 3306 in Port, add root in User name and its password in the Password field. When this is set up, press Test DSN. Now you can find all the databases the server contains by looking through the drop down menu.

Press next until it is done.

Add a MySQL ODBC data source.

To add it, right click on Data Sources, go to Data Sources, Application Specific ODBC and choose Add MySQL 5.1 Client data source.

In the menu choose the System DSN we just created.

Set the Escape Character to None and press OK.

Synchronize with the data source and see the fields. In this you will only get the tables related to the database you chose in the DSN setup. If you have more databases and want to connect to them as well, you need to create additional DSN providers for all of them.

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.