In Ignition, system.db.runPrepQuery always returns a PyDataset, even when the SELECT yields one column and one row. Passing that object straight into system.dataset.setValue writes the whole dataset into the cell, not the string you selected. For a single value, call system.db.runScalarPrepQuery, which returns the first column of the first row as a plain value. If you keep runPrepQuery, pull the value out by row index and column name first.
How do you read what the table cell is showing?
Look at the data path before touching the table. Three stages sit between the database and the cell: the query result, the script variable that holds it, and the dataset column that receives it. The wrong value enters at one of those stages, and the cell only shows the last one.
The failing script ran this sequence:
InsertAssist = ('SELECT matAssist FROM usermaterials WHERE materialName = ?')
args2 = [selected_material]
assist = system.db.runPrepQuery(InsertAssist, args2)
if selRow != -1 and selCol != -1:
newData = system.dataset.setValue(table.data, selRow, selCol2, assist)
table.data = newData| Signal | Source | Wrong-value symptom |
|---|---|---|
assist |
system.db.runPrepQuery |
Cell shows a dataset object or its text representation instead of the matAssist name |
assist (empty result) |
No row matches materialName
|
Scalar call returns None. Indexing assist[0] raises an index error. |
assist (several rows) |
Duplicate materialName in usermaterials
|
Scalar call silently returns the first row, which can be the wrong assist value |
selected_material |
Selection component or variable feeding the ? parameter |
Empty cell or wrong material because the parameter never matched |
selRow / selCol2
|
Table selection logic | Value lands in the wrong cell, or the write runs when no column is valid |
| Target column type |
table.data column definition |
Coercion error or unexpected text when the value type does not match the column |
Why does runPrepQuery hand back a dataset instead of a value?
The query functions split on what they return, not on what the SQL says. runPrepQuery is built for result sets of any size, so it wraps every result in a PyDataset: a row-and-column container you can iterate and index. A one-row, one-column result is still a 1x1 dataset. The function does not collapse it to a scalar for you.
system.dataset.setValue accepts whatever object you pass as the value and writes it into the target cell. It does not reach inside a dataset to find the first element. The table then renders the object it received. That is why the cell shows a dataset instead of a name, even though the SQL and the parameter binding are both correct. Tuning the query will not change this. The problem sits in how the script handles the return type.
runScalarPrepQuery uses the same prepared-statement binding (the ? placeholder plus an argument list), but it returns only the value in the first column of the first row. When no row matches, it returns None.
How do you fix the script?
- Replace
system.db.runPrepQuerywithsystem.db.runScalarPrepQueryand keep the same query string and argument list. - Guard against
Noneso a non-matching material does not blank or corrupt the cell. - Check the same column variable you write to. The original guard tests
selColbut writes toselCol2, so fix whichever one is wrong. - Write the modified dataset back to
table.data. Datasets are immutable, andsetValuereturns a new copy.
query = 'SELECT matAssist FROM usermaterials WHERE materialName = ?'
assist = system.db.runScalarPrepQuery(query, [selected_material])
if assist is not None and selRow != -1 and selCol2 != -1:
table.data = system.dataset.setValue(table.data, selRow, selCol2, assist)If you need more than one column from the same row later, keep runPrepQuery and extract explicitly:
assist = system.db.runPrepQuery(query, [selected_material])
# index row 0, column by name
if len(assist) > 0:
val = assist[0]['matAssist']
# or iterate rows
for row in assist:
val = row['matAssist']The column name in the index must match the name the SELECT returns. If you alias the column in SQL, use the alias.
How do you verify the value landed correctly?
- Run the query in the Script Console with a known material name and print
type(assist)and the value. The type should be a plain value such as a string, not a dataset. - Run the same SELECT in the Database Query Browser against the same datasource. Confirm that exactly one row comes back for that
materialName. - Trigger the script from the Vision window, then read the target cell directly with
table.data.getValueAt(selRow, selCol2)and print it. Checking the stored value this way rules out a renderer or formatting issue on the table. - Test a material name that does not exist. The cell should stay unchanged and no error should appear in the client console.
What trips people up on this pattern again?
Non-unique lookup keys. The scalar call hides duplicates by returning the first row. If materialName is not unique in usermaterials, add a constraint or an ORDER BY so the result is deterministic.
Mismatched guard and write indices. Checking one column variable and writing to another passes the guard even when the write target is invalid. That produces an out-of-range error or writes to the wrong column.
Column type mismatch. The target column in table.data has a fixed type. Writing a string into a numeric column, or a number into a string column, either fails or coerces. Check the column type in the dataset editor before blaming the query.
Indexing an empty dataset. assist[0] on a zero-row PyDataset throws. Always check len(assist) first when you use the full-dataset call.
Wrong datasource. Without a database argument, the query runs against the project default datasource. If the table lives in a different database, pass the datasource name explicitly, or the lookup returns nothing.
FAQ
Why does system.db.runPrepQuery return a dataset for a single value?
It always wraps results in a PyDataset regardless of row or column count. Use system.db.runScalarPrepQuery to get the first column of the first row as a plain value, or index the dataset with assist[0]['columnName'].
Why does runScalarPrepQuery return None in my Vision script?
No row matched the WHERE parameter, or the query ran against a different datasource than you expected. Run the same SELECT with the same argument in the Database Query Browser, and check for trailing spaces or case differences in the lookup key.
Why does system.dataset.setValue not change my Vision table?
Datasets are immutable, so setValue returns a new dataset rather than modifying the original. Assign the result back with table.data = system.dataset.setValue(...), and confirm that the row and column indices point to a valid cell.
When should I escalate an Ignition query-to-table problem to support?
Escalate once the Script Console shows the correct plain value and type, the Query Browser returns exactly one row, and the cell still stores something different after assignment. At that point, collect the gateway and client logs along with the exact Ignition version, and open a case through Inductive Automation's official support channel.