Copy sql table data

Ignition
, ,
1

Hello, new to this forum and to ignition but learning my way around. Need advice on or guidance to documentation to accomplish the following:

I have a SQL table called recipenames and three other tables recipe1, recipe2, and sample. the last 3 tables have the same structure. I have a dropdown component with a SQL query binding that allows me to select values stored in recipenames, these are the names of the other tables.

I have a table component that uses a SQL query binding to display data based on the {Root Container.Dropdown.selectedStringValue} making it a dynamic table based on this property.

I would like to move the data from the table that is currently referenced in the {Root Container.Dropdown.selectedStringValue} property into a table called sample.

I figure i need to execute two SQL queries 1)clear the old data from sample table 2)populate sample table with new data

to clear the sample table, I want to execute SQL query

DELETE FROM sample WHERE id IS NOT NULL

to copy the data, I want to execute SQL query:

INSERT INTO sample (ID, ItemNumber, ProductName, Setpoint)

SELECT ID, ItemNumber, ProductName, Setpoint FROM {Root Container.Dropdown.selectedStringValue}

My questions are what mechanism can i use to execute those two SQL queries based off a Boolean status change trigger? I figure it's a tag change gateway event, but I don't know python so I am lost in that part of the designer.

second question is would I still be able to replace the source table name with the property value path I used to populate the table component? I tried doing so in the database query browser tool as this is where I was trying to work out the queries to clear and copy data but got an error when executing.

Thank you,
Ric L.

2

Are you doing any other operations to the sample table? I only ask because this seems... counterintuitive when, on the face of it, why not do a select, and work with the resultant dataset?

3

I'll try to steer you in the right direction. I think you need to do what @JordanCClark is telling you but I suspect from your table structure that you might not understand what he's saying.

If you have 3 recipes and they all have the same structure it makes sense to have a single table to store the recipe data and a recipe ID that you can use to identify which information goes to which recipe.

Your dropdown on your screen would then have a recipe ID behind the scenes and a recipe name that is visible in the dropdown. When they select the recipe your query would query the recipe information for that recipe using its recipe ID.

4

Hello,

thank you both for your replies and help on this topic. Yes there is another reason moving data into the sample table. I currently have a transaction group for DB to OPC import of data into plc. the transaction group from what I have learned can only reference one table. so i want to move dynamic data into my sample table (with same table structure) and have the transaction group tied to it.

my recipe will hold 3 data points per ingredient and recipes can have more than 20 ingredients. So if i make a single table to hold all my recipes i would need to import 60+ tags within the transaction group for each recipe as well as the table would need to have that same number of columns. i would not be able to show a table with only a single recipe's data possibly without some kind of scripting to isolate the single row and i think that the table would be represented as a single row multi-column table vs a multi row table.

by creating tables for each recipe and a table with recipe names, i can get away with bringing in only 3 tags into my sample table to simplify the transaction group. I know that this would create more manual DB work as far as creating tables for each ingredient but would be less work within the transaction group and amount of tags/modbus addresses to create in my PLC. i would also benefit by being able to display a recipe specific table as well.

thank you,
Ricardo L.

5

I think a more common modle would be to have a Recipe header table, which stores all of the data which is shared among all recipes, Recipe Header ID, Recipe Name, etc...

Then have another table which references the Recipe Header ID, and has a row for each step in each recipe. This table would have a StepCodeID, which would point to a third table which stores the table for each step.

I also, don't believe that Transaction Groups are the best tool for this particular job, an you would probably be better served by a Gateway Script.

Alot of this depends on how you structure the project. Recipe systems are complex and quite the undertaking in an unfamilar software.

6

Whether they are or not in this particular case, a single table for all recipes is still the right design choice. A transaction group can have a WHERE clause for a recipe ID.

7

Thank you for the feedback. If working with a single table is the best approach to recipe handling, then I will use that method. I was trying to find a way to minimize the amount of tags that i need to create.
my table structure is the following
columns: ID/RecipeName/Ing1/Code1/SP1/Ing2/Code2/SP2/Ing3/Code3/SP3

So at this point i have 11 tags that would be part of my transaction group(I am using the ID column as a key/value pair in order to select a row from my PLC). If one recipe has an ingredient list of 30 ingredients, I would need a total of 90+ columns in my table and my transaction group would require 90 tags to move the recipe data into my PLC.

my reason behind wanting to move data from one table to another was so that i could create a "hold" table with fewer columns: ID/Ingredient/Code/Setpoint. i would create a transaction group with the hold table then use my dynamic table selection dropdown component to move data from the selected table into it. This transaction group would only have 4 tags to move from the db to the OPC and again i would use the ID field to move between rows of the table within my PLC. for my program i don't need to bring in all the recipe data just the ingredient its working on any given step of the process.

maybe this is an unconventional method of recipe handling and i should just stick to a single table recipe and deal with the multiple columns and tags. but at this point I am now just curious to know if I can make this idea work.

Thank you,
Ricardo L.

8

I think you misunderstood. You described three tables for recipe content, plus a table for recipe names. Keep that latter, and put columns in it for the particulars of a recipe other than the ingredients.

The "single table" is to cover the columns needed for single ingredients, with a column with the recipe ID from the recipe names table, and probably a sequence number for the ingredient index within the recipe.

9

my apologies I probably mistyped something along the way when describing my tables/goals. I am basically breaking out my recipes into single tables and trying to load dynamic data into a table used in a transaction group

my original recipes table has 3 columns per ingredient per "x" amount of ingredients along with a recipe name column. 1 row = 1 recipe with "x" amount of columns multiple rows depending on recipes

I created a new table for every recipe with only 3 columns. 1 row = 1 ingredient with data, with multiple rows depending on ingredients.

now I have 4 total tables recipenames, recipe1, recipe2, recipe3. i created a 5th table called "hold" which has the same structure as the recipe tables.

I have a dropdown and table component that allows me to select what recipe i want to see using recipenames table.

now i just want to move the data of the selected recipe into that "hold" table which will have the transaction group sending data to PLC. i have an sql query to do so, i dont know how to trigger it.

my plc handles the indexing to move between rows of the db table to view data on that side

10

I think you’re making it harder than it needs to be.

Table - Recipes
|Recipe Id| RecipeName |

Table - RecipeSteps
|RecipeId|StepNumber|StepId|TargetValue|

Table - StepCodes
|StepId|StepDescription|Item Number|

Recipes Has a row for each recipe. Add more columns to hold data specific to each recipe. An example might be something like if it’s production ready or not.

RecipeSteps has a row for each step of each recipe. It would have a clustered index with the RecipeId and StepNumber. Add more columns for data which is specific to a step in a recipe.

StepCodes has a row for each step available to be used in a recipe. Add more columns for data which is specific to a step, such as if it has a lot number, or can run concurrently with other steps.

Then you query the database using joins to connect the tables into a single recipe.

SELECT StepNumber,StepDescription,ItemNumber, TergetValue
FROM RecipeSteps 
    INNER JOIN StepCodes ON RecipeSteps.StepId = StepCodes.StepId
WHERE RecipeId = :SelectedRecipeId

This allows you to have a single database structure that can hold multiple recipes with a dynamic number of steps, and the steps can be dynamic between recipes.

All of which can be returned as a single return set of the steps for a chosen recipe.

Obviously you will need some hard number of allowable steps, like 50 or something you feel will cover any recipe.

11

And this is precisely what we are trying to discourage. Don't do it. Use one table. Use recipe IDs within that table to distinguish which rows belong to which recipes. Use a WHERE clause in the transaction group to pick the right rows.

This lets you have many recipes with one set of tables. Like Louis suggests.

12

Ok thank you i will utilize the one table recipe method. I appreciate all your time and input into my questions.

Ricardo L.