[BUG-6572]Updating last row of table to tags using TX Group

Ignition
1

I have a database table that is always changing, rows are being inserted, and sometimes rows are being updated.
I am interested in bringing out the values for the LAST ROW of the table into Ignition memory tags.

I started using a Standard Transaction group with the following configuration:

  • mode set to DB->OPC.
  • timer set to 1s
  • use custom index column = “rcdID” (the primary key of the table)
  • update/select = last
  • Only evaluate when values have changed = TRUE. Watch ALL tags.

Everything works sweet if i link each column to its corresponding memory tag, except when trying to update the value of the index column, “rcdID”.

If i try to link this column as shown below, i get an execution group error.

image
image938×173 41.7 KB

Error during group execution. com.microsoft.sqlserver.jdbc.SQLServerException: Ambiguous column name 'RcdID'. Ambiguous column name 'RcdID'.

Why can we not also read this column, while using it in the where query for the transaction group?

2

RcdID can be either a tag name or a db column name. It doesn't know which to use.

3

The item name is “tag_rcdID”, so shouldn’t that make it distinct from the db column name?
However the actual tag path is:
[Corporate]Lumber/ERP/latestTagMasterRecord/rcdID

Would changing that memory tag to something slightly different fix it?

4

Changing the item name (in the TX Group), nor changing the tag name (realtime provider) did anything to help this.
I still don’t understand the ambiguity here.

5

What does your database table look like?

This error about ambiguous column names is simply being relayed to you from the database via JDBC, it’s not originating in Ignition.

6

Create syntax:

CREATE TABLE [dbo].[mesImportMaster](
	[rcdID] [bigint] IDENTITY(1,1) NOT NULL,
	[tagID] [varchar](100) NULL,
	[tagSourceID] [varchar](100) NULL,
	[tagSourceDate] [datetime] NULL,
	[tagLocationID] [varchar](100) NULL,
	[tagThickID] [varchar](100) NULL,
	[tagWidthID] [varchar](100) NULL,
	[tagLengthID] [varchar](100) NULL,
	[tagGradeID] [varchar](100) NULL,
	[tagUFTwo] [varchar](100) NULL,
	[tagFBM] [varchar](100) NULL,
	[tagPieces] [int] NULL,
	[tagTally] [int] NULL,
	[tagLineSeq] [varchar](100) NULL,
	[tagImported] [varchar](10) NULL,
	[tagReceiving] [varchar](100) NULL,
	[tagPrintLabel] [varchar](100) NULL,
	[tagPrinted] [bit] NULL,
	[tagMachineID] [varchar](100) NULL,
	[tagProductID] [varchar](100) NULL,
	[tagImportDate] [datetime] NULL,
	[tagSurfacingID] [varchar](100) NULL,
	[tagStateID] [varchar](100) NULL,
	[tagVolume] [real] NULL,
	[dateModified] [datetime] NULL,
	[modifiedBy] [varchar](50) NULL,
 CONSTRAINT [PK_mesImportMaster] PRIMARY KEY CLUSTERED 
(
	[rcdID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

I basically want the Transaction group to give me the last value in the table, for specific columns i’ve listed in the transaction group.
This works fine for any column, except the column used as the primary key, rcdID

I presume this is an issue because Ignition has to run a query that uses ORDER BY rcdID DESC LIMIT 1 in the SQL statement but i don’t see how this should be an issue.

7

Is this possible?

8

So what looks to be happening is Ignition is sending the following query to your SQL Server instance:

SELECT "rcdID","tagLocationID","tagSourceID","tagID","tagImported","tagLengthID","rcdID" FROM mesImportMaster ORDER BY "rcdID" DESC

What looks to be happening is the ORDER BY statement is telling the DB to sort by a column that is non-unique. This causes SQLServer (and Oracle) to throw an error. MySQL and Postgres handle it without issue for some reason.

At this point it doesn’t look like there is a work around for this and it is something we would need to make a change to the code base to work around and I am filing a bug for it. You might be able to create a trigger against the DB to clone the rcdID column to another column as a work around.

Garth

9

Hi Garth, thanks for the actual query, that was what i was trying to find out about.
Why does it try to SELECT “rcdID” twice though?
Is that because “rcdID” is used as the “Custom index column” option, and Ignition just adds whatever that is to the start/end of the query?

10

Yeah, Ignition is always including the index when doing a Transaction Group select statement (I am guessing to ensure the query returns as quick as possible as well as to quickly find the last entry) along with all the columns you are monitoring. The default state for Transaction Groups is for the TG to generate its own index column and it isn't really available to access within the group itself, and this specific scenario looks to be something we missed when this was developed years ago.

Garth

11

Pssst! Anything a transaction group does can also be done by a timer or scheduled event script…

2 Likes
12

Would creating a view in the database instead work in this case?
Create the view as a SELECT TOP 1 * ORDER BY rcdID DESC
That way the newest record will always be there.

(I would test but I am not around a gateway at the moment)

13

Potentially, I’ll have a play with that idea.

14

I suppose, but not as good at detecting ANY change to ANY column in the TX Group, which is what i am trying to achieve. Also not as intuitive to other engineers that may come after me. I like the simplicity of Transaction Groups for the less advanced users.

15

Tried this just now. Used the VIEW in the Transaction Group table, but still get the ambiguous column name ‘rcdID’ error.
Damn.

16

Perhaps you can clear the error by prefacing the column name with the table name, like you would if you were combining two different tables:

Select mesImportMaster.rcdID…

17

Good tip, i took a slightly different approach and got it working…

In my VIEW definition i simply added another column referencing the rcdID but giving it an alias name.
ie:

SELECT TOP (1) rcdID, rcdID AS 'rcdID_Latest', tagID ...

Then the Transaction Group tag that i want to write to my Ignition memory tag can reference the rcdID_Latest column, and the TX Group itself can still use the original rcdID column for its “custom column” and ORDER BY clauses without conflict…

Thanks for all the tips, glad i finally got it working, and understand how these groups work behind the scenes a bit more.

1 Like