topic Re: QlikView - How to connect it to MySQL with OLE DB Provider in QlikView

QlikView - How to connect it to MySQL with OLE DB Provider

Anonymous — Wed, 27 Sep 2017 19:29:22 GMT

Please, would you mind helping me?

I don't now how to connect my Qlikview application to a MySQL Database with OleDB for MySQL (not using "Microsoft OLE DB Provider for ODBC Drivers").

I've instaled the connectors (MySQL ODBC 5.3 Unicode Driver) in my computer.

However QlickView doesn't display me the OLE DB for MySQL.

It only displays the providers for Oracle Data Base and Microsoft SQLServer...

Does anybody have any idea to resolve it?

Thanks in advance

Ulises Yair Ogaz | Analista Técnico/Desarrollador JDE - SISTEMAS

Re: QlikView - How to connect it to MySQL with OLE DB Provider

Peter_Cammaert — Wed, 27 Sep 2017 21:01:29 GMT

AFAIK there is no OLE DB driver for MySQL available, only an ODBC driver.

OLE DB and ODBC are not the same thing. Installing one doesn't mean you can automatically connect to the same DBMS using the other one. In your screenshots, you appear to have a MySQL ODBC driver at your disposal, but not an OLE DB driver. That means there is no way to connect to a MySQL DBMS using OLE DB except through the Microsoft "glue" driver that puts an OLE DB Interface on top of any ODBC DSN that already exists on your platform.

Re: QlikView - How to connect it to MySQL with OLE DB Provider

Anonymous — Thu, 28 Sep 2017 12:28:57 GMT

Thanks, Peter!

I don't understand when you said: "except through the Microsoft "glue" driver that puts an OLE DB Interface on top of any ODBC DSN that already exists on your platform". Would you mind explaining me?

Do you mean to create a System DNS associated with MySQL, don't you? I've created an ODBC called "Mesa-QA"

...but this way only allows you to connect QV to MySQL with ODBC.

Sorry for the inconveniences.

Ulises Yair Ogaz | Analista Técnico/Desarrollador JDE - SISTEMAS

Re: QlikView - How to connect it to MySQL with OLE DB Provider

Peter_Cammaert — Thu, 28 Sep 2017 13:02:42 GMT

Let's go back to the start. OLE DB is an interface specification for talking to databases in a standardized way (let's call that one Language B). ODBC is another - much older - interface specification (let's call that one Language A). OLE DB has superseded ODBC for most of the DBMS products, but unfortunately not all of them. For example, AFAIK MySQL has no native OLE DB driver, so you are forced to keep talking to this DB from QlikView in Language A (ODBC) only. And indeed you seem to have an ODBC System-DSN available called Mesa-QA.

However, it appears that you don't like speaking Language A at all. Luckily there is a Microsoft driver available that simply translates all Language B talk into Language A speak and stuffs the result into a selected ODBC DSN to be sent to the DBMS. That's why I call the Microsoft OLE DB Provider for ODBC Drivers" a glue driver. It doesn't do any interfacing by itself; it just translates Language B into Language A (and back) and gives it to an existing ODBC definition. Which is a perfect option if you want your document to use OLE DB exclusively, but there is no native OLE DB driver available for your DBMS.

In short:

QlikView -> OLE DB interface -> Microsoft OLE DB Provider for ODBC Drivers -> ODBC DSN -> ODBC Driver -> network -> DBMS.

and back again. Hope this makes things clear.

Re: QlikView - How to connect it to MySQL with OLE DB Provider

Anonymous — Thu, 28 Sep 2017 13:12:01 GMT

Peter,

I've figured it out. These are the steps to connect Qlikview to MySQL with "OLEDB CONNECT TO ":

Steps to generate the ConnectionString

1. Press “Connect..” button. Then select “Microsoft OLE DB Provider for ODBC Drivers”

2. Complete Data Source Name (our System DNS ODBC), User Name, and password. Finally press “Confirm” button

3. QlikView displays the connection with OLEDB (connect it to MySQL with OLEDB provider)

OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=True;User ID=lchelpdesk_ro;Data Source=Mesa-QA;Extended Properties="DSN=Mesa-QA;UID=lchelpdesk_ro;"] (XPassword is **********************);

Best regards.

Ulises Yair Ogaz | Analista Técnico/Desarrollador JDE - SISTEMAS

Re: QlikView - How to connect it to MySQL with OLE DB Provider

Peter_Cammaert — Thu, 28 Sep 2017 15:21:15 GMT

No need to tell me that, I already knew. And yes, it is very simple once you know how it works.

I've been trying to convince you that this is the only way to go, but you said in your OP that you didn't want to use the "Microsoft OLE DB Provider for ODBC Drivers". Apparently the explanation worked out well...