java.sql.SQLSyntaxErrorException with PersistentRecord

I’m trying to use a PersistentRecord to store settings for a web service.

Here’s the record:

[code]public class TierInterfaceSettingsRecord extends PersistentRecord {

private static final long serialVersionUID = 5609419912765113584L;
public static final RecordMeta<TierInterfaceSettingsRecord> META = new RecordMeta<TierInterfaceSettingsRecord>(TierInterfaceSettingsRecord.class, "TierInterfaceSettings");
public static final IntField Port = new IntField(META, "Port").setDefault(2000);

public Integer getPort() {
	return getInt(Port);

public void setPort(Integer port) {
	setInt(Port, port);

public RecordMeta<TierInterfaceSettingsRecord> getMeta() {
	return META;


Here’s my gateway hook:

public class IgnitionCellTier extends AbstractGatewayModuleHook { private Logger logger; private WebServiceManager wsm; private GatewayContext context; /* (non-Javadoc) * @see com.inductiveautomation.ignition.gateway.model.GatewayModuleHook#setup(com.inductiveautomation.ignition.gateway.model.GatewayContext) */ @Override public void setup(GatewayContext context) { this.context = context; logger = Logger.getLogger(getClass()); try { this.context.getSchemaUpdater().updatePersistentRecords(TierInterfaceSettingsRecord.META); } catch (SQLException e) { logger.error("Unable to update persistent record for tier interface settings.",e); } } .... }

That logger records this exception:

java.sql.SQLSyntaxErrorException: unexpected token: )

Any ideas where I went wrong here?

Hmm, looks good to me. Can you provide the stack trace for that error?


Here you go:

java.sql.SQLSyntaxErrorException: unexpected token: ) at org.hsqldb.jdbc.Util.sqlException( at org.hsqldb.jdbc.Util.sqlException( at org.hsqldb.jdbc.JDBCStatement.fetchResult( at org.hsqldb.jdbc.JDBCStatement.executeUpdate( at org.apache.commons.dbcp.DelegatingStatement.executeUpdate( at com.inductiveautomation.ignition.gateway.localdb.hsql.DelegatingDataSource$DelegatingConnection$DelegatingStatement.executeUpdate( at com.inductiveautomation.ignition.gateway.localdb.DatasourceDBInterface.runUpdateQuery( at com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl$SingleConnectionDBInterface.runUpdateQuery( at com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl.updatePersistentRecords( at com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl.updatePersistentRecords( at com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl.updatePersistentRecords( at Source) at com.inductiveautomation.ignition.gateway.modules.ModuleManagerImpl$LoadedModule.setup( at com.inductiveautomation.ignition.gateway.modules.ModuleManagerImpl.setup( at com.inductiveautomation.ignition.gateway.SRContext.startupInternal( at com.inductiveautomation.ignition.gateway.redundancy.RedundancyManagerImpl.startup( at com.inductiveautomation.ignition.gateway.SRContext.asyncInit( at com.inductiveautomation.ignition.gateway.SRContext.access$200( at com.inductiveautomation.ignition.gateway.SRContext$ at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$ at java.util.concurrent.Executors$ at java.util.concurrent.FutureTask$Sync.innerRun( at at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301( at java.util.concurrent.ScheduledThreadPoolExecutor$ at java.util.concurrent.ThreadPoolExecutor$Worker.runTask( at java.util.concurrent.ThreadPoolExecutor$ at Caused by: org.hsqldb.HsqlException: unexpected token: ) at org.hsqldb.error.Error.parseError( at org.hsqldb.ParserBase.unexpectedToken( at org.hsqldb.ParserBase.checkIsSimpleName( at org.hsqldb.ParserDQL.readColumnNameList( at org.hsqldb.ParserDQL.readColumnNames( at org.hsqldb.ParserDQL.readColumnNames( at org.hsqldb.ParserDDL.compileAlterTableAddPrimaryKey( at org.hsqldb.ParserDDL.compileAlterTable( at org.hsqldb.ParserDDL.compileAlter( at org.hsqldb.ParserCommand.compilePart( at org.hsqldb.ParserCommand.compileStatements( at org.hsqldb.Session.executeDirectStatement( at org.hsqldb.Session.execute( at org.hsqldb.jdbc.JDBCStatement.fetchResult( ... 25 more


Looking at that stack trace, I suspect it’s probably trying to add a primary key to the table, where one isn’t defined. Off the top of my head I’m not sure if the ORM system requires a primary key on the table, but I wouldn’t be surprised. All of the PersistentRecord types I can find in Ignition have some sort of primary key. You might try adding one to your record:

public static final IdentityField Id = new IdentityField(META);

Beyond that, to see exactly what query is causing the problem, you would need to turn the logger for the class “LocalDBManagerImpl” to “DEBUG”, and then reload the module. You might be able to set the logger to this level in the gateway, and then republish your dev module, or you might need to set it in the file (add the following, the file is in the “data” folder) and restart the gateway:

When your module starts, you’ll see a message like “Automatic schema update phase2:…” right before the error.

I recommend trying the identity field first, however. I really suspect that’s the culprit.


That identity field seems to have done the trick, thanks!

Ok, good. If we’re going to require a primary key, we should probably throw an informative error, instead of simply running a bad query. I should probably also update the programmer’s guide and javadocs.


Out of curiosity - is there anything like this for database connections other than the internal one? I was looking at the DatabaseRecord but am not quite sure if that’s the right direction. It insisted that the getMeta method return a RecordMeta instead of RecordMeta.

No, there isn’t an ORM system for regular database connections. I would imagine that it should be possible to find/pick one and work it in if you wanted, but that would probably be a bit of work.

In general, everything in Ignition is done with queries, built with some care and use of the DB Translators, in order to squeeze out maximum performance, which ORMs aren’t known for. However, for simply making it easy to work with database data, it probably is a little strange that we don’t have a system like this. But ultimately, I don’t really think we will be adding it anytime soon.


[quote=“Colby.Clegg”]Ok, good. If we’re going to require a primary key, we should probably throw an informative error, instead of simply running a bad query. I should probably also update the programmer’s guide and javadocs.

Just a quick update - forgot this issue when working on extending the auth system in 7.4.2 and ran into the same exception for the “unexpected token”. Adding the primary key cleared it up again.