Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ariel_klien
Specialist
Specialist

Connection string MySQL

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.

1 Solution

Accepted Solutions
ariel_klien
Specialist
Specialist
Author

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

View solution in original post

13 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

petter
Partner - Champion III
Partner - Champion III

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

........

........

........

ariel_klien
Specialist
Specialist
Author

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.

BR

Ariel

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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');

NEXT

LET DBName =;

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

Peter

Peter_Cammaert
Partner - Champion III
Partner - Champion III

OK, all clear. See below.

ariel_klien
Specialist
Specialist
Author

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

ariel_klien
Specialist
Specialist
Author

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

petter
Partner - Champion III
Partner - Champion III

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?

petter
Partner - Champion III
Partner - Champion III

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