Thanks @George,
I’ve been having a play with this and it does work well. It would be great to have dynamic connection parameters as an option in the UI
In my case I worked around it as follows (in case it helps others):
I have a central set record of DB credentials that a user retrieves once logged in to a central authentication DB (based on the organisation they are connected to). Each organisation has its own DB which can be self-hosted.
-
I set up a Server Connect DB connection to my central DB for the authentication.
-
I then set up a DB connection for a test DB that acts as my schema point (has the same structure as all organisation DBs - synced via a chronjob). When creating new SC queries, I use this connection to retrieve the DB schema.
-
I created a single record query to retrieve the DB connection parameters and save/linked it. This is added to all dynamic SC Server Actions before using the dynamic DB connection.
-
I then created the separate DB connection for the dynamic connection. I found the file in /dmxConnect/modules/Connections and opened it in the editor. I altered the connection string:
"connectionString": "mysql:host={{getdbcreds.dbhost}};sslverify=true;dbname={{getdbcreds.dbname}};user={{getdbcreds.dbuser}};password={{getdbcreds.dbpass}};charset=utf8"
In my case the query that retrieved the connection parameters was called ‘getdbcreds’
Now I can set up the queries/updates/inserts etc. using the test connection which then retrieves the DB schema within the UI for data picking. I then simply switch the connection to the dynamic one.
It is worth noting that if you want to use the client-side data pickers for the query, you have to switch to the test connection in SC, use the data picker and then switch it back again as the data picker is connection specific