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.

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.
Conn_String_OLEDB_MySQL_QlikView1

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

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);”];

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.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on whatsapp
WhatsApp