Macro to run to multiple database connections

[NBlancorodriguez]

Hello,

I'm trying to create a workflow that can run a query for 82 different database connections, the query is the same for all of them but it needs to be dynamic depending on the fields entered by the users. Do you have any idea how can I do this using a macro?

[rzdodson]

What you can do is set up a Text Input tool that has two columns: 1) your query title, and 2) the query associated with the title. From here, you can create a batch macro that will change the query that is passing  through it. Then, once the batch macro completes, it will union the results together.

If you have a couple of queries that can be used - really just dummy data here - this workflow should be pretty easy to set up for you. :)

Keep in mind though, depending on how complex these queries are, your workflow may experience significant performance issues.

[NBlancorodriguez]

Do you have an example? I need to perform this through multiple databases which means that are different connections.

[apathetichell]

Are the connections set up? how do you authenticate? Before asking for an example - can you provide more information?

[NBlancorodriguez]

These are IBM i Access ODBC Driver Connections each data source will need to be changed for 82 connections.

[apathetichell]

I'd use dynamic input in-db in a batch macro if these are set up for in-db. If you are using dynamic input - put that in a batch macro. use an action tool to update your db name. no matter what - you're using a batch macro.

[NBlancorodriguez]

@apathetichell Do you have an example of what could be a solution?

[apathetichell · Accepted solution]

it's literally a text input tool and a dynamic input in-db with a control parameter and an action tool hooked up into the text input tool. That's it.

A workflow would not do anything for you because my databases are different than your databases. My tables are different than your tables. and I do not use dynamic input for queries - I only use dynamic input in-db.

[diegosantos]

 How can i pass the connection to macro?

Any idea?