Change database in SQL connection using Dynamic Input

Question

I am trying to setup a workflow so a user only needs to enter the database name in a Text Input, and this will then dynamically update the database in a SQL connection.

My Input Data Source Template parameter is connected to a database and has a query setup in it. I now just want the Dynamic Input tool to update the database parameter in the connection so I can run this query on any database the user wants.

However, I can't seem to find a way to do it. I don't want to use Modify SQL Query because the query doesn't need to change, it's the connection properties. If I use a formula tool to create a connection string and then replace the entire connection path, I get "No table chosen" as if it's replacing my query. I've tried to create the connection and query in one big string but this just returns "Unknown error". What can I do here?

(I know I could use an App or Macro for this but trying to avoid it for a specific reason)

gabrielvilella · Answer

Since you don't want to use a macro, you can have multiple containers, one for each database. Then you just need to enable the container for the database you need.

ChrisTX · Answer

Could you use two Connection files, where you could fully test each connection?  In my Text Input tool, I have one field for Connection and one field for Query.

The Connection field has only one line and looks like:

File:\My folder path\Connection_File_Name.indbc

The Query field looks like a normal SELECT:

SELECT H.HEADERID,
H.INVOICEID,
H.INVOICENUMBER

from myDB.myTable H
where (#Placeholder_Where_Clause#)

If you want to change to a different database, just change the Connection field to point to a new .INDBC file.

Community > Support > Knowledge > Designer > How To: Create an Alteryx In-DB Connection File

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Create-an-Alteryx-In-DB-Connection-File/ta-p/17574

Chris