Do you know what is MySQL? This is open source RDBMS (relationship database management system), more info for MySQL here. As QlikView / QlikSense developer we should connect to different databases to extract data. Most of the time, we would use ODBC or OLE DB connections, based on different drivers. There are some applications like Salesforce and SAP with special connectors, developed for data extract. Generally there are these 3 ways:
- OLE DB
- Specific connector for application
For MySQL database I will show you three different ways to extract data with QlikView. In this post you will see only the first two ways, using ODBC connection and OLE DB connection for ODBC driver. In my next post, I will show you how to connect to MySQL using DSN-less OLE DB connection.
1. ODBC connection
For ODBC connection to MySQL DB, you need special ODBC connector, which is available for free download on the website of MySQL.
Choose the right connector based on your OS, download and install it. It is important to pay attention which connector you select 32-bit or 64-bit.
Now you should create proper ODBC connection, using the new installed MySQL connector to your database.
Go to ODBC Data Source Administrator (search for ODBC in START menu). Select 32-bit or 64-bit, it depends what connector you just installed.
Click on “ADD” button in User DSN
Choose the new installed ODBC connector – MySQL ODBC 5.3 Unicode Driver and click “Finish”
Enter your server config settings
Click on “Test” button. If everything is fine, you should see following message:
Click on “OK”
This is everything you need to do in ODBC Data Source Administrator. Now you need to go in QlikView, in order to use this new connection. Create new QlikView document and open the Script Editor window. In tab “Data” below, under “Databases”, choose ODBC. Make sure that “Force 32 bit” is not checked, if you are using 64-bit connector or make sure it is checked, if you use 32-bit connector. In this case, we will use 64-bit connector, so it is not checked.
Now select “Connect” button. You should see a window, choose your ODBC connection. In this case it is “billionrows” and click OK.
The code appears in the script:
ODBC CONNECT TO billionrows;
This is the first way to connect to MySQL database with QlikView. Let’s see the second one.
2. OLE DB connection to MySQL via ODBC
This way to extract data is very similar to the previous one. You need to use again the ODBC Data Source Administrator to define your connection there. After that in QlikView you should use OLE DB driver to make the connection to MySQL, but using the ODBC.
In QlikView, open the Script Editor window. Down below in Data tab, under Database, select OLE DB. The checkbox for “Force 32 Bit”, should be unchecked. Click on button “Connect”.
Next step is to select proper OLE DB Provider – in this case I will select “Microsoft OLE DB Provider for ODBC Drivers” and click “Next”
In the next window, we need to select the our ODBC connection. In my case this is “billionrows”
Finally you should test your connection by clicking on “Test Connection”.
- If everything is fine, you will see the message: “Test Connection Succeeded.” Click on “OK” to the message and to the window. The connection string will be created for you:
OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;Data Source=billionrows;Extended Properties=”DSN=billionrows;”];
These were the first 2 ways to connect with QlikView to MySQL Database. In my next post we will explain how to connect to MySQL database without using ODBC config. This is so called DSN-less connection to MySQL with QlikView.