Troubleshooting Ignition SQL Queries for PLC Arrays

The Ignition table shows all 600 PLC timeout values, but the operator needs only valves whose timeout exceeds 3. The decisive issue is not the comparison operator; it is whether each valve is stored as a queryable row or as one of 600 separate columns. Trace the signal from the DINT array, through the transaction group, into MySQL before changing the display query.

Why do the usual SQL fixes fail?

Several plausible fixes address the visible symptom without correcting the data path:

• SELECT * FROM valve_timeout returns every stored row and column because it contains no predicate.
• SELECT Valve_Timeout_1_ FROM valve_timeout WHERE Valve_Timeout_1_ > 3 works for one physical column, but it cannot inspect the other 599 columns.
• Adding a 600-column OR expression can identify records containing at least one value above 3, but SQL still returns the selected record shape. It does not automatically convert the matching cells into one result row per valve.
• Changing the threshold does not repair an incorrect tag-to-table mapping. Query tuning does not fix acquisition structure.
• Adding WHERE to a DELETE statement does not resolve an executeQuery() error. That error comes from using a read-query execution path for a data-modification statement.
• Correcting the misspelling DELTEE to DELETE removes the syntax error, but the command still must run through the update-query API.

Look at the trend first. Confirm whether one timestamp contains 600 value columns, or whether one acquisition produces 600 rows identified by row_id. That observation determines the correct query.

What causes the 600-column filtering problem?

A SQL result has a fixed set of columns. A WHERE clause accepts or rejects complete rows; it does not select individual cells from a wide record. If one database row contains Valve_Timeout_0_ through Valve_Timeout_599_, a condition on Valve_Timeout_1_ filters only that column's value. An expression covering all 600 columns can retain the database row, but the client still receives whichever columns appear in the SELECT list.

The query becomes direct when the table stores one valve observation per row:

• An identifier maps the row to array element 0 through 599.
• One value column stores the DINT timeout.
• t_stamp records the acquisition time when history is required.
• A block identifier separates acquisition cycles when multiple 600-row snapshots share the table.

With that shape, WHERE timeout_value > 3 operates on every valve through one predicate. The display no longer needs knowledge of 600 physical value columns.

Where can the value become wrong?

Measure at each boundary before adjusting the next one. A correct SQL filter cannot repair an array index shifted during tag mapping, and a display label cannot prove that row_id matches the intended valve.

How should the transaction group store the array?

Configure the array as a block so that repeated items become rows. The tag range Valve_Timeout_0_ through Valve_Timeout_599_ contains exactly 600 elements. A block range of 0 through 600 would contain 601 positions, so use 0 through 599 unless the PLC array actually includes another element.

1. Create a block group for the valve-timeout items.
2. Add the 600 tags in deterministic array-index order, starting with Valve_Timeout_0_ and ending with Valve_Timeout_599_.
3. Enable storage of the row identifier. Treat row_id as the valve index only after the first, middle, and last mappings pass verification.
4. Select insert changed rows when the requirement is to record changed values. This was the table action that produced filterable rows in the working configuration.
5. If the table must retain successive blocks, store a block identifier so queries can distinguish one acquisition set from another.
6. If the requirement is only the latest 600 values, select the transaction-group action intended to update the current block rather than accumulating history. Confirm the exact update/select behavior in the manual for the installed Ignition release before changing a production group.
7. Change one known PLC element, execute the group, and inspect the resulting row_id, value, and t_stamp.

insert changed rows represents change history, not necessarily a complete current snapshot. If unchanged valves must remain visible alongside recently changed valves, define whether the display needs latest-per-valve state or only change events. That choice controls whether the query needs a current-state table, a block selection, or latest-row logic.

How do I query only timeout values greater than 3?

For the row-oriented result produced by the working block configuration, filter the common value column and return its row identity:

SELECT row_id, Valve_Timeout_0_, t_stamp
FROM valve_timeout
WHERE Valve_Timeout_0_ > 3
ORDER BY row_id;

Here, Valve_Timeout_0_ is the stored block-value column, while row_id identifies which block item supplied that value. Verify this mapping instead of interpreting the column name as proof that every returned row came from PLC element 0.

For a MySQL-backed table, a readable valve label can be derived after the index mapping is proven:

SELECT
  CONCAT('Valve_Timeout_', row_id, '_') AS valve_tag,
  Valve_Timeout_0_ AS timeout_value,
  t_stamp
FROM valve_timeout
WHERE Valve_Timeout_0_ > 3
ORDER BY row_id;

If the threshold comes from operator input, pass it through the prepared-query form supported by the installed Ignition release. Do not concatenate user-entered text into SQL.

If the table still has one physical column per valve, the short-term alternative is to reshape it with one branch per column and then filter the resulting value field. The pattern below shows three branches; extending it to all 600 is mechanically possible but difficult to maintain:

SELECT valve_tag, timeout_value
FROM (
  SELECT 'Valve_Timeout_0_' AS valve_tag,
         Valve_Timeout_0_ AS timeout_value
  FROM valve_timeout
  UNION ALL
  SELECT 'Valve_Timeout_1_', Valve_Timeout_1_
  FROM valve_timeout
  UNION ALL
  SELECT 'Valve_Timeout_2_', Valve_Timeout_2_
  FROM valve_timeout
) AS valve_values
WHERE timeout_value > 3;

Use that only as a migration bridge. Row-oriented storage keeps the comparison, indexing strategy, display binding, and future array expansion tractable.

Why does DELETE fail from the Ignition button?

DELETE is a data-manipulation statement. In the reported Ignition 7.6.3 environment, routing it through the read-query path produced:

Can not issue data manipulation statements with executeQuery().

The logged operation showed database MySQL and a 5000ms query context. The failure does not mean MySQL requires a WHERE clause, and it does not prohibit deletion from Ignition. It means the execution function was wrong.

Run the command from the button's executable script location with the update-query API:

system.db.runUpdateQuery("DELETE FROM valve_timeout")

system.db.runQuery is for result-returning queries such as SELECT. system.db.runUpdateQuery is for UPDATE, INSERT, and DELETE, and it returns the number of affected rows.

No WHERE clause means every row in valve_timeout is deleted. Put the command behind deliberate operator authorization and confirmation. If the goal is to clear only one acquisition block, one time range, or one valve, add a predicate using the verified block identifier, timestamp, or row identifier.

How do I verify the complete correction?

1. Set three controlled PLC values: one below 3, one equal to 3, and one above 3. Record their array indexes.
2. Trigger the transaction group and inspect raw rows with an unfiltered query limited to the relevant acquisition set.
3. Confirm that each tested row_id maps to the expected PLC index and that t_stamp belongs to the test acquisition.
4. Run the > 3 query. Only the value above 3 should appear; a value equal to 3 must not pass a strict greater-than comparison.
5. Change a previously high value below the threshold. With insert changed rows, verify whether the display is intentionally showing change history or the current value. If it needs current state, select the latest record for each valve or use the table action designed for the current block.
6. Test the clear button on a non-production dataset. Capture the affected-row count returned by system.db.runUpdateQuery, then run SELECT * FROM valve_timeout to confirm the intended scope is empty.
7. Restart or retrigger the acquisition path and confirm that new rows populate with correct identifiers, values, and timestamps.

A correct result requires agreement across all three stages: the PLC supplies the expected DINT, the block mapping assigns the correct row identity, and the SQL predicate evaluates the stored value column.

FAQ