Nisarg Upadhyay
Control Panel

Configure ODBC drivers for MySQL

August 5, 2020 by

In this article, I am going to explain ODBC drivers. We will go through the process to set up the ODBC driver for MySQL and use it to create an SSRS report. First, let me talk about ODBC drivers.

The ODBC, also known as Open DataBase Connectivity, is provided by Microsoft that allows us to connect the application to the SQL Server or other database servers like MySQL, Oracle, or PostgreSQL. Every database platform provides its setup of ODBC drivers that can be used to connect Windows or Linux applications to connect to the database.

When we install the MySQL server, the ODBC driver for MySQL is installed automatically. In the installation process, you can choose the different version of MySQL from the Select Products and Features screen. I have already installed it on my computer, so it is greyed out.

Select Products and Features

You can download the driver from here.

Once the driver is installed, let us configure the ODBC data source for MySQL. The Windows ODBC data sources (64-bit) is used to install and manage the ODBC drivers of various databases. We are going to use it. Open Control Panel Open Administrative Tools Open ODBC Data source (64-bit) See the following image:

Control Panel

A dialog box ODBC Data Source Administrator dialog box opens. On the User DSN tab, you can view the pre-configured ODBC data sources. We want to create a new System DSN; hence click on System DSN tab and click on the Add… button.

System DSN

A dialog box Create New Data Source opens. In the list, you can see all see the list of various ODBC drivers that are installed on the computer. We want to set up MySQL ODBC Data source, hence choose MySQL ODBC 8.0 ANSI Driver or MySQL ODBC 8.0 Unicode Driver and click on Finish.

Create a new data source

A dialog box MySQL Connector/ODBC Data Source configuration opens. In the Data Source name and description text box, provide the desired name and the description of the Data source.

If you are using TCP/IP protocol to connect to MySQL, then click on TCP/IP Server and provide the name and the port number of MySQL Server. If you are using Named Pipe protocol, then select Named Pipe and enter the appropriate value in the text box.

Enter the username and password in the User and Password text box to authenticate to the server. Select the desired database name from Databases drop-down box. Click OK to save the ODBC connection properties and close the dialog box. See the following image:

Configure MySQL ODBC data source

On ODBC Data Source Administrator (64-bit) screen, you can see that the MySQL for SSRS has been created. Click OK to close the dialog box. See the following image:

ODBC Data source has been created

Once the ODBC Data Source is configured, let us create an SSRS report to test it. To create an SSRS report, you must download SQL Server data tools from this location. Once it is downloaded and installed, open it and click on File Hover on New Click on New Project. See the following image:

New SSDT project

A dialog box, New Project, click on Reporting Services from the left pane and click on Report Server Project Wizard. Provide the desired name in Name textbox. See the following image:

New Report server project wizard

Create a new report wizard opens. The first screen gives information about the wizard and the tasks that can be performed by it. You can review the details and click on Next. See the following image:

Welcome screen

On Select Data Source Screen, provide the desired name of the data source in the Name text box. Choose ODBC from the Type drop-down box and enter DSN=MySQL in connection string text box. Click on Next. See the following image:

Select Data source

On Design the Query screen, enter the following query. It populates the list of movies, their category, release year, rental details.

Click on Next. See the following image:

Design the Query

On Select, the Report Type screen, select the type of report. You can create a matrix report that summarizes the output of the query. We want to populate the details of the movie titles; hence choose Tabular and click Next. See the following image:

Report Wizard

On the Design the Table screen, you can select the columns that you want to display on the report. We require the following columns from the query output

  1. Movie_name
  2. Movie_category
  3. Movie_description
  4. Movie_release_year
  5. Rental_rate
  6. Rental_description
  7. Special_features

Design the Table

Select all the fields from the available fields list and click on details. All the selected fields will be moved to the Displayed field list. Click on Next. See the following image:

Move fields to displayed screen

On Completing the Wizard screen, provide the desired report name in the report name text box. Review the report details in Report Summary textbox and click on Finish. See the following image:

Reporting wizard is completed

Once the report is created, in the Design window, it looks like the following image.

Design of Report

To view it correctly, click on Preview. In Preview, it looks like the following image:

Preview of SSRS Report

You can format the report according to your requirement.

Summary

In this article, we have learned about the ODBC driver and how to configure it to connect it to the MySQL Server. I have also demonstrated the usage of the ODBC driver to create an SSRS Report to display data from MySQL Server.

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)
MySQL, Server management, Utilities

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

642 Views