Transaction Group-Stored Proc, Seems to set completion tag before results are written

General Discussion
1

I have a Trans Group of type stored proc that selects data from a table. This TransGroup has a write handshake on success tag, writing a one on success. I have a script tied to the handshake tag, with a if trigger tag value ==1 in the script, that copies a value into a memory tag for later use. But instead of getting the value from the query, I am getting the value prior to the query. Which is actually the result of the prior query.

This seems like the values from the query are not written to the tags prior to the Handshake tag is set hi.

Question is how do I know when the values have been written, or am I missing something?

2

Sounds like it, yes. I'm going to assume that value you are trying to copy is an OPC tag. Writing to an OPC tag (or other tag type with external source) doesn't update the tag value at that point, but when the tag subscription reads it back from the device. The device is the "source of truth" for OPC tags, so the written value won't show up in the tag until that value makes the round trip. (There's a tag group setting called "optimistic writes" that tries to address this, but has other negative consequences.)

Most likely, you shouldn't be using a transaction group for this, but a script instead. When you retrieve results from a scripted SP call, you are responsible for writing them where you wish, and can write to multiple destinations.

3

The results coming back from the SP go into memory tags. The handshake tag is a memory tag. I have a script on the handshake tag that concatenates the results coming back from the SP into one string that is then placed into an OPC tag, which then is written to the PLC. The script uses readBlocking calls to gather the data.

What am I missing?

4

Sounds like the handshake and value write are racing (parallel) and the handshake is winning. I wouldn't have thought that would occur, but I don't attach scripts to transaction groups like you've done. (If I need script actions, I don't use a transaction group at all.)

5

Sounds like good advice. But I am having difficulty getting a SP call to work in a script. cold you give me example code for this SP, connecting with a DB connection of PW_HV?
ALTER PROCEDURE [dbo].[MoveTempDataToPermanent_Attribute]
(

@TempTableName AS VARCHAR(255),
@SP_Result as int OUTPUT,
@SP_BadRecordUID as int OUTPUT

) AS
Thanks for your time

6

I am using 8.1.22

7

Something like this:

def doSP(dsName, tempTableName):
	sp = system.db.createSProcCall("MoveTempDataToPermanent_Attribute", dsName)
	sp.registerInParam(1, system.db.VARCHAR, tempTableName)
	sp.registerOutParam(2, system.db.INTEGER)
	sp.registerOutParam(3, system.db.INTEGER)
	system.db.execSProcCall(sp)
	return sp.getOutParamValue(2), sp.getOutParamValue(3)

As noted in the docs, not all JDBC drivers behave properly with named arguments. I use the numeric index instead.

8

Thanks - One quick question, what is def doSP(dsName,tempTableName): doing?

9

I figured it out. You Are creating a callable piece of code.