Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
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
Partner

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
Partner

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
Partner

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
Partner

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
Partner

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
Partner

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