QlikView DSN-less OLE DB Connection to MySQL

In this post I will show you one interesting solution to extract data from MySQL DB with QlikView.

0 Jan 2022

IT General

In this post I will show you one interesting solution to extract data from MySQL DB with QlikView. You could see my previous post for 2 other ways to extract data from MySQL DB with QlikView. I will use so called DSN-less OLE DB connection. This means that I will not set up ODBC connection, but use only a connection string. The connection string is based on 2 drivers – one ODBC driver and one OLE DB.

First lets see the whole connection string and will explain what you need to make it works:

CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False; Extended Properties=”DRIVER={MySQL ODBC 5.1 Driver}; UID=$(vUser);Server=$(vServer);Database=$(vDB);Password=$(vPass);OPTION=3;PORT=$(vPort);”];

You need OLE DB driver – Microsoft OLE DB Provider for ODBC Driver. This driver should be installed with your Windows OS.
You need ODBC driver – MySQL Connector ODBC 5.1
Both drivers 64-bit
You could set variables (vUser, vServer, vDB, vPass, vPort) or to type your database config settings within the string.

SET vServer = 11.11.11.11;
SET vPort = 3306;
SET vDB = CustomDB;
SET vUser = billionrows;
SET vPass = mypass;

As you may understand, I am using OLD DB driver, but for the direct extract I use ODBC. The advantage of this method to extract data from MySQL database with QlikView is that you don’t need to create and maintain ODBC connection. Everything is within the string.