SQL Edit Command Doesn't Allow Transaction Group to Find Value

Good morning,

I pre-made a recipe list for a customer from the office but when I got on-site it looks like I was given some wrong information. At the office I used a custom made recipe editor to add fields into SQL with transaction groups. I added ~300 recipes with operation numbers, material number, names, etc. The problem I found is that lets say operation number “400” was in fact “0400” on their system.

So, instead of re-making all recipes with the new operation numbers I went in to SQL and edited all rows and added the “0” in front of every operation number. Now when I go to load the recipe from ignition, it can no longer find the recipe even though the operation number matches to what is in the SQL Database:

This only happens on any part that I had to edit with SQL. If I load a part that I didn’t edit (one that was already 4 characters long) it loads just fine.

Has anyone ran across this issue before? I am minor when it comes to SQL programming, but it almost seems like I have to re-initialize the database for Ignition to read it again. I can save the exact same part information into the database and it will find it after, but then I show two recipes with the same values. My search for my transaction group is the following:

OP_NUM_EDIT_SP = ‘{[default]Collins/Oven_4/DATABASE/OP_NUM_EDIT_SP}’ AND MATERIAL_NUM_EDIT_SP = ‘{[default]Collins/Oven_4/DATABASE/MATERIAL_NUM_EDIT_SP}’

What does the query you used to make the edit in SQL look like?

Is it possible that you used a transaction but didn’t confirm the changes? Perhaps the transaction is still open and your changes are in some kind of limbo?

If you do a select from the DB Management Suite does it find the data as you expect? If you try to Select the data based on the old Operation Number does that work?

This is the statement I used and then edited all rows in operation number column. I used the pre-caned EDIT TOP 200 Rows then just changed the 200 to 1000

I made the changes and closed the SQL studio.

It does find the data as expected. The old operation number does not come up.

Huh.

I would expect it to work.

What if you run a select statement from the Database Query Browser in ignition. Does that work as expected?

Clearly I’m guessing here, I would have expected this to work.

You may want to get a hold of support and have them take a look.

Yes I did try that and it did work as well. I just tried exporting the table and renaming it when importing but it is doing the same thing as well.

What are your column types?

Some things to check:

  • In some environments, an extra leading zero on an integer causes it to be interpreted as octal instead of decimal.
  • To reliably use leading zeros, the column types must be characters, not numbers. And all of the properties and parameters along your query chain must also be strings.
2 Likes

All of my column types are strings, or characters as you said. Also all my my setpoints for the transaction group searches are strings.

Might just be a typo but it looks like you are selecting the ndx field twice but one is recipes and the other is recipe?
oven4_recipes_ndx and oven4_recipe_ndx?

This is what I have set up in the transaction group manager:

But I am not searing based on the index just the combination of the operation number and material number.

I just created a DB to OPC group as a test and it is working fine with a similar setup.
One thing I am noticing is you have single quotes around the tag in the Where clause.
Try removing the single quotes, they aren’t needed.

He’s using strings. They are needed.

Works fine for me without them. :man_shrugging: