Ignition runPrepUpdate MySQL Syntax Error: Fix the SET List

Traceback Layers: Script Line vs SQL Line

Before anything else, confirm which layer threw the error. An Ignition system.db.runPrepUpdate failure produces a stacked traceback. Each layer reports its own line numbering, and mixing them up sends you looking in the wrong place.

"Line 1" does not point at the first line of the Python script. The SQL statement is one line, so every syntax error in it reports line 1. The Python layer already told you the call site (line 10). The fault sits inside the SQL string.

Check: Do not move on until you have isolated the innermost caused by entry. Here that is the MySQLSyntaxErrorException. Everything above it is wrapping.

The "near" Fragment and the Missing Separator

MySQL's syntax error message quotes the SQL text starting at the token where the parser gave up. The defect almost always sits immediately before the quoted fragment, not inside it. In this failure the fragment is:

near 'resolution = 'Reset Antennae and made sure cows could not get to it', timespent ' at line 1

The parameter values appear already substituted into the fragment. The MySQL JDBC driver builds the final statement text with the bound values inlined before sending it, so the error shows literal data rather than ?. That is normal and does not mean the parameters were mishandled.

Look at what precedes resolution in the original query:

... sitelocation = ?, problem = ? resolution = ?, timespent = ? ...

There is no comma between problem = ? and resolution = ?. In an UPDATE ... SET clause, each column = value assignment must be separated by a comma. The parser accepts problem = 'Bent Antennae' as a complete assignment. It then expects either a comma, WHERE, ORDER BY, LIMIT, or end of statement. Instead it finds a bare identifier resolution and fails at that point.

Check: Read the SQL string left to right and confirm exactly one comma between every pair of assignments and no comma before WHERE.

Corrected SET List

Insert the comma and leave everything else unchanged for the first test. That way the syntax fix is the only variable.

grp = event.source.parent.getComponent('Group')

clientname   = grp.getComponent('Text Field').text
dateofwork   = grp.getComponent('Text Field 1').text
progress     = grp.getComponent('Numeric Text Field').intValue
sitelocation = grp.getComponent('Text Field 2').text
problem      = grp.getComponent('Text Field 3').text
resolution   = grp.getComponent('Text Area').text
timespent    = event.source.parent.getComponent('Text Field 3').text

db = "DB"
updateQuery = """UPDATE workorders
   SET clientname = ?,
       dateofwork = ?,
       progress = ?,
       sitelocation = ?,
       problem = ?,
       resolution = ?,
       timespent = ?
 WHERE dateofwork = ?"""

rows = system.db.runPrepUpdate(updateQuery,
    [clientname, dateofwork, progress, sitelocation, problem, resolution, timespent, dateofwork],
    db)
print rows

Two changes beyond the comma, neither of which alters behavior:

1. Cache the Group container in grp. This shortens each line and makes a wrong container reference easy to spot.
2. Put one assignment per line inside the triple-quoted string. A missing trailing comma then shows as a visually broken column. MySQL treats the newlines as whitespace. After this change, the error line number will point at the actual SQL line if a syntax error recurs.

Check: Run the button. A non-negative integer printed to the console (or the Designer output window) confirms the statement parsed and executed. A new MySQLSyntaxErrorException means a second defect exists. Read its near fragment the same way.

Placeholder and Argument Alignment

runPrepUpdate binds arguments to ? placeholders strictly by position. A syntax fix that adds or removes a placeholder shifts every value after it. Confirm the count and order before trusting any result.

Eight placeholders map to eight arguments, so alignment is correct. Note the component references. problem reads 'Text Field 3' inside Group, while timespent reads a different 'Text Field 3' directly under the parent container. The logged values differ ("Bent Antennae" vs "3 hour"), so these are two separate components with the same name in different containers. That works, but it is fragile. Rename one (for example txtTimeSpent) so a future copy-paste cannot silently read the wrong field.

Check: Count ? characters in the query and the length of the argument list. They must be equal before you proceed.

WHERE Clause Key and Date Handling

The statement now parses, but the WHERE clause decides which rows change. Settle it before this screen goes into use.

• Non-unique key. WHERE dateofwork = ? updates every work order with that date. Two jobs on 6/4/2015 would both be overwritten with the same client, site, problem, and resolution. Filter on the table's primary key (typically an auto-increment ID column; read the actual name from the table definition). Carry that ID in a hidden component or custom property when the record loads into the form.
• Updating the key you filter on. The query sets dateofwork and filters on dateofwork using the same value. It can never change a work order's date. If the operator edits the date field, the WHERE clause looks for the new date and misses the original row. Filtering on the primary key removes this problem.
• Date format vs column type. dateofwork is passed as text in M/D/YYYY form. If the MySQL column is VARCHAR, the comparison is a plain string match. If the column is DATE or DATETIME, MySQL expects YYYY-MM-DD ordering. A 6/4/2015 string will not match stored dates reliably and can write a zero or invalid date. Check the column type with SHOW CREATE TABLE workorders. If it is a date type, use a Calendar or Popup Calendar component and pass its date value as the parameter so the driver binds a real date.
• Text vs numeric columns. timespent carries "3 hour", so that column must be a character type. progress is bound as an integer from intValue, which suits an INT column.

Check: The return value of runPrepUpdate is the affected-row count. For a single-record edit form it must be exactly 1.

Symptoms and Causes After the Syntax Fix

Copy-Edit Pitfall and a Separator-Proof Query Builder

A second script that "looks almost identical" and works is the usual trap. Visual comparison skips punctuation. Diff the two SQL strings character by character, or avoid hand-typed separators entirely. Generate the SET list from a column list so commas are always correct and the argument order always matches:

grp = event.source.parent.getComponent('Group')

fields = [
    ("clientname",   grp.getComponent('Text Field').text),
    ("dateofwork",   grp.getComponent('Text Field 1').text),
    ("progress",     grp.getComponent('Numeric Text Field').intValue),
    ("sitelocation", grp.getComponent('Text Field 2').text),
    ("problem",      grp.getComponent('Text Field 3').text),
    ("resolution",   grp.getComponent('Text Area').text),
    ("timespent",    event.source.parent.getComponent('Text Field 3').text),
]

setClause = ", ".join(["%s = ?" % f[0] for f in fields])
args = [f[1] for f in fields]

keyValue = grp.getComponent('Text Field 1').text   # replace with the primary key value
args.append(keyValue)

query = "UPDATE workorders SET " + setClause + " WHERE dateofwork = ?"
rows = system.db.runPrepUpdate(query, args, "DB")
print query
print rows

Column names come from a fixed list in the script, never from operator input. Only the values go through ? binding. Keep it that way so the query stays parameterized.

Check: The printed query shows six commas in the SET clause for seven columns and none before WHERE.

End-to-End Verification

1. Test the SQL outside the script first. Paste the corrected statement into the Designer's Database Query Browser against the DB datasource with literal values substituted. Confirm it executes without a syntax error.
2. Run a SELECT with the same WHERE clause and value. Confirm it returns exactly one row. If it returns zero or several rows, fix the key before running the update.
3. Trigger the button in a Designer preview or a client. Confirm the output console shows no traceback and the printed row count is 1.
4. Re-run the SELECT. Confirm every column holds the value typed into its intended component: client, date, progress 75, site, problem text in problem, resolution text in resolution, and "3 hour" in timespent.
5. Edit one field only (for example progress) and repeat. Confirm row count 1 and that only that column changed. This proves the argument order and the key filter together.

FAQ