What is MySQL ODBC Connector? Guide for Data Integration

Developed by Microsoft in the early 1990s, ODBC stands for Open Database Connectivity or more commonly referred to as Open Database Connector. It’s essentially an Application Programming Interface (API) for accessing Database Systems. Almost all the large Database Systems such as Oracle, Microsoft SQL Server, and MySQL, will have the ODBC and it will allow you to connect a variety of applications to a given Database. This article will help you set up MySQL ODBC Connector.

MySQL is one of the most widely used open-source Database Management Systems in the world. MySQL is a Relational Database Management System and it runs on a Client-Server model. You can use the MySQL ODBC Connector to connect a whole variety of different products and third-party applications such as R, Python, MS Excel, SPSS, MS Access, etc. Connector/ODBC works on all platforms supported by MySQL such as Windows, Unix-like OS, and macOS.

What is MySQL?

MySQL is one of the most popular Relational Database Management Systems used for managing Relational Databases. MySQL uses the Structured Query Language (SQL) to define, update, and query the Database. SQL is the most widely used language for accessing and managing records in any Database. Supported by Oracle, MySQL is an open-source and free Database software under the GNU license.

Key Features of MySQL

Below are the reasons mentioned for the immense popularity of MySQL.

• MySQL is an open-source Relational Database, which means it is completely free to use.
• MySQL is based on a well-known and most widely used SQL language. It lets you execute queries on Tables, Rows, Columns, and Indexes.
• MySQL stores data in a collection of Rows and Columns called Tables, also known as Relations.
• MySQL works well even with a large data set and can support up to 50 million rows or more in a table.
• MySQL is highly customizable and the open-source GPL license allows the SQL software to be modified easily as per the needs of programmers.

What is MYSQL ODBC Connector?

The ODBC, also known as Open DataBase Connectivity, was developed by Microsoft in the early 1990s. Every Database provides its setup of ODBC drivers to connect Windows or Linux applications to the Database. Similarly, the ODBC Connector is provided by Microsoft to connect different applications to MySQL Database Server.

The MySQL Connector/ODBC is the name for the family of MySQL ODBC drivers provided by Microsoft to access a MySQL Database using the standard ODBC API. MySQL Connector/ODBC provides both driver-manager-based and native interfaces to connect to the MySQL Database. MySQL Connector/ODBC is a universal cross-platform solution supported by all platforms like Windows, Unix, and macOS. However, all new OS versions are tracked continually by developers to ensure compatibility with MySQL and ODBC.

To get started, one needs to install the MySQL Connector/ODBC on the Windows platform. You don’t need to install MySQL ODBC Connector for Unix and macOS, you can use the native MySQL network port to connect to your MySQL Database.

ODBC/Connector Installation on Windows

Connector/ODBC is a cross-platform solution that works on any platform. It runs on all of MySQL’s supported platforms, including Windows, Unix-like OSes, and Mac OS X. The developers keep track of all new OS versions to ensure that MySQL, in general, and ODBC, in particular, will run on them.

You can view, install, update, configure, and remove MySQL products using the MySQL community installer. You can also use the MySQL Community installer to install ODBC drivers.

Open the installer once the process is finished. The list of MySQL software installed on the computer appears on the first screen.

Follow the steps below:

• Step 1: Click Add to install the ODBC driver.

• Step 2: You’ll find a list of MySQL products in various categories on the new Select Products and Features screen. Under the MySQL Connectors category, you’ll find ODBC drivers. Expand MySQL Connectors > Expand Connector/ODBC

• Step 3: ODBC drivers are available in two versions. You’d like to update your driver. Expand Connector/ODBC 8.0 and select the most recent ODBC driver version. Toggle the arrow sign.

• Step 4: The ODBC driver will be moved from Available Products to Installed Products. Next should be selected.

• Step 5: You can see the list of MySQL products we’re installing on the Installation screen. To begin, press the Execute button.

• Step 6: The installation process begins. Wait for it to finish before moving on to the next step.

The ODBC driver is now visible in the DSN list.

What are the Methods for Connecting to a MySQL Database?

This article delves into two of the best ways to connect to a MySQL Database. Below are the two methods.

• Method 1: Connecting to a MySQL Database with Connector/ODBC
• Method 2: Connecting to a MySQL Database with Hevo

Method 1: Connecting to a MySQL Database with MySQL Connector/ODBC

You can follow these steps to manually connect to a MySQL Database through Connector/ODBC.

• Step 1: Installing MySQL Connector/ODBC
• Step 2: Configuring MySQL Connector/ ODBC Connection Parameters
• Step 3: Connecting to a MySQL Database

Step 1: Installing MySQL Connector/ODBC

MySQL ODBC Connector is automatically installed with MySQL Server. You can download the MySQL Installer from MySQL Community Downloads. From the list of downloads, select the installer file preferred for your Windows version (32-bit or 64-bit) to start downloading.

• Once the download is finished, locate and double-click the file you downloaded. 
• Upon opening the installer, you can choose the drivers you want to install from the “Select Products and Features” tab. You can find ODBC drivers under the “MySQL Connectors” category. 
• Expand the “Connector/ODBC” tab and click on “Connector/ODBC 8.0”.

• Now, choose the latest version of the MySQL ODBC driver to be installed.
• Click on “Next”. On the next screen, you can see the list of MySQL products you’re going to install. 
• Click on the “Execute” button to proceed with the installation.

• Click on “Next” once the installation is finished.

If you already have MySQL installed on your computer, you can download the MySQL Connector/ODBC from MySQL Community Downloads. 

• Open the installer after downloading the Connector/ODBC driver from the list. You’ll see the list of MySQL software already installed on your computer on the initial screen.
• To install the MySQL Connector/ODBC driver, click on the “Add” button and select the latest version of the ODBC driver from the “Select Products and Features” tab as instructed earlier in this section. 

• Proceed and complete the installation process.

Now, you can see the installed MySQL ODBC driver in the list of DSN.

Step 2: Configuring MySQL Connector/ ODBC Connection Parameters

After installing the MySQL ODBC Connector, you need to configure the ODBC Data Source for MySQL. ODBC Data Source (64-bit) is used to install and manage the ODBC drivers of various Databases in Windows. To configure the Data Source, follow the below-mentioned steps.

• From your system, go to Control Panel → Administrative Tools → ODBC Data Source (64-bit).
• An ODBC Data Source Administrator (64-bit) dialog box will open. Click on the “System DSN” tab to create a new System DSN.

• Under the System DSN tab, click on the “Add” button. This will open a “Create New Data Source” dialog box.
• Here, you can see the list of all ODBC drivers installed on the computer. Select “MySQL ODBC 8.0 ANSI Driver” from the list and click on “Finish”.

• Now, the MySQL Connector/ODBC Data Source Configuration Wizard will open. Specify the following connection parameters:
  • Data Source Name: Enter the desired name of the Data Source.
  • Description: Provide a brief description of the Data Source.
  • TCP/IP Server: If you’re using the TCP/IP Protocol to connect to MySQL, provide the Hostname or IP address along with the Port Number of the server where MySQL Database is installed.
  • Named Pipe: If you’re using the Named Pipe Protocol to connect to MySQL, specify the appropriate name in the text box.
  • User: Provide the Username to connect to the MySQL Server.
  • Password: Enter the Password for the specified User to connect to the MySQL Server.
  • Database: From the drop-down, select the desired MySQL Database that you want to connect.

• After specifying all the parameters, you can check the connectivity to the MySQL Database by clicking on the “Test” button.

• Click “OK” to save the MySQL ODBC Connection parameters, and close the configuration dialog box.

If the configured parameters are correct, MySQL ODBC Connection will be established successfully. You’ll now see the configured ODBC Data Source under the System DSN tab.

Step 3: Connecting to a MySQL Database

You can now access a Database on a Linux or Unix host from a Windows machine through Connector/ODBC. For the purpose of this demonstration, let’s assume that you are connecting to system ALPHA from system BETA. The Username and Password combination is assumed to be “myuser” and “mypassword” respectively. To set up access between the two systems, follow the below-mentioned steps.

• Start the MySQL Server on system ALPHA.
• Use GRANT to set up an account with a Username of “myuser” and Password of “mypassword” to connect from system BETA to the Database TEST on system ALPHA.

• Configure a Connector/ODBC DSN on system BETA with the following parameters.
  • DSN: Provide the desired name to identify the connection.
  • Server: Enter ALPHA as the address of the remote server.
  • Database: Enter TEST as the name of the default Database.
  • User: Enter “myuser” as the Username configured to access the database.
  • Password: Enter “mypassword” as the password specified for “myuser”.
• You can now connect to the MySQL Database using the DSN created through any ODBC-capable application such as Microsoft Office.

Method 2: Connecting to a MySQL Database with Hevo

Hevo helps you directly transfer data from various sources to MySQL Database, Business Intelligence tools, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

The following steps can be implemented to connect to a MySQL Database using Hevo:

• Configure Source: Connect Hevo Data with a source of your choice by providing a unique name for your Pipeline, along with details about your Data Source.

• Integrate Data: Establish a connection to MySQL by providing information about your MySQL Database and its credentials such as Database Name, Username, and Password, along with information about Port Number associated with your MySQL Database.

Here are more reasons to try Hevo:

• Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
• Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
• Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
• Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
• Scalable Infrastructure: Hevo has in-built integrations for 100’s of sources that can help you scale your data infrastructure as required.
• Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within Data Pipelines.
• Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Resolutions for Connector/ODBC Errors

Error 10061 (Cannot connect to server):

I get error 10061 when connecting or using the Test button in ODBC Data Source Administrator (Cannot connect to server).

A variety of issues can cause this error, including server problems, network problems, and firewall and port blocking issues.

“Transactions are not enabled” Error:

When using transactions, the following error is displayed: “Transactions are not enabled.”

This error means you’re attempting to use transactions on a MySQL table that doesn’t support them. When using the “InnoDB” database engine, which is the default storage engine in MySQL 5.5 and higher, transactions are supported. You can also use the BDB engine in MySQL versions prior to 5.1.

• Before continuing, double-check the following: Make sure the tables you’re working on are using a transactional database engine.

• Make sure your DSN doesn’t have the “disable transactions” option enabled.

• Check to see if your MySQL server has a transactional database engine installed. To see a list of available engine types, type “SHOW ENGINES.”

#DELETED# Records Reported by Access

When inserting or updating records in linked tables, Access marks records as #DELETED#.

If the inserted or updated records appear in Access as #DELETED#, then:

• If you’re using Access 2000, go to http://support.microsoft.com/kb/110093 and download and install the latest Microsoft MDAC (Microsoft Data Access Components) (version 2.6 or higher). This fixes a bug in Access where the table and column names aren’t specified when exporting data to MySQL.

• Additionally, download and install Microsoft Jet 4.0 Service Pack 5 (SP5) from http://support.microsoft.com/default.aspx?scid=kb;EN-US;q239114. This corrects some cases in Access where columns are marked as #DELETED#.

• Enable the Connector/ODBC Return matching rows option in all versions of Access. Enable the Simulate ODBC 1.0 option for Access 2.0.

• In all tables that you want to be able to update, include a TIMESTAMP.

• In the table, include a primary key. If this is not the case, new or updated rows may appear as #DELETED#.

• Only use DOUBLE float fields in your calculations. When comparing single-precision floats, access fails. The most common symptom is that new or updated rows appear as #DELETED# or that you are unable to locate or update rows.

• When connecting to a table with a BIGINT column with Connector/ODBC, the results are displayed as #DELETED. The workaround is as follows:
  • Create a new dummy column with the data type TIMESTAMP.
  • In the connection dialogue of ODBC DSN Administrator, select the Change BIGINT columns to INT option.
  • Delete and recreate the table link in Access.
  • Old records remain #DELETED#, but newly added/updated records are properly displayed.

Write Conflicts or Row Location Errors

Select the Return Matching Rows option in the DSN configuration dialog, or specify OPTION=2 as the connection parameter, if you see the following errors:

Write Conflict. Another user has changed your data.

Row cannot be located for updating. Some values may have been changed
since it was last read.

Importing from Access 97

A Syntax Error is reported when exporting data from Access 97 to MySQL.

This error only occurs in Access 97 and Connector/ODBC versions prior to 3.51.02. To fix this problem, update the Connector/ODBC driver to the most recent version.

Importing from Microsoft DTS

A Syntax Error is reported when exporting data from Microsoft DTS to MySQL.

This error only occurs when MySQL tables with the TEXT or VARCHAR data types are used. Upgrade your Connector/ODBC driver to version 3.51.02 or higher to fix this error.

SQL_NO_DATA Exception from ODBC.NET

When using ODBC.NET with Connector/ODBC to fetch an empty string (0 length), the SQL NO DATA exception is thrown.

The patch to fix this problem is available at http://support.microsoft.com/default.aspx?scid=kb;EN-US;q319243.

Error with SELECT COUNT(*)

SELECT COUNT(*) FROM tbl name returns an error in Visual Basic and ASP.

This error occurs because the COUNT(*) expression returns a BIGINT, and ADO is unable to handle such a large number. Choose the option to convert BIGINT columns to INT (option value 16384).

Multiple-Step Operation Error

The Multiple-step operation produced errors when using the ADO methods AppendChunk() and GetChunk(). If an error is returned, check each status value.

When the cursor location is set to adUseServer, the ADO GetChunk() and AppendChunk() methods do not work as expected. Using adUseClient, on the other hand, you can avoid this error.

Modified Record Error

Returns on access While editing records on a Linked Table, another user modified the record that you modified.

Most of the time, this can be resolved by doing one of the following:

• If the table doesn’t already have one, create one.
• If a timestamp column isn’t already present, create one.
• Only use float fields with double precision. When comparing single-precision floats, some programmes may fail.

If none of the above strategies work, create a log file from the ODBC manager (the log you get when you request logs from ODBCADMIN) and a Connector/ODBC log to help you figure out why things aren’t working.

S1T00 Error

The error S1T00 is returned when calling SQLTables, but I can’t find it in the list of Connector/ODBC error numbers.

The S1T00 error indicates that the ODBC system has experienced a general timeout and is not a MySQL error. It usually means that your connection has become stale, that the server is too busy to accept your request, or that the server has disappeared.

“Table does not exist” Error in Access 2000

You may receive errors about tables not existing when linking to tables in Access 2000 and generating links to tables programmatically rather than through the table designer interface.

This problem is caused by a known issue with a specific version of the msjet40.dll. 4.0.9025.0 is the version that is affected. You can create the links by reverting to an earlier version. If you’ve recently updated your version, look for the older version of the file in your WINDOWS directory and copy it to the drivers directory.

Batched Statements

When I try to use batched statements, the batched statements fail to execute.

Support for batch statements was added in 3.51.18. By default, batched statements are not supported. Allow multiple statements in a GUI configuration by enabling option FLAG MULTI STATEMENTS, value 67108864, or selecting the Allow multiple statements flag. MySQL does not support batch statements that use prepared statements.

Packet Errors with ADODB and Excel

When using ADODB and Excel to connect to a MySQL server, the application occasionally fails to communicate with the server, and the error Got an error reading communication packets appears in the error log.

This error could be related to PanteraSoft.com’s Keyboard Logger 1.1, which has been known to interfere with MySQL Connector/ODBC and MySQL network communication.

Outer Join Error

When using Connector/ODBC and outer joins to connect to a MySQL server, an error regarding the Outer Join Escape Sequence is reported.

According to the specifications at Microsoft ODBC Specs, MySQL Connector/ODBC does not correctly parse the “Outer Join Escape Sequence.” When asked for SQL OJ CAPABILITIES, Connector/ODBC currently returns a value > 0, despite the fact that the driver does not parse the outer join escape sequence.

Hebrew/CJK Characters

Using Connector/ODBC 5.1, I can correctly store extended characters in the database (Hebrew/CJK), but when I retrieve the data, the text is not formatted correctly, and I get garbled characters.

To ensure that the data returned is properly encoded when using ASP and UTF8 characters, add the following to your ASP files:

Response.CodePage = 65001
Response.CharSet = "utf-8"

Duplicate Entry in Installed Programs List

In my Installed Programs list, I have a duplicate MySQL Connector/ODBC entry that I can’t delete.

When you upgrade an existing Connector/ODBC installation rather than removing and reinstalling the updated version, this problem can occur.

Warning
To fix the problem, use any working uninstallers to remove existing installations; then may have to edit the contents of the registry. Make sure you have a backup of your registry information before attempting any editing of the registry contents.

Values Truncated to 255 Characters

My field values are truncated to 255 characters when I submit queries with parameter binding using UPDATE.

Make sure your connection’s FLAG BIG PACKETS option is enabled. The 255-character limit on bound parameters is no longer enforced.

Disabling Data-At-Execution

Remove the corresponding calls if you don’t want to use data-at-execution. Consider the following scenario:

SQLLEN ylen = SQL_LEN_DATA_AT_EXEC(10);
SQLBindCol(hstmt,2,SQL_C_BINARY, buf, 10, &ylen);

Would become:

SQLBindCol(hstmt,2,SQL_C_BINARY, buf, 10, NULL);

In the call to SQLBindCol, this example also replaced &ylen with NULL ().

NULLABLE Attribute for AUTO_INCREMENT Columns

When you call SQLColumns() for an AUTO INCREMENT table column, the result set’s NULLABLE column is always SQL NULLABLE (1).

Because MySQL reports the DEFAULT value for such a column as NULL, this is the case. It means that if you insert a NULL value into the column, the table’s auto increment counter will increment to the next integer value.

Learn more about : MS SQL to MySQL

Conclusion

This article introduced you to MySQL ODBC Connector and provided you with a step-by-step guide on how to connect to a MySQL Database through Connector/ODBC and Hevo, respectively. Connecting to a MySQL Database through Connector/ODBC requires a lot of time and resources. It is a tedious and time-consuming process, but using a data integration tool like Hevo can perform this process with no effort or time.

Hevo Data, with its strong integration with various sources and BI tools such as MySQL, allows you to not only export data from sources and load data in the destinations but also transform and enrich your data and make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools. Sign up for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

FAQs