Sql query to 2 databases

Ignition
1

Hi every body.

Do you know how to do a SQL query between 2 databases?. They are stored in different database servers. I need to show the information in a bar chart.

Thanks.

2

For the regular bar-chart, I don’t think there’s a simple way to do that (setting up a SQL Query binding on the dataset only gives the option to query a single database).

If I had to do that, my first question would be “Does the data need to be updating in real-time?” If not, then I would set up a script to fire the two queries and then concatenate the data into a single dataset on window open, and maybe on a refresh button.

If the data needs to update real-time, then I would make the same script in a Client Event Script, running at the desired update-rate (remembering that there are limits to the frequency of queries that a SQL database will tolerate, so if you have a lot of clients open and the update rate is too fast, then this can get ugly). I would encapsulate the whole Client Event Script in an “IF” statement to the effect of

if system.tag.read("[System]Client/User/CurrentWindow").value == "the window name with the bar chart":

In other respects, a use-case script (probably not YOUR use-case) might look something like this:

db1Results = system.db.runQuery("SELECT MYDATA FROM MYDATABASE1");
db2Results = system.db.runQuery("SELECT MYDATA FROM MYDATABASE2");

myOutputLabels = ["List","Of","Expected","Column","Names"]

myOutputData = []

for row in db1Results:
	myRow = []
	for cell in row:
		myRow.append(cell);
	myOutputData.append(myRow);

for row in db2Results:
	myRow = []
	for cell in row:
		myRow.append(cell);
	myOutputData.append(myRow);

event.source.parent.getComponent('Bar Chart').data = system.dataset.toDataSet(myOutputLabels, myOutputData)

I wrote that without actually using it, so I might have made a mistake or two, but you get the picture.

2 Likes
3

Everything @zacslade said is excellent. I’ll only add two things:

  1. If the number of columns (and their types) exactly match, then you can use the new system.dataset.appendDataset function to make the scripting substantially easier.
  2. Depending on your RDBMS, it’s possible you can create a “link” between different databases (even from different vendors, ie: https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-2017). This is probably overkill for what you need, but may be worth exploring.
5 Likes
4

system.dataset.appendDataset looks awesome! Glad you guys added that to the toolkit :slight_smile: