Is it possible to connect to the database via dynamic settings?

[KhomsanSaejia]

Hello, I don't know if the library currently supports database connections using configuration settings, for example, not needing to create multiple nodes to connect to multiple databases, but using the ones sent in msg.payload etc.

[marko033]

Hello, any info about this functionality?

[KhomsanSaejia]

[KhomsanSaejia]

@marko033 I think that I can receive the settings via the payload as shown in this picture or it is already possible but I just don't know how to use it.

[Steve-Mcl]

Firstly, since the config is a "config node" it cannot recieve messages directly and thus cannot receive msg.payload to affect it settings.

However, as with any Node-RED text field, can use ENV VARS in the form ${MY_ENV_VAR}. See Node-RED docs: https://nodered.org/docs/user-guide/environment-variables

Additionally, the MSSQL config node specifically supports compound values like MySqlServer{{{ENV_SS_ID}}}.company.com

From the built in help:

You can use environment variables in any connection field, using mustache format.

Example: {{{environment variable name}}} or
Server: MyAwesomeSQLServer-{{{ENV_NODE}}}.company.com

[KhomsanSaejia]

If so, then I can understand that we need to create an Environment Variable. But if we want to connect to more than 100 databases, how do we do that? Because if we create separate variables, it will still be too many.

[Steve-Mcl]

Do you need to connect to all 100 databases at the same time or 1 at a time (for performing adhoc queries)?

[marko033]

I for example need connect multiple database one by one not at the same time. I thought that i will have one MSSQL Node and somehow will tell the node multiple times to which server and database should connect.

[Steve-Mcl]

At this time, this node does not support runtime DB selection though there is a draft PR: #107. It could be updated to include SERVER/PORT etc.

For now, you would need to create multiple configs up-front and use something like a SWITCH node to direct your queries accordingly.

[KhomsanSaejia]

Now I have to use this pattern, which is to create multiple nodes to support it.