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

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

AxxessPhilips — Fri, 15 Nov 2024 23:52:58 GMT

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!

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

MS5 — Mon, 09 Aug 2021 13:19:21 GMT

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

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

AxxessPhilips — Mon, 09 Aug 2021 13:37:20 GMT

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!

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

MS5 — Mon, 09 Aug 2021 15:47:32 GMT

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.

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

AxxessPhilips — Mon, 09 Aug 2021 16:03:54 GMT

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