Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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)];
........
........
........
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
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
OK, all clear. See below.
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
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
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?
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)];