Binding to a named query column

I’m sorry if this is a repeat question.

I have a named query that returns one row with a bunch of columns. For each column, I’d like to bind it to a memory tag, whenever the query refreshes.

Is this possible? Or do I need a scalar named query for each binding?

Here’s what my named query looks like:

SELECT TOP 1 productid, valuex, valuey, valuez FROM products WHERE productid = :productId

The :productId parameter is tied to a text box.

Under my Binding Preview, I get Dataset[1 rows, 4 columns] which would work for displaying the value in a table, but not for binding the column data to a tag.

Why a memory tag, if your are getting the parameter from a user entry field? Before recommending one of several approaches, it matters what you are really trying to do. Some relevant questions:

Are multiple users going to provide different parameter values and expect to see different results?

Or are the results supposed to be global? If so, should the query continue updating when no user is displaying the parameter entry field?

Is this Vision or Perspective or Both?

Hi pturmel, thanks for the quick response!

I am new to Ignition, so I may be way off base here, but I was thinking of a memory tag as something like a global variable that could be used by different parts of the system. In addition to getting the information (valuex, valuey, valuez) from the database to the PLC, we need to use it to for subsequent, dependent database queries.

We don’t need results when there is no user input on the parameter entry field. In that case, I’d think about setting another global variable (system offline) to false, that would tell the PLC to take it easy.

This is for Perspective so far.

Ok, global. If the PLC will leave loaded values alone, I would just use OPC tags for anything that the PLC needs, and memory tags for anything else that will be needed later. Possibly including a copy of the parameter used.

I would use an unbound text entry field to accept the parameter combined with an “Execute” button. The execute button would have an action script something like this, assuming you make the Ignition tags in a folder with the same names as the result columns:

param = self.getSibling("text field name").props.text
ds = system.db.runNamedQuery("projectName", "queryName", {'productId': param})
tagNames = ["[default]Path/To/Tags/"+col for col in ds.columnNames]
tagValues = [ds.getValueAt(0, col) for col in ds.columnNames]
system.tag.writeBlocking(tagNames, tagValues)

The tags that are type OPC, connected to your PLC, will be loaded with the appropriate data. The memory tags will hold onto the rest. (The above won’t create the tags for you–do that manually.)

Thank you, sir!