topic Re: Connection string MySQL in QlikView

Connection string MySQL

ariel_klien — Thu, 01 Oct 2015 11:53:44 GMT

Hello all.

I have more then 500 connections to MySQL DB.

I have all the details of the connections in a table.

Is there an option that use connection string with the table without configure ODBC for each DB?

I tried:

ConnectionString = "DRIVER={MySQL ODBC 5.2 Unicode Driver}; SERVER=10.10.10.10; Port=3306; USER=venu; PASSWORD=venu; OPTION=3;"

But it is not working.

Does anyone have an Idea

Ariel.

Re: Connection string MySQL

Peter_Cammaert — Thu, 01 Oct 2015 11:59:14 GMT

You can only keep a single DBMS connection open at any time. How do you want to select the particular database connection that should be open at a specific moment? Is it ok to use a specific variable out of a set of (apparently) 500? For example:

ODBC CONNECT TO '$(DBConnection392)';

Peter

Re: Connection string MySQL

petter — Thu, 01 Oct 2015 12:01:12 GMT

If you have a table already loaded early in your load script you could do:

ConnectionStrings:

LOAD * INLINE [

ConnId, ConnString

0 § DRIVER={MySQL ODBC 5.2 Unicode Driver}; SERVER=10.10.10.10; Port=3306; USER=venu; PASSWORD=venu; OPTION=3;

1 § DRIVER={MySQL ODBC 5.2 Unicode Driver}; SERVER=10.10.10.20; Port=3306; USER=mars; PASSWORD=april; OPTION=4;

] (delimiter is §);

Conn = Peek('ConnString', 0 , 'ConnectionStrings');

ODBC CONNECT TO [$(Conn)];

........

Conn = Peek('ConnString',1 , 'ConnectionStrings');

ODBC CONNECT TO [$(Conn)];

........

Re: Connection string MySQL

ariel_klien — Thu, 01 Oct 2015 12:11:13 GMT

Hi Peter.

Thanks for the quick answer,

But, this is not the problem.

I'm using one connection in a QVW.

Today i'm using ODBC connection.i, have more then 500 connections in a different QVW .(i have more then 500 different customer - each customer have a different DB in a different server - each one have a different QVW)

Each day i need to add another connection in the ODBC. (New Customer)

I want to make it hand-free. so when a new customer is created in the DB i will have the connection details. (there is a table in the DB contain all the customers and their connection details.

I want to make connection string that uses variables with the connection details.

Ariel

Re: Connection string MySQL

Peter_Cammaert — Thu, 01 Oct 2015 12:11:40 GMT

Imagine that your Connection String Table is called 'ConnectionStringTable' and its layout consists of a field DBName and a corresponding field DBConnectionString. Then you can simply define a huge range of variables like this:

FOR i = 0 to NoOfRows('ConnectionStringTable') - 1

LET DBName = peek('DBName', i, 'ConnectionStringTable');

LET DBConnection$(i) = peek('DBConnectionString', i, 'ConnectionStringTable');

LET DBConnection$(vDBName) = peek('DBConnectionString', i, 'ConnectionStringTable');

LET DBName =;

You now have 1000 variables at your disposal to select from, either using a sequence number or a Database name.

Peter

Re: Connection string MySQL

Peter_Cammaert — Thu, 01 Oct 2015 12:13:14 GMT

OK, all clear. See below.

Re: Connection string MySQL

ariel_klien — Thu, 01 Oct 2015 12:13:40 GMT

Hi Petter,

Thanks for the quick answer,

But this is not my problem/

The connection string doesn't work.

Can you help with that?

Ariel

Re: Connection string MySQL

ariel_klien — Thu, 01 Oct 2015 12:14:41 GMT

Hi Peter,

Thanks for the quick answer,

But this is not my problem.

The connection string doesn't work.

Can you help with that?

Ariel

Re: Connection string MySQL

petter — Thu, 01 Oct 2015 12:15:31 GMT

What do you mean by "The connection string doesn't work" ? Do you get an error message and if yes how does it look like?

Re: Connection string MySQL

petter — Thu, 01 Oct 2015 12:20:53 GMT

Actually I had some typing errors but try this instead:

ConnectionStrings:

LOAD * INLINE [

ConnId | ConnString

0 | DRIVER={MySQL ODBC 5.2 Unicode Driver}; SERVER=10.10.10.10; Port=3306; USER=venu; PASSWORD=venu; OPTION=3;

1 | DRIVER={MySQL ODBC 5.2 Unicode Driver}; SERVER=10.10.10.20; Port=3306; USER=mars; PASSWORD=april; OPTION=4;

] (delimiter is '|');

Conn = Peek('ConnString', 0 , 'ConnectionStrings');

ODBC CONNECT TO [$(Conn)];

Re: Connection string MySQL

ariel_klien — Thu, 01 Oct 2015 12:36:19 GMT

This is what i'm getting.

No matter what I put inside - it doesn't wok.

Re: Connection string MySQL

Peter_Cammaert — Thu, 01 Oct 2015 13:01:36 GMT

IMHO this has nothing to do with your connection string as such, but with failing SQL server authentication. The login prompt keeps coming back, whatever you enter in the two input fields, right?

I think you can solve this problem by using a windows account that has SQL Server access privileges by default, or by switching SQL server to mixed authentication mode (SQL Server+Windows Integrated Authentication) and using a SQL Server-defined UserID+Password.

You may want to verify this explanation with your SQL Server dba.

Re: Connection string MySQL

Peter_Cammaert — Thu, 01 Oct 2015 13:04:27 GMT

Now that I'm glancing over our latest posts again, this question came up: why does the dialog say "SQL Server login" when in reality you seem to want to connect to a MySQL database? Driver mixup?

Peter

Re: Connection string MySQL

ariel_klien — Sun, 04 Oct 2015 09:05:20 GMT

i found the solution:

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

Ariel