Using Drop-Downs to specify a SQL query

Ignition
,
1

I am looking for the best way to accomplish the following with drop-down boxes:

  1. Let the selection from one drop-down dictate what set of tags will be displayed (i.e., choose a reactor from the drop-down, and temp/pressure/level values will propagate to the HMI).

  2. Let the selection from two drop-downs be linked to the row+column of a SQL query (i.e. choose a reactor to set the row, choose a cleaning solvent to set the column, and the window will return the resulting charge weight to a label... choosing reactor 1 and solvent 1 will return the weight found in [1,1] in the database).

Any advice appreciated!

2

What flavor of DB? (MSSQL, MySQL, Postresql, etc...)

Off the top of my head without knowing anything about your setup I would suggest something like this:

Create a Named Query with the required parameters in the WHERE clause and then use a property change script to call system.db.runNamedQuery() supplying the values of the dropdowns as the parameter values.

3

Our DB is MSSQL.

I'll give runNamedQuery a try and see if i can get something worked out, thanks!

4

Why a change script ?
A simple query binding should be enough.

  1. Let's say you have, in the tag provider [default], a folder named reactors, that contains the following UDTS: reactor1, reactor2, reactorA, reactorB, reactorFoo.
    Configure your dropdown options to look like this:
[
  {
    'label': "reactor 1",
    'value': "reactor1"
  },
  {
    'label': "reactor 2",
    'value': "reactor2"
  },
  {
    'label': "reactor A",
    'value': "reactorA"
  },
  {
    'label': "reactor B",
    'value': "reactorB"
  },
  {
    'label': "reactor foo",
    'value': "reactorFoo"
  }
]

Now, use an indirect tag binding on the labels where you want to disaply your reactors properties, so that it looks something like this:
[default]reactors/{reactor}/temperature
And use the dropdowns value property as the {reactor} parameter.

  1. Create a named query, let's call it get_charge_weight. Make it a SCALAR property, as you want to get only one value out of this. The query should look like this, assuming the reactor and solvent columns are foreign keys to actual reactors and solvents:
select charge_weight
from charges
where reactor_id = :reactor and solvent_id = :solvent

Obviiously you'll need to configure those 2 parameters to the appropriate names.
Now create your 2 dropdowns with the requires options. most likely you can just generate them with queries:

select id
from reactors

select id
from solvents

You can then use the dropdown values as parameter for the get_charge_weight named query, that you can use in a query binding on whatever you want to use to display the result.