Ignition Named Query Inserts Must Use the Update Query Type

The second system.db.runNamedQuery call never runs because the first one throws an exception. The named query NP/HI/25L/WD/OrderBreakdown/Insert_life_cycle is an INSERT, but its Query Type is set to Query. SQL Server executes the INSERT and the row lands. The JDBC driver then raises SQLServerException: The statement did not return a result set., and that exception ends the script before the reservation loop starts.

The fix has three parts:

1. Set every INSERT, UPDATE, or DELETE named query to Update Query.
2. Save the project.
3. In gateway scope, call the signature that takes the project name as its first argument.

Read the Symptoms Before Touching the Code

Here is how the fault looks. The tag goes to 1. One row appears in the life-cycle table. No reservation rows appear. The Designer shows nothing. The script console gives either a clean run or a different stack trace, depending on how you call it. That mix of results sends people after scope bugs, thread pools, and named-query "bugs" long before they open the named query itself.

Start here. Open the named query that writes the first row and check its Query Type.

Symptom	Cause	First check
First named query row is in the DB; the second named query (inside the loop) never inserts	First named query is an INSERT with Query Type = Query. The INSERT commits, then the driver throws and the script aborts.	Named query Authoring settings: Query Type
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.	Statement with no result set run through the result-set (Query) path	Query Type on every write query
Query Type changed to Update Query, same error persists	Project not saved. The gateway still serves the old named query resource.	Save the project, then retrigger
ClassCastException: Cannot coerce value 'NP/HI/25L/WD/OrderBreakdown/Insert_life_cycle' into type: interface java.util.Map in the script console	Project-name argument passed in Designer/Vision Client scope. There the second positional argument must be the params dict.	Which scope is executing the call
Code moved to a project library and called from the tag's valueChanged, and now nothing runs	Tags belong to no project, so the tag cannot resolve project library scripts without a Gateway Scripting Project	Gateway Settings: Gateway Scripting Project, or move the code to a project Gateway Tag Change event
runPrepQuery calls in a tag event fail or return nothing	No database argument in gateway scope with no project default to fall back on	Add database='...' to every system.db call
Other tags' change scripts report missedEvents or seem to skip	Slow blocking DB work saturates the tag event thread pool	Move the work out of the tag event

Understand Why One Insert Lands and the Next Never Runs

A named query's Query Type selects the JDBC execution path:

• Query: executes the statement and expects a result set, which comes back as a dataset.
• Update Query: executes the statement and returns the affected row count.
• Scalar Query: expects a single value.

With Query Type = Query, an INSERT goes down the result-set path. SQL Server runs the statement, and under autocommit the row is committed immediately. The driver then finds no result set and throws. Ignition wraps that as a GatewayException and raises it into Jython.

Nothing in valueChanged catches it, so the function exits at that line. The for loop and the Insert_Material_Reservation call are never reached. From the database it looks as if only one named query per script is allowed. That is not the fault. Running several named queries in one script is normal.

Two details make this worse:

• The Testing tab in the named query editor misleads you. An INSERT tested under Query type still writes the row, so the query "works" there.
• Tag event errors land in the gateway log, not the Designer. Unhandled exceptions from a tag's valueChanged go to the gateway's log viewer. If you only watch the Designer output console, you see nothing.

Know Which Scope Is Executing the Call

system.db.runNamedQuery has two call signatures. Which one applies depends on where the script runs:

• Designer script console: Vision Client scope, always tied to the open project. Signature: runNamedQuery(path, params). If you pass a project name first, the path lands in the params slot, which produces the Cannot coerce value ... into type: interface java.util.Map error.
• Tag valueChanged event: gateway scope, with no project association. Signature: runNamedQuery(project, path, params). Leave out the project and the gateway cannot find the named query.
• Project gateway events (Tag Change, Timer, Message Handler): gateway scope, owned by a project. Named query functions in gateway scope still need the project argument.

This is why the script console is not a valid test bench for gateway code. In this case the console also hit the real fault. Once the project argument was removed, the console trace shows the same The statement did not return a result set. error, coming back from the gateway's named query executor. The error follows the named query resource wherever it runs. Scope was never the root cause.

To test gateway-scope code from the Designer, run it in gateway scope. Put the logic in a project library function and call it from a gateway message handler with system.util.sendRequest. Alternatively, trigger the real tag and read the gateway log.

Fix the Named Query Type First

1. In the Designer Project Browser, open Named Queries and select NP/HI/25L/WD/OrderBreakdown/Insert_life_cycle.
2. On the Authoring settings, change Query Type from Query to Update Query.
3. Open NP/HI/25L/WD/OrderBreakdown/Insert_Material_Reservation and set it to Update Query as well. Do the same for any other named query that runs INSERT, UPDATE, DELETE, or MERGE.
4. Save the project. Until you save, the gateway keeps executing the old resource, and tag scripts run on the gateway. A changed but unsaved Query Type produces exactly the same error.
5. Delete any test rows the failed runs left behind. Each failed trigger committed a life-cycle row with no matching reservations.
6. Toggle the trigger tag back to 0 and then to 1.

With Update Query set, runNamedQuery returns an integer row count instead of throwing. Log it; it becomes your first verification point.

Correct the Gateway-Scope Calls

Fix these even after the Query Type change. Each one is a separate failure waiting to happen:

• Project name on every named query call. system.db.runNamedQuery('WDE_System', 'NP/HI/25L/WD/OrderBreakdown/Insert_life_cycle', life_cycle_params). The project must be the one that owns the named query.
• Database on every ad-hoc query. A tag has no project, so it has no default database. Pass database='<connection name>' to runPrepQuery and runPrepUpdate. Copy the name exactly as it appears under the gateway's database connections page.
• One tag read, not three. Pass all three paths to a single system.tag.readBlocking call. Each separate call is its own blocking round trip.
• One SELECT, not three. targetSourceItem, materialNumber, and loQuantity come from the same rows of tblBillOfMaterials with the same WHERE clause. Three queries means three round trips. Worse, it relies on three unordered result sets coming back in the same row order, because the original code pairs them by index.

Move the Logic Off the Tag's valueChanged Event

Tag event scripts share a small, fixed executor:

• 3 threads for valueChanged scripts across all tags on the gateway.
• A per-tag event queue of 5.

A script that makes several blocking database calls holds one of those three threads for the full round-trip time. If a few of these fire together, change events on unrelated tags queue up, overflow, and get dropped. Those scripts then see missedEvents set. Keep tag event scripts to single-digit milliseconds. They should not touch a database unless they go through the Store and Forward system.

Move the work to a project Gateway Tag Change event:

1. In the WDE_System project, open Gateway Events and add a Tag Change script.
2. Add [NP_HI_25L_WDE_ProcessProvider]NP/HI/25L/WDE/OrderBreakdown/... trigger tag paths as triggers. One script can list several trigger tags. You do not need a tag per query.
3. Put the database logic in a project library script. Call it from the event. Gateway events belong to the project, so they resolve library scripts and named queries without extra configuration.
4. Remove the database code from the tag's valueChanged.

If you must keep a tag event that calls project library code, set the Gateway Scripting Project under the gateway's Config > System > Gateway Settings. It tells project-less resources such as tags which project holds their scripts.

• Only one Gateway Scripting Project is allowed. Scripts used by tags from several projects must all live in that one project.
• This does not fix the thread-pool problem. The heavy work still runs on the tag executor.

# Gateway Events > Tag Change (project WDE_System)
if not initialChange and newValue.value == 1:
    OrderBreakdown.reserveMaterials()

Collapse the Reads and Batch the Inserts

This project library version keeps the two named queries, with Query Type fixed. It uses one tag read and one SELECT, and it logs at every stage. DB is a placeholder; replace it with your gateway connection name.

# Project library: OrderBreakdown (project WDE_System)
PROJECT = 'WDE_System'
DB = 'YourDbConnection'  # placeholder: gateway database connection name
ROOT = '[NP_HI_25L_WDE_ProcessProvider]NP/HI/25L/WDE/OrderBreakdown/PAS-X/WDEDOWN/'
log = system.util.getLogger('OrderBreakdown')

def reserveMaterials():
    paths = [
        ROOT + 'MatFlow_MATERIAL_CHECK_MESSAGE/shopFloorOrderId',
        ROOT + 'Transaction_DOWNLOAD/targetMaterialNumber',
        ROOT + 'Transaction_DOWNLOAD/targetBatchSize',
    ]
    orderId, targetMat, batchSize = [qv.value for qv in system.tag.readBlocking(paths)]

    bom = system.db.runPrepQuery(
        '''SELECT [targetSourceItem], [materialNumber], [loQuantity]
           FROM [NP_HI_25L_WD_DB].[dbo].[tblBillOfMaterials]
           WHERE [targetMaterialNumber] = ? AND [targetBatchSize] = ?''',
        [targetMat, batchSize], database=DB)
    log.infof('order=%s mat=%s batch=%s bomRows=%d', orderId, targetMat, batchSize, len(bom))

    lc = {'localOrderShopFloorOrderId': orderId, 'lifeCycleName': 'LOCAL_ORDER',
          'lifeCycleStatus': 'RESERVED', 'operatorComment': 'NONE',
          'createdBy': 'NVML', 'archived': 0}
    n = system.db.runNamedQuery(PROJECT, 'NP/HI/25L/WD/OrderBreakdown/Insert_life_cycle', lc)
    log.infof('Insert_life_cycle rows=%s', n)

    for row in bom:
        p = {'loShopFloorOrderId': orderId, 'bomTargetMaterialNumber': targetMat,
             'bomTargetBatchSize': batchSize, 'bomTargetSourceItem': row['targetSourceItem'],
             'bomMaterialNumber': row['materialNumber'], 'reservedQuantity': row['loQuantity'],
             'unitOfMesurementId': 'kg ac.ing.', 'createdBy': 'NVML', 'archived': 0}
        n = system.db.runNamedQuery(PROJECT, 'NP/HI/25L/WD/OrderBreakdown/Insert_Material_Reservation', p)
        log.debugf('Insert_Material_Reservation mat=%s rows=%s', row['materialNumber'], n)

For larger BOMs, replace the per-row loop with one multi-row INSERT through system.db.runPrepUpdate. This helper builds the statement and the flat value list:

def build_insert_string(table, data, columns=None):
    # data: list of dicts, keys = destination column names
    if not data:
        return None, None
    if columns is None:
        columns = data[0].keys()
    marks = '({})'.format(','.join('?' for _ in columns))
    marks = ','.join(marks for _ in data)
    q = 'insert into {} ({}) values {}'.format(table, ','.join(columns), marks)
    values = [row[c] for row in data for c in columns]
    return q, values

The dict keys must be the table's column names, not the named query parameter names. The parameter names above (loShopFloorOrderId, unitOfMesurementId, and so on) only match the columns if whoever built the named query named them that way. Check the INSERT text inside Insert_Material_Reservation before you reuse the keys. SQL Server also limits parameters per statement, so split very large inserts into chunks.

Pick One Transaction Type, Then Wrap It

Named query transactions (system.db.beginNamedQueryTransaction(project, database)) are separate from the transactions used by runPrepQuery and runPrepUpdate. Mixing the two does not produce one atomic unit. Opening a tx_id and never passing it to any call does nothing, and it leaves an open transaction on the connection.

If the life-cycle row and its reservations must commit together:

1. Choose one family. With a bulk insert in the mix, use regular prep queries for both writes.
2. Open the transaction, pass tx= to every write, commit on success, roll back on any exception, and close it in finally.
3. Set the isolation level and timeout per the system.db.beginTransaction manual page. Do not leave a transaction open across a slow step.

tx = system.db.beginTransaction(database=DB)
try:
    # life-cycle INSERT as a prep update (write the SQL from the Insert_life_cycle named query text)
    system.db.runPrepUpdate(lifeCycleSql, lifeCycleArgs, database=DB, tx=tx)
    q, v = build_insert_string('dbo.YourReservationTable', reservationRows)  # placeholder table
    if q:
        system.db.runPrepUpdate(q, v, database=DB, tx=tx)
    system.db.commitTransaction(tx)
except:
    system.db.rollbackTransaction(tx)
    log.error('Reservation transaction rolled back', )
    raise
finally:
    system.db.closeTransaction(tx)

This also removes the partial-write state that the original fault created: a committed life-cycle row with zero reservations.

Verify With the Gateway Log and Row Counts

1. Open the gateway's log viewer and filter on the OrderBreakdown logger. Raise it to DEBUG if you want the per-row lines.
2. Trigger once. Confirm the bomRows count is greater than 0. If it is 0, the loop is skipped by design, and the problem is in the tag values or the BOM data, not the inserts.
3. Confirm Insert_life_cycle rows=1. A number instead of an exception proves the Query Type change was saved and deployed.
4. Count reservation rows in the database for that shopFloorOrderId. The count must equal bomRows.
5. Trigger a second time and check the log for missedEvents warnings on other tags. There should be none once the work is off the tag executor.
6. Force a failure, for example by pointing at a nonexistent named query path. Confirm the exception shows in the gateway log and, if you use the transaction version, that no partial rows remain.

Skip These Time-Wasters

• Declaring a named query bug. Multiple named queries in one script work. Check Query Type before anything else.
• Trusting the script console. It runs in a different scope with a different runNamedQuery signature. A pass there proves nothing about a tag or gateway event.
• Editing without saving. The gateway runs the saved project. An unsaved Query Type change looks exactly like a change that didn't help.
• Relying on the Testing tab for writes. An INSERT under Query type still writes the row there, so the defect stays hidden.
• Adding a tag per query. This spreads the same blocking work across more tag events and makes the thread-pool problem worse. One Gateway Tag Change script with several trigger paths covers it.
• Pairing three result sets by index. Separate SELECTs without ORDER BY give no row-order guarantee. Pull all columns in one query.
• Moving code to a library but keeping the tag event. Without a Gateway Scripting Project, the tag cannot find the library, and nothing runs.
• Swallowing exceptions. A bare except: pass around the named query calls recreates the original symptom: silent partial writes. Log, then re-raise or roll back.

FAQ