Selecting multiple datasets, returning output parameters and returns with stored procedures

Ignition
1

Hi everyone,

I am in the process of moving all of a customer’s OEE calculation’s from the front end to their MSSQL database. I am writing stored procedures to get all the data and return the calculated values along with a list of all products made and their respective information. My thought was to create output parameters for the calculated values they care about (OEE precentages, total uptime, total scrap, etc.) and my SELECT would be all the data making up those calculated values.

For instance, let’s use the Quality OEE percentage. The customer would like to see the following metrics on a graphic: total scrap yards, total good yards, quality percentage. They also want a table of all of the created and scrapped product, timestamp, order num, etc of the data making up the quality percentage. This graphic will also have datetime range and other filters for granularity. How would I be able to have this all in one stored procedure to feed ignition and then bind the output parameters and data set to the table? Can this only be done with python scripting and not sql binding? And if I want to do sql binding would I need to break this up into two different procedures?

Thanks

1 Like
2

The stored procedures you defined in your database can be executed from sql query bindings by following the steps in the documentation here.

Another approach can be to use an actionPerfomred script or property change script on the table. Here you can query the data from the
database using system functions. Then you can do any transformation on this data before displaying it on the table component. This system function can be found here.

3

It is not possible to receive multiple datasets in Ignition from a single stored procedure so you would have to make a stored procedure for each dataset. But there is a way around this…

In MSSQL, in a stored procedure I would convert each dataset into a JSON object and then return the JSON objects as a single dataset. Below is a sample SQL script of how I accomplish this in a stored procedure. Ignore the fact that I am using the same table three times in a row.

DECLARE @jsonDataset1 NVARCHAR(MAX);
DECLARE @jsonDataset2 NVARCHAR(MAX);
DECLARE @jsonDataset3 NVARCHAR(MAX);

-- dataset 1
SET @jsonDataset1 = (
        SELECT TOP 10 l.[Message]
            , l.Source
        FROM log.SystemLog l
        FOR json path
        )
-- dataset 2
SET @jsonDataset2 = (
        SELECT TOP 5 l.[Message]
            , l.Source
            , l.[TimeStamp]
        FROM log.SystemLog l
        FOR json path
        )
-- dataset 3
SET @jsonDataset3 = (
        SELECT TOP 30 l.[Message]
            , l.Source
            , l.[TimeStamp]
        FROM log.SystemLog l
        FOR json path
        )

--output json dataset
SELECT @jsonDataset1 AS [Data1]
    , @jsonDataset2 AS [Data2]
    , @jsonDataset3 AS [Data3]

Note, I define a variable for each JSON object as type nvarchar(max). If you do not do this the JSON object will be truncated by the MSSQL JDBC driver. I learned this the hard way.

Then on the Ignition side I have a named query to call the stored procedure.

image

Then in scripting I called the named query and split out the JSON objects from the dataset.

data = system.dataset.toPyDataSet(system.db.runNamedQuery("Data/GetData",{}))

#dataset 1
dataSet1 = system.util.jsonDecode(data[0][0])
#dataset 2
dataSet2 = system.util.jsonDecode(data[0][1])
#dataset 3
dataSet3 = system.util.jsonDecode(data[0][2])

Now process the 3 JSON objects that represent the 3 datasets from MSSQL. :smiley:

1 Like