Search

User Manual

GETTING STARTED


MODULES AND PLATFORM


APPENDIX


TUTORIALS & HELPFUL TRICKS


GLOSSARY


STRATEGIC PARTNER LINKS

Sepasoft - MES Modules
Cirrus Link - MQTT Modules

RESOURCES

Inductive University
Ignition Demo Project
Knowledge Base Articles
Forum
IA Support
SDK Documentation
SDK Examples

ALL MANUAL VERSIONS

Ignition 8
Ignition 7.9
Ignition 7.8

Deprecated Pages

Skip to end of metadata
Go to start of metadata


A Named Query Example

Here we will go over the steps necessary to put together a basic named query. This example will create a Named Query that uses a single parameter to run a select query, and then add a Table to a window and create a binding that uses our new Named Query.

We are going to be querying a table in the database that holds information about products stored in storage bays. The database table is named "containers" and has the following structure.

idStorageBayContainerTypeItemNameWeightTime
11JugVanilla25.22017-06-25 15:58:47
61Mason JarChocolate12.32017-06-26 16:05:27
182TraySwiss Cheese88.82017-06-25 01:21:31
223TrayCheddar Cheese54.72017-06-25 03:52:16
233BasketStrawberry36.82017-06-25 15:56:03
313JugWhole Milk80.12017-06-27 09:51:31
323JugFat Free Milk76.92017-06-27 09:52:52

If you want to follow along with the example, feel free to make a database table that looks similar to this one and add as many rows of data as you would like, otherwise, you can use your own and substitute in the proper column names from your database.

On this page ...

Creating a Named Query and Adding Security

  1. Start by opening up the Designer and loading a project.
  2. Locate the Named Query section of the project browser, right click on it, and select the New Query option.



  3. You should now have a fresh Named Query that you can rename whatever you want. We used FirstNamedQuery in the example.




  4. To setup security on the Named Query, set required Security Zone and Role combinations in the Security table of the Settings tab. You can leave this blank if you don't have roles or zones set up yet.
  5. If multiple security combinations are required, use the Add  icon to add additional rows.



Building the Query

  1. Click on the Authoring tab. Here is where we do most of the work.
  2. Under Database Connection, we need to select a database connection that this named query will use. We selected <Default>.
  3. For the Query Type, we can decide what type of query this will be. For this example, we are running a select query that will return a dataset, so we chose Query.
  4. In the Parameters section, we can decide on a list of parameters that will be used in this query. This query is fairly simple and will only use a single parameter.
    Click the Add  icon to add a new parameter and set the following values:
    Type: Parameter
    Name: BayNum
    Data Type: 
    Integer
  5. The Query section below is where we construct our query using the Table Browser.
    1. Right click on the containers table in the Table Browser on the right, and click on Create SELECT Statement. This will populate our query field with a basic select all statement.
    2. Type into the Query field and add the following WHERE clause: "WHERE StorageBay = "
    3. Now drag the BayNum parameter from the Parameters table to the end of the query you just typed. Notice ":BayNum" will be added at the end of the query.

  6.  

Using the Query

  1. In the Project Browser, create or open a Main Window.
  2. Drag a Dropdown List component onto the window.
  3. The Dropdown List is where we will be able to select a Bay Number to use as our Named Query's BayNum Parameter. Use the Dataset Viewer to set the Data property of the Dropdown to look like this:

    ValueLabel
    1Bay 1
    2Bay 2
    3Bay 3




  4. Now drag a Table component onto the window. We can setup a Named Query binding on the Data property.
  5. Click on the Binding icon for the data property and select the Named Query binding type.
  6. Set the Path property by clicking on the Select Resource Path  icon and selecting your new Named Query from the list. For this example, it is FirstNamedQuery. Alternately, you can type the name in.
  7. Highlight your BayNum Parameter (in the Parameters table) and click on the Insert Property  icon. We want to select the Selected Value property of our Dropdown List.
  8. Finally, we want to ensure the Polling Mode is set to Off. This means the query will not run continuously, but will only run when it changes such as when a new bay is selected from the dropdown.
  9. Click the OK button to save your binding, and put your Designer into Preview Mode  to test it. We can then make a selection (i.e., Bay 3) with our Dropdown, and see the table populate with data.

 


Related Topics ...

 

  • No labels