[FEATURE] Query ignition dataset

Now I have a dataset from a querie.
Since I want to limit the amount of database transactions I don’t want to query for every table, while I already have the data in the dataset.

The best way I could think of is too made it possible to query a ignition dataset in the sql query part of a property.

Now if you want such a thing you have to make a script or a large expression.

1 Like

You can have your query return one large dataset which you could then pass into a template repeater. That would eliminate most scripting while allowing you to easily account for a changing dataset.

You might find the view() expression function useful. It uses a Pseudo-SQL query to process one dataset into another. It is part of the (free) Simulation Aids module.

Yes, this is usefull. But doesn’t contains everything I wanted.
It should really be a nice feature to query your own datasets in ignition 8 with the normal sql query option of a property.

Unfortunately, I dont even see a simple option to limit the rows to 10 or something :frowning: ,
as in SQL you can do: select top 10 productnames,price , you cant use that in the simulation aids

True enough. But you can use _r in a WHERE clause to filter by source row number (starting with zero). So you can nest one view() expression in another to limit the row count. Something like this:

view("SELECT * WHERE _r<10",
  view("SELECT .... WHERE .... ORDER BY ....", {Root Container.SomeData}))

Also, you can turn on debug mode to see the jython that the view() function internally uses to execute the operation. You can cut and paste that into a script module, edit it to implement any special behavior, and call it from runScript() or objectScript() instead of using view() directly.

I solved it by using a script, because I have more task for my data. But thank you for the reply, this will help me in other projects.

I’ve updated the Simulation Aids module version 1.7.8 to support the LIMIT clause in the view() function. (-:

2 Likes

Is it true that the Simulation Aids module does not support the like operator?

It does not support any SQL operators or functions. Expressions in its Pseudo-SQL are python, so you can do the equivalent of LIKE in various ways. The python string method .startswith() is handy.

That means I have to use a python script?
Because I prefer to use an expression for the readability.

No, you have to use python expressions within the PseudoSQL. The view() expression function writes the necessary script for you and runs it “under the hood”. The view() function doesn’t actually talk to a database that could execute real SQL expressions. Ignition runs java and jython, so the view() expression must do so. As noted in the documentation, if you turn on DEBUG logging for the view expression function, it will show you the script it generated. That can be a big help if your syntax throws an error.

1 Like

I succeeded to use order by in the view() expression.
However I didn’t succeed to order by descending with this function.
Is this not supported yet?

No, the descending keyword is not supported. For numerical keys, you can negate the expression.

I found another way to do so.
I used the following code to sort ascending and it’s working perfectly :
sortDataset({Root Container.Rectangle background.AlarmData},‘AlarmCount’,0)

Thanks for the help.

Just to close the loop here - this isn’t a feature we are likely to add. +1 to using Phil’s module for this.