Get Dataset From DB

Ignition
1

I need to get the top 5 items in a MS SQL database table (descending order) and looking for the best way to accomplish this in Ignition. I have a table that is collecting data for a production line and need to get the top 5 names and times for those stations. It is very likely to have less than 5 with greater than zero, so I will ignore zero values.

table:
index, int
station, nvarchar(50)
time, int

What would the best method of getting this data be, transaction group or gateway script, or something else?

Thanks!

2

It depends on what you want to do with this data.

Do you need the data in a client or gateway? What do you want to do with it?

You probably just need a database query in Python using system.db.runQuery.

result = system.db.runQuery("SELECT TOP 5 station,time from table ORDER BY time DESC")

You will need to have a database connection setup in Ignition and have the project using that connection as its default database connection.

3

This would be a gateway script. I have a table that is tracking current downtime and I need to show a chart with the top 5 downtime locations and the total time they were down, all that is included in a current status table.

4

If you need the data in a chart then you do not need the data in the gateway, you need it in a runtime client because charts are in clients. More specifically you need the data in a client window, in a chart.

Perhaps what you should do is write a database query for a SQL Query binding on the “DB Pens” property on an Easy Chart in an Ignition window. Or do the same thing on the “Data” property of a Chart or Bar Chart component.

5

I used a chart and did a query with selecting TOP into a chart

SELECT TOP(5) Station, ShiftDownTime
FROM tblStatCurState
WHERE (ShiftDownTime > 0)
ORDER BY ShiftDownTime DESC

I will modify this some more based on getting a specific area for the given screen which is assigned individually by location already.

Thanks for the help