Query database without gateway database connection

General Discussion
, , , ,
1

Hi everyone,

Is it possible to query a database without setup a gateway connection beforehand?

I'm going to read a databases which names I don't know, so these names will be a variable.

I see it's possible to create a connection on the go but I'm not sure it's the best approach.
https://docs.inductiveautomation.com/display/DOC81/system.db.addDatasource

If they way is to add all the connections, is there any limit on the database connections? I estimate 40 databases

2

No, you'll need the connections set up.

40 isn't too many, but if you're able to estimate that, it's unclear to me why you can't just set these up in advanced instead of using system.db.addDatasource. Where are you going to get all the information you need to make the connection and what part of the workflow would that happen in?

3

Is there a number that might seem to many? I'm new to 40 connections.

As I understand, there is a pattern that depends on the date; a new database per month.
There are two approaches here:

  1. Keep adding databases ever month. Meaning that 40 is just the current number and that it will continue to grow.

  2. Dump their "legacy" way of doing things. That would keep it a 40 and I will have a fixed array of names to add as connections in the gateway. I find system.db.addDatasource handy in this scenario.

  1. Case #1; Before attempting to read I should make sure the connection is established. Hopefully the names do follow a pattern and no surprises are waiting for me. It can be a scheduled event in the gateway or a component input if there are surprises.
  2. Case #2: it just have to be setup once. I hope for this case.
4

Sorry, but that is just dumb. Someone needs to be retrained or fired. IMNSHO.

5 Likes
5

A new Database?! not a table?

1 Like
6

Yea, when I read that, I thought he might be talking about tables, not databases.

7

Maybe it's a new SQL Express per month ... :see_no_evil:

5 Likes
8

I was as surprised as everyone else here. My question was "why??" but I got no answer at that time.

Yeap, new database...

I wish.

And... that was finally the answer. After my initial shock, I quit complaining and now I'm just moving with the flow.

--
So I need a completely new connection to each database to display the data. What can/might be the upper limit regarding database connections? That sole server has 40 databases and I have a few databases for Ignition (historian, alarms, audit).

Would you advice to do it manually? Some security concerns? something else?

9

We don't impose one. I suppose you could be the pioneer who finds it.

Presumably there are some credentials required for this connection, which would now be embedded in your script...

10

Meaning no software limitations. I would also assume that hardware will be fine.

Yeap, totally exposed. I was also looking for a way to hide/secure scripts in the gateway but that would be another topic.