SQL UPDATE statements silently fail in script console

Ignition
,
1

Hello all

I'm here to get help eliminating possible issues on my end before claiming there's a bug with Ignition.

I'm writing some library functions that generically handle reporting requests. While doing so I'm breaking things apart as much as possible so that someone wanting to inspect the pipeline can call individual pieces of it from the script console. In doing so I noticed that I was unable to update my database from the console context, even with the correct read/write comm mode enabled.

I boiled it down to as simple of a query as I could. This query:

UPDATE READS_ReportRequests SET [Status] = 'Processing'

Runs perfectly fine in any DBM I've tried it with. I then reset the value of the Status column for the singular row in the table to something else.

Recontextualizing it for use in Ignition script console:

query = "UPDATE READS_ReportRequests SET [Status] = 'Processing'"
rows = system.db.runPrepUpdate(query, [])
print rows

Displays 0 and, checking the table, has not done anything. Adding things like targetting the specific PK of that singular row doesn't change anything.

Running a similar script as an onActionPerformed for a button in a Perspective view:

def runAction(self, event):
	query = "UPDATE READS_ReportRequests SET [Status] = 'Processing'"
	rows = system.db.runPrepUpdate(query, [])
	system.perspective.print(rows)

Prints 1 and the update is reflected in the table.

This feels like a bug, but am I missing some context for why this shouldn't work?

I'm using SQL Server, with an admittedly outdated JDBC I've been trying to get updated. But the ability to get this working on a Perspective view/gateway context suggets to me that isn't the issue.

2

Doing something like:

query = """
    UPDATE READS_ReportRequests SET [Status] = 'Processing';
    SELECT @@ROWCOUNT AS RC;
"""
rc = system.db.runScalarPrepQuery(query, [])
print rc

Prints 1 and updates the row in the table. Seeing as it just "Returns the number of rows affected by the last statement" per the Microsoft Documentation for SQL Server it really feels like there's an issue with runPrepUpdate in the console context specifically.

3

IIRC, there's a bug in the v8.3 runPrepUpdate if there are no parameters to substitute.

4 
query = """
    UPDATE READS_ReportRequests SET [Status] = 'Processing'
    WHERE 1 = ?
"""
rc = system.db.runPrepUpdate(query, [1])
print rc

Indeed affects a change in the database.

I ended up using this pattern instead:

query = """
        DECLARE @claimed TABLE (ID INT);

        UPDATE READS_ReportRequests
        SET Status = 'Processing'
        OUTPUT INSERTED.ID INTO @claimed
        WHERE ID = (
            SELECT TOP 1 ID
            FROM READS_ReportRequests WITH (READPAST, UPDLOCK)
            WHERE Status = 'Pending'
            ORDER BY RequestedDate ASC
        );

        SELECT ID FROM @claimed;
    """
    result = system.db.runPrepQuery(query, [])

Which has the useful side effect of returning the ID of the row I updated anyway, which getKey=1 does not seem to do.

5

I believe this was fixed in 8.3.7.
Ignition Release Notes for Version 8.3.7 | Inductive Automation.

6

Correct: