Connect to MySQL using ODBC
Connecting to MySQL using ODBC
MySQL is a free and open-source software that is released under the terms of the GNU General Public License and is also made available under a variety of proprietary licenses. In computing, ODBC which is the abbreviation for Open Database Connectivity is a standard application programming interface (API) that is used for accessing database management systems (DBMS).
The designers of ODBC developed it to make it independent of any database and operating systems. An application that is developed using ODBC can be ported to other systems platforms, both on the client and server-side, with very few changes to the data access code.
ODBC MySQL connection
This article is going to show how you connect your application to a MySQL data source by means of the Application Specific ODBC connector.
While the ODBC option is still very much available, the ADO.net provider is a much-recommended one for a more improved performance.
Install and set up the provider
To use the ODBC connection to MySQL you are required to have a 64-bit ODBC provider. You can just make use of MariaDB. Download and install it on your system.
Open up the ODBC Data Sources Administrator (64-Bit) and go over to System DSN and click on Add. Locate the driver in the list and click on Finish.
Give it a name and click on the Next button.
Add the server that is in the Server Name field, enter 3306 into the Port field, enter in root in the User name field and its password in the Password field. When this is entirely set up up, click on the Test DSN button. Now you can find all the databases that the server has in it by just going through the drop-down menu.
Click on the next until it is done.
Add a MySQL ODBC data source.
To add a new MySQL ODBC data source, right-click on Data Sources, and go over to Data Sources, Application Specific ODBC and choose Add MySQL 5.1 Client data source.
In the menu that comes up, select the System DSN that we just created above.
Set the Escape Character to None and click on OK.
Synchronize that with the data source and see the fields. In this, only the tables that are related to the database that you chose in the DSN setup would be returned. If you have more databases and want to connect to them as well, you would need to create other additional DSN providers for all of them.
Common issues with MySQL data
Date fields smaller than 1753-01-01
In MySQL, the smallest date value that is allowed is 0001-01-01 in MSSQL, although, you w=should note that it is not the case for DateTime fields.
If that is the case you will be getting the following error message as output.
To fix this you can simply make use of the override data type feature: by using the Override Data Type feature.
In this case, you would have to make use of the General Rule in which DateTime fields are converted into DateTime2 fields. Remember that it distinguishes between the upper case and the lower case field entries.
Apply the override method, and synchronize it with the data source. You should get a notification about which fields would be affected by the change.
