I am trying to create a lookup table that is linked to my SQL database. I have a database where part stats are saved. I want to be able to search a specific work order and see that parts that are in the work order and see specific stats about each part and I want to see cumulative stats (fail rate, total parts, stuff like that).
I am not sure the best/most efficient way to do this. I saw another topic about potentially using a python script, but I feel like there is a better way to do this.
Scripting is never the most efficient way. Use a Named Query binding on a Table.
I saw there was the option but my database isn’t showing up. I’m sure I’m doing something wrong but I don’t know what
You need to create a named query first - do you have (saved) named queries in your current project?
I do not
Okay, so you'll need to create a named query and add a parameter to it for your work order:
Then you'll be able to select that in the Perspective binding and link the query parameter to some property on your view.
And, just in case you haven't realised it yet, the first thing you need to do is create a database connection.
I now have it all setup so I can pull in data from my SQL and display it in a table. I still have another question though. How do I map a query parameter to a input box? I want to let the user type in a workorder and see all the parts from that work order
That's what Paul's second video link demonstrates. Named Query Parameters Video at Inductive University.
I see that I can use the QueryString to create a dynamic parameter. I just don’t know how to link that QueryString to the numeric entry field I have
You can, but generally should not, as it permits SQL injection from user input. Use value parameters.
(Show your NQ SQL, formatted as code, for more specific help.)
Bidirectional checkbox.fx in the parameter box:You can pass the value of the input directly as a parameter to the query, but I don't recommend this, as the reference can break if you ever rearrange the component layout holding that input (or the table the query is bound to).
Understand this, from Named Query Parameters | Ignition User Manual
The Value type should be used whenever a Named Query needs a dynamic WHERE clause. These act like values passed to a prepared statement, meaning they can never be used to parameterize column or table names. However, they are resilient to SQL injection attacks.
Example:
SELECT *
FROM mytable
WHERE name = :myParam
QueryStrings are more flexible than the Value type in that they can be used to parameterize column and table names. However, their values are never sanitized, which causes them to be more susceptible to SQL injection attacks. When using QueryStrings, it is best to avoid situations where the user can manually type in the value that will be passed to the Name Query.
Example:
SELECT {myColumnName}
FROM mytable
WHERE ...
Where do I create this custom view property?
Sorry for all the questions. I am very new to Ignition and how to navigate everything.
Custom properties are created in the panel on the right:
Just make sure you have the view itself selected, not the root or any other component within it (you can create custom properties for those, but I don't recommend it, for the reason I stated about relative references potentially breaking when you rearrange components).
