Open Studio: how to dynamically change the server and database connection info without the commercial license?

[AxxessPhilips]

I have 500 databases to loop through and run SQL as stored procedure, I need to change the server and db for the connection before each run, is this possible in the community edition, if so how? It looked like context changing is available for connections only in the commercial license. If anyone knows please help.

Thanks!

[MS5]

Hello,

How your connection information to each database are stored?

To modify context values, you can use tcontextload (available in open studio version), or you can use global var to store connection information and modify them in each loop using tsetglobalvar component.

Best regards

[AxxessPhilips]

The connection info is in a configuration table in the MySQL database but I am open to store it in a file as well. Is there an example you can point me to on using tContextLoad and changing connection info? Really appreciate the response. Thanks!

[MS5 · Accepted solution]

Solution to chose depend on how your connection information are stored :

if it's key/value, the solution with tcontextLoad is better

if it's one record per connection, the solution with global variable is better

On my side, I've made an example that take connection information with format : (soit 1 record per connection) 

 

• tfileinput read the file that contain connection information
• tFlowtoIterate is used to iterate on each line of the file (generate one global variable for each column read from the file)
• tfixedflowinput create a record using global var generated by the component tflowtoiterate
• tsplitrow to transform each column of the record to key/value (schema needed in the tcontextload)

 

tcontextdump and tlogrow are present only to verify that context variable are loaded correctly

Then context values can be used for the connection to your different database at each record read in your file.

Another solution : usage of global variables to store connection information. As component tFlowToIterate generate a global variable for each columns read in the file, you can directly use them to connect, instead of context variable.

[AxxessPhilips]

Wow, great response, amazing, let me try this out, thanks a lot!