Resolving system.db.runPrepUpdate INSERT Errors in Ignition

system.db.runPrepUpdate accepts SQL text containing ? placeholders plus a list of values. The statement fails when the VALUES clause holds quoted names like 'user', 'timestamp', 'sensor_number', 'notes' instead of placeholders. The fix that works:

insertQuery = \"INSERT INTO sensor_notes (user, timestamp, sensor_number, notes) VALUES (?, ?, ?, ?)\"\nsystem.db.runPrepUpdate(insertQuery, [user, timestamp, sensor_number, notes], 'db')

The procedure below builds the working insert one hop at a time. Each step ends with a check before you move to the next.

Where does the INSERT travel before it reaches the table?

Follow the packet. A button script in a Vision client does not open its own database socket. The request passes through several hops, and each one can reject it for a different reason.

The original INSERT ran cleanly in the MySQL client, so the database hop is known good for literal values. That points the fault at the scripting and binding hops.

Check: open the full exception text, including the details or stack trace in the client error popup. Note whether it comes from Jython (script parse), from the Gateway (connection), or from the JDBC driver or database (SQL/binding).

Does the Gateway recognize the connection name?

The third argument, 'db', must match the name of a database connection configured on the Gateway. Matching the schema or database name inside MySQL is not enough. The Gateway matches this string against its own connection list.

1. Open the Gateway web interface and go to the database connections page.
2. Confirm that a connection named exactly db exists and shows a valid status.
3. If the project has a default database set, you may omit the argument. Passing the name explicitly removes ambiguity while commissioning.

Check: run a trivial read against the same connection from the Script Console, such as a SELECT against sensor_notes. If rows come back, the connection hop is proven.

How must the SQL text look for a prepared statement?

A prepared statement separates the SQL text from the data. The driver compiles the text once, then binds each element of the argument list, in order, to one ? marker. The driver quotes and escapes each value itself, based on its type.

With VALUES ('user', 'timestamp', 'sensor_number', 'notes'), the text contains zero placeholders while the list carries four arguments. The driver has nowhere to bind the list, so the call throws. Even if it ran, it would insert the literal words user, timestamp, and so on. Jython variable names are never expanded inside a SQL string.

Never wrap ? in quotes. A quoted '?' is a one-character string literal, not a parameter marker. The argument order in the list must match the column order in the INSERT.

Check: count the ? markers and the list elements. They must be equal.

Are the arguments values, or tag path strings?

Look at the lines that define the arguments:

user = \"[System]Client/User/Username\"\ntimestamp = \"[System]Client/System/CurrentDateTime\"

These assign the tag path text itself to the variables. With placeholders in place, the insert will now succeed, but the user column receives the string [System]Client/User/Username rather than the logged-in operator's name. The timestamp column gets a path string, not a date.

Read the tag values instead:

• Use the tag-read function from the scripting reference for your Ignition version.
• Take the .value of the returned qualified value.
• For the timestamp, a date object bound to a ? lets the driver write a native date/time value.

# Values bound to the placeholders\nuser = <value read from [System]Client/User/Username>\ntimestamp = <value read from [System]Client/System/CurrentDateTime>\nsensor_number = event.source.parent.sensorNumber\nnotes = event.source.parent.getComponent('Text Area').text\n\ninsertQuery = \"INSERT INTO sensor_notes (user, timestamp, sensor_number, notes) VALUES (?, ?, ?, ?)\"\nrows = system.db.runPrepUpdate(insertQuery, [user, timestamp, sensor_number, notes], 'db')

Match the argument types to the column types:

• sensor_number: if the column is numeric, pass the custom property as a number, not a formatted string.
• notes: text from the Text Area binds as a string. Apostrophes in operator notes need no escaping, because the driver handles quoting.

Check: before the INSERT line, temporarily print each variable and its type. Confirm you see an operator name and a date, not bracketed paths.

Which characters and column names still break it?

On MySQL, the column list (user, timestamp, sensor_number, notes) already works, since it ran in the MySQL client. Carry the identifier-quoting check only if the table moves to another database engine.

Check: the script compiles in the Script Console without a syntax error.

How do you prove the row landed end to end?

1. Capture the return value. runPrepUpdate returns the number of affected rows, and a single-row INSERT should return 1.
2. Trigger the script from the actual client button, not only from the Script Console. The [System]Client/... tags carry that client's session values.
3. Query sensor_notes from the MySQL client and read back the new row.
4. Confirm that user holds the logged-in operator's name and that timestamp is a real date close to the click time.
5. Confirm that sensor_number matches the parent container's sensorNumber and that notes matches the Text Area text exactly, including any apostrophes.
6. Enter a note containing a single quote and repeat steps 1–5. A clean insert proves the driver, not string concatenation, is doing the quoting.

FAQ