Bind runtime table data with SQL Query

Hello, I would like to ask if anyone knows how to associate a SQL query generated by a script, to the data field of a table. So that the table updates automatically when the database is changed.

What I would like is a method that allows me to run a script whenever the table decides to re-query the db.

So currently you have a table component whose data property is bound to a sql query binding? Or is it not bound and populated instead by the script?

If it’s of any relevance, you can call the system.db.refresh (vision) or the refreshBinding component method (perspective)

Are you using vision or perspective?

Hi, thanks for the answer.

I am currently using vision.
I found a solution by adding a script to the page load event that performs the correct query and assigns the result’s dataset to the date field of the table.

This solves the problem, but every time an operation is performed, within the page, for example an insert through a simple form, so without reloading the page, I have to manually update the table again.

What i wanted was the ability to perform these updates automatically via polling, a bit like when you bind to a named query.

Is your raw query not compatible with the Named Query system? It sounds like you want a script to “post-process” the raw data before assigning to the table. If so, add a custom dataset property to the table, bind your named query there, and use a propertyChange event script to convert the data whenever it arrives.

Unfortunately not. I’ll explain better.

I created a configuration file external to ignition. Upon startup, the gateway reads it, and based on this file, certain columns of a table may be visible or not. Like various other properties.

By processing the configuration file i insert the list of visible columns in system.util.getGlobals (“myKey”). (“myKey” is for example).

The sql query for the table is therefore a function of this processing, and consequently of the global dictionary.

In named queries system i have no way to “write” code to retrieve this data.

Actually, you do. The “queryString” parameter type is specifically for these odd cases where you need to supply some structure to the query, not just values.

Yes, but how do i pass the list inside the dictionary to the parameter value?

You convert the dictionary to a string containing the SQL fragment your named query needs. Personally, I would put that in a string tag, not in the python globals. The caller of the named query would include the tag reference. If you need different configurations per Vision client, use a client tag.


Yes, is doable.

Thanks man