Why does Ignition runPrepQuery throw Unknown column errors?

Read the Caused-by line before touching the syntax

The usual first move is to rewrite the triple-quoted SQL string: move the closing quotes, add slashes, re-indent. That doesn't help, because Python string syntax was never the problem. A Jython syntax error stops the script before any query leaves the Designer. If the traceback contains AbstractDBUtilities.runPrepQuery and GatewayInterface.sendMessage, the string was valid. It reached the Gateway, went to the MySQL JDBC driver, and the database rejected it.

Scroll past the Java frames to the last Caused by. For this failure it reads:

Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'production_date' in 'where clause'

A second attempt returned the same error for 'timestamp'. The Error executing system.db.runPrepQuery(...) line above it echoes the exact statement, datasource and parameter list that ran:

SELECT `CKGSunenergy_1MW_CE` FROM Plants_Cumulative_Energy
WHERE MONTH(production_date) = ? AND YEAR(production_date) = ?
ORDER BY production_date ASC LIMIT 1 , IgnitionDB, [12, 2025]

Debug against that echo. It shows what actually ran, which may differ from what the editor shows.

Traceback layer	What it tells you	Action
File "<input>", line N	Script line that made the call	Locate the call; don't edit yet
Error executing system.db.runPrepQuery(...)	Final SQL text, datasource, parameter values	Copy it out and compare against the table
GatewayException	Gateway passed the error through from the database	Not a Designer or network fault
SQLSyntaxErrorException (com.mysql.cj.jdbc)	MySQL parsed the statement and refused it	Fix identifiers in the SQL

Remove the try: ... except Exception as e: print(...) wrapper while you commission. In Jython, errors from system.db calls are Java exceptions, and a plain except Exception doesn't reliably catch them. When it does catch one, it prints a single line and drops the Caused by chain you need.

Check: you can name the rejected identifier and the parameter list from the echo. Stop editing Python until every identifier in the SQL exists in the table.

Pull the real column list from the table

A query that already works tells you the datasource name and table name are correct:

query = "SELECT * FROM Plants_Cumulative_Energy LIMIT 5"
results = system.db.runQuery(query, "IgnitionDB")

Extend it to print the headers and a few rows in the Script Console:

ds = system.db.runQuery("SELECT * FROM Plants_Cumulative_Energy LIMIT 5", "IgnitionDB")
print(ds.getColumnNames())
for row in ds:
    print(list(row))

You can also run SHOW COLUMNS FROM Plants_Cumulative_Energy in the Database Query Browser against IgnitionDB. That also returns each column's data type.

Next, decide which table layout you have. The first script assumed a plant_name column and a cumulative_energy column. The statement that actually ran selected `CKGSunenergy_1MW_CE`, which is a plant-named column. Only one of those layouts matches the table, and the choice changes how the plant dropdown reaches the SQL.

Layout	What the columns look like	How plant selection enters the SQL	Parameter type
Long (one row per plant per reading)	timestamp column, plant_name, cumulative_energy	WHERE plant_name = ?	Value parameter; safe, prepared
Wide (one column per plant)	timestamp column, CKGSunenergy_1MW_CE, other plant columns	The column name itself changes	String substitution; must be whitelisted

Drop the IgnitionDB. prefix from the table name as well. IgnitionDB is the name of the Ignition datasource, and it doesn't have to match the MySQL schema name. The working query has no prefix and runs.

Check: write down the exact name and type of the date/time column. Neither production_date nor timestamp is it. Confirm every identifier in your SQL appears in the column list with identical spelling. Stop here if the table has no date/time column at all. You can't filter by month until the logging side records one.

Take the memory tags out of the selection path

Holding dropdown selections in memory tags such as [Default]HistoricalData/Month, /Year and /Plant works fine with one Designer session open. It breaks once a second client connects. Tags live on the Gateway, so there is one value shared by every Perspective session. If operator A picks March and operator B picks July, the last write wins, and both clients query using the other person's selection.

Store the selections in Perspective custom properties instead. Each session gets its own copy.

1. Select the view's root in the Project Browser. In the Property Editor, open the CUSTOM section.
2. Add three properties: month, year, plant.
3. Give each a sensible default, for example the current month and year and the first plant. The view then opens showing a valid result.
4. If other views need the same selection, put the properties on the session instead: Project Browser → Perspective → Session Props → CUSTOM. They still stay scoped to one session.

After this change, delete the HistoricalData memory tags or leave them unused. Nothing in the view should reference them.

Check: the three properties appear under CUSTOM with defaults, and a project-wide search for HistoricalData/ finds nothing in this view.

Bind each dropdown bidirectionally to its property

1. On each Dropdown, bind props.value with a Property binding to view.custom.month, view.custom.year or view.custom.plant.
2. Tick Bidirectional. The operator's selection writes to the property, and the property's default seeds the dropdown when the view opens.
3. Set month options to numeric values 1–12 with text labels. Set year options to numeric years. This way the parameters arrive as integers, matching the [12, 2025] shape that reached MySQL.
4. Plant options depend on the layout. For a long table, use the stored plant_name text as the value. For a wide table, use the exact column name, for example CKGSunenergy_1MW_CE, as the value and a readable plant name as the label.

Check: drop three temporary Labels on the view, bound to the three custom properties. In Preview mode, change each dropdown and watch its label follow. If a label shows a quoted "12" where you expect 12, fix the option values before going further.

Build the Named Query with SQL only

A common mistake is pasting the Python script, with its if result_first_day: blocks and print calls, into the Named Query authoring tab. A Named Query holds SQL plus parameter definitions and nothing else. Anything in the query text goes to MySQL, and MySQL can't run Python.

Create the Named Query with Datasource IgnitionDB and Query Type Scalar Query. The result is one number, so a scalar keeps the binding simple. In the SQL below, replace date_col with the timestamp column name you found in the column list.

Long layout:

SELECT
  (SELECT cumulative_energy FROM Plants_Cumulative_Energy
    WHERE plant_name = :plant
      AND date_col >= MAKEDATE(:year, 1) + INTERVAL (:month - 1) MONTH
      AND date_col <  MAKEDATE(:year, 1) + INTERVAL :month MONTH
    ORDER BY date_col DESC LIMIT 1)
  -
  (SELECT cumulative_energy FROM Plants_Cumulative_Energy
    WHERE plant_name = :plant
      AND date_col >= MAKEDATE(:year, 1) + INTERVAL (:month - 1) MONTH
      AND date_col <  MAKEDATE(:year, 1) + INTERVAL :month MONTH
    ORDER BY date_col ASC LIMIT 1) AS monthly_energy

Wide layout: replace cumulative_energy with `{plantColumn}` and remove the plant_name = :plant lines.

Parameter	Type	Referenced as	Notes
month	Value, Int4	:month	Prepared; can be reused in the text
year	Value, Int4	:year	Prepared
plant	Value, String	:plant	Long layout only
plantColumn	QueryString	{plantColumn}	Wide layout only; inserted as raw text

A column name can't be a ? or :param value. A prepared parameter becomes a string literal, so SELECT ? returns the text CKGSunenergy_1MW_CE, not the meter reading. QueryString parameters get around this by inserting raw text, which also makes them an injection path. Only feed them from the fixed dropdown option list. Never feed them from a free-text field.

Check: open the Named Query's Testing tab, enter 12, 2025 and a known plant, and run it. You should get a number, or NULL for a month with no rows. An Unknown column error here means an identifier is still wrong. Go back to the column list.

Fix the month filter and the baseline

MONTH(date_col) = ? AND YEAR(date_col) = ? returns correct rows, but it wraps the column in functions. MySQL then can't use an index on that column and scans every row. That's fine for a few thousand rows and slow on years of logging. The range form in the query above, >= first of month and < first of next month, uses an index and handles December rolling into January without special cases.

Monthly energy is calculated as the last cumulative reading in the month minus the first. Decide whether that is the number the plant wants:

How readings are logged	Last − first within month gives	Better baseline
Snapshot at start of each day	Misses the final day's production	First reading of the next month as the end value
Snapshot at end of each day	Misses the first day's production	Last reading of the previous month as the start value
Frequent interval logging	Close; misses only the gap between months	Previous month's last reading

Watch for counter resets and meter replacements too. They produce a negative or undersized result for that month. A negative result points to a data problem, not a query problem.

Check: for one plant and one month, pull the raw rows from the Database Query Browser, calculate the difference by hand, and compare it with the Testing tab result.

Bind the result display to the Named Query

1. On the result Label, bind props.text with a Query binding and select the Named Query.
2. Bind each parameter to its custom property: month → {view.custom.month}, year → {view.custom.year}, and plant or plantColumn → {view.custom.plant}.
3. Leave polling off. The binding re-runs whenever a parameter value changes, so the dropdown change is the trigger and you don't need a button or script.
4. Add a script transform that returns a readable "No data" when the value is None and formats numbers with the engineering unit otherwise.

If you need production running tonight, a Table component bound to a Named Query that returns the month's raw rows for the selected plant is an acceptable stopgap. Operators can read the first and last values off the table, and there's no scripting to maintain. Get it running, then fix it properly with the scalar binding above.

Check: in a Perspective session, change the month. The value should update without a click, and the temporary labels from the dropdown step should match the parameters you expect.

If you must script it, fix these five things

Sometimes a button script or report needs the value in Python. The original script had several problems, and these are the corrections:

• Read selections from the view. In Perspective, use self.view.custom.month, not tags. If you still read tags somewhere else, use one system.tag.readBlocking() call with a list of paths instead of three single reads.
• Use scalar calls. One row with one column fits system.db.runScalarPrepQuery(), so you don't have to index result[0]["col"].
• Keep one query string. Format in only the sort direction and the whitelisted column name, and pass user values as prepared arguments.
• Test for None explicitly. A reading of 0 is valid, so if result: treats it as missing. Also, all(a, b, c) raises a TypeError, because all() takes a single iterable.
• Name the datasource and catch the Java exception. Pass "IgnitionDB" as the last argument. Then catch java.lang.Exception and re-raise it so the Caused by chain still reaches you.

from java.lang import Exception as JavaException

# Script Console test: literals stand in for view.custom values
month, year, plant = 12, 2025, 'CKGSunenergy_1MW_CE'
ALLOWED = ['CKGSunenergy_1MW_CE']  # list every plant column here
if plant not in ALLOWED:
    raise ValueError('Unknown plant column: %s' % plant)

query = '''SELECT `{col}` FROM Plants_Cumulative_Energy
WHERE MONTH(date_col) = ? AND YEAR(date_col) = ?
ORDER BY date_col {order} LIMIT 1'''

try:
    first = system.db.runScalarPrepQuery(query.format(col=plant, order='ASC'), [month, year], 'IgnitionDB')
    last = system.db.runScalarPrepQuery(query.format(col=plant, order='DESC'), [month, year], 'IgnitionDB')
except JavaException as e:
    print(e.getCause())
    raise

if first is None or last is None:
    print('Energy data for the period is incomplete')
else:
    print('Monthly energy: {}'.format(last - first))

If you keep tags for a test harness, read them like this:

paths = ['[Default]HistoricalData/{}'.format(p) for p in ('Month', 'Year', 'Plant')]
month, year, plant = [qv.value for qv in system.tag.readBlocking(paths)]
if None in (month, year, plant):
    raise ValueError('One or more selections are empty')

The Script Console runs in Designer scope and can't see a view's custom properties, which is why the test uses literals.

Check: the console prints the same number the Named Query Testing tab returned for that month.

Verify end to end

1. Open the view in two separate browser sessions. Select different months in each and confirm each shows its own result. This proves the memory tags are gone from the path.
2. Select a month with known data and compare the displayed value against the hand calculation from the baseline step.
3. Select a future month and confirm the label shows the "No data" text rather than an error overlay.
4. Change the plant and confirm the value changes. On a wide table, check that the Named Query is picking the right column and not returning a literal string.
5. Close and reopen the session and confirm the defaults produce a valid result on first load.
6. Check the Gateway status logs for any SQLSyntaxErrorException logged while you ran these steps.

FAQ