I would like to get a block transaction group to update several rows in a table at once.
The rows i want updated will be identified by a unique ID, which is one of the block items in my group.
How can i set this up? I want to link the OPC item “SerialNum” to the Where clause.
I see a tag icon in the area where you add the custom WHERE clause to update your rows.
What if you put your desired OPC items in the “Basic OPC/Group Items” section and then reference them in the where clause?
I’m not 100% sure that will work, but it seems plausible.
Unfortunately I had no luck there. None of the Block items show up. I can’t have it reference an individual tag anyway, it needs to somehow reference all the Serial Number tags in the block, and do an update for each row that the SerialNumber is found in the table.
Maybe I am mistaken, but can’t you add the needed serial number OPC tags to this section by right clicking and selecting “New Item->New OPC Item”?
After the tags are added in this group, you should be able to reference them using the tag picker shown in the picture.
Yes i can do that, but it only allows the update of that ONE serial number specified by the OPC tag.
I’ve got that functionality working ok on a normal transaction group, but i’m trying to do several serial number updates at once using a BLOCK group. And the tag picker only lets you reference one tag, but all of them in a list.
Okay. I spent some time this morning trying to mock up what you have created and I think that I got it.
Basically I created some memory tags that would represent serial numbers and result data, similar to your structure above.
The Update syntax is: SerialNum={[~]Serial1} OR SerialNum={[~]Serial2}
So what happens is when I change the result in result 1 or result 2, the appropriate result row corresponding to the serial number it matches updates. I believe this is what you wanted. If both results change before the next update, both rows get updated at the same time.
The only thing I will point out is the time stamp does not change if the data does not change when the 10 second update rolls around. So it is not constantly updating the rows if the data remains the same.
Thanks for taking the time to mock something up.
I’m not quite sure i understand what you’ve done though. Do you need both the OPC items in conjunction with the Block items? Are they the same OPC tag path? I’m guessing your Basic OPC items are Read-Only?
I’ve got it working ok using your method, but am experiencing a strange bug when i am trying to update multiple rows (serial numbers) with the column value (status string).
If i have two serial numbers in the custom update clause which have values 100 and 101, then the first serial number found (100) will have its status changed, but also the serial number is then changed to 101!!
But if i set the Block Item for Serial Numbers to Read-Only, then it doesn’t update anything.
Yes. I have placed Serial1 and Serial2 in the Basic OPC/Group Items list. They have the same OPC path as the serial numbers in the block item called Fleet Serial Numbers
Yes.[quote="richardNZ15, post:7, topic:15246"]
If i have two serial numbers in the custom update clause which have values 100 and 101, then the first serial number found (100) will have its status changed, but also the serial number is then changed to 101!!
[/quote]
That should not happen from what I have see in my setup.
Mine are not read only in the block items, they have a target column
Two Questions:
- Does your update clause reference both serial numbers similar to this:
SerialNum={[~]Serial1} OR SerialNum={[~]Serial2} - Are you storing a timestamp? This could help you determine exactly which row(s) updated. Your status pane at the bottom of the transaction group will let you know how many DB writes happen. Each time the DB write increments at least one row changed.
I hope this helps
@richardNZ15
One other thing that could fix this. You could try updating your JDBC connector. When I mocked up my transaction group the first time, I was getting a SQL syntax error even though there was nothing wrong.
I ran across this link that talked about the process of replacing the JDBC connector on the gateway. Like I said, this could not be your problem, but it is a possibility.
Hi Brandon,
I’ve tried another variation of this for a slightly different application, and am still running into some strange behavior.
I think the only reason your method works, is because of two reasons:
- Both your serial numbers are defined in the table. If only one of them is, that will get overwritten by whichever serial number is first in the select query.
- Serial numbers are in the same order in the table as they would be selected with the custom WHERE select query. If you switch the order then the transaction group will overwrite the serial number and corresponding value to be in the order it wants, which is unacceptable.
I don’t believe this method is actually associating any link between the Group Block Serial Number item, and the Basic OPC Group Serial Number.
What i really want it to do is run a select query on each Serial Number in the block, and only IF an entry is found to match, update the columns in that row with the corresponding Block result values.
I simply don’t think the transaction group is clever enough to do so. It seems the only way to do it is to have a single standard Transaction Group for EVERY unique SerialNumber or “ID” you want updated.