# java.sql.SQLSyntaxErrorException with PersistentRecord **URL:** \ **Category:** Module Development\ **Created:** [February 21, 2012, 7:33pm UTC](https://forum.inductiveautomation.com/t/java-sql-sqlsyntaxerrorexception-with-persistentrecord/4038 "2012-02-21T19:33:37Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![bloeper](https://avatars.discourse-cdn.com/v4/letter/b/ac91a4/32.png) [@bloeper](https://forum.inductiveautomation.com/u/bloeper)\ **Post date:** [February 21, 2012, 7:33pm UTC](https://forum.inductiveautomation.com/t/java-sql-sqlsyntaxerrorexception-with-persistentrecord/4038/1 "2012-02-21T19:33:37Z")
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 META = new RecordMeta(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); } @Override public RecordMeta getMeta() { return META; } ``` }[/code] 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: ```auto java.sql.SQLSyntaxErrorException: unexpected token: ) ``` Any ideas where I went wrong here? ---
**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\ **Post date:** [February 21, 2012, 8:44pm UTC](https://forum.inductiveautomation.com/t/java-sql-sqlsyntaxerrorexception-with-persistentrecord/4038/2 "2012-02-21T20:44:23Z")
Hmm, looks good to me. Can you provide the stack trace for that error? Regards, ---
**Author:** ![bloeper](https://avatars.discourse-cdn.com/v4/letter/b/ac91a4/32.png) [@bloeper](https://forum.inductiveautomation.com/u/bloeper)\ **Post date:** [February 21, 2012, 8:57pm UTC](https://forum.inductiveautomation.com/t/java-sql-sqlsyntaxerrorexception-with-persistentrecord/4038/3 "2012-02-21T20:57:14Z")
Here you go: `java.sql.SQLSyntaxErrorException: unexpected token: ) at org.hsqldb.jdbc.Util.sqlException(Util.java:350) at org.hsqldb.jdbc.Util.sqlException(Util.java:227) at org.hsqldb.jdbc.JDBCStatement.fetchResult(JDBCStatement.java:1830) at org.hsqldb.jdbc.JDBCStatement.executeUpdate(JDBCStatement.java:207) at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at com.inductiveautomation.ignition.gateway.localdb.hsql.DelegatingDataSource$DelegatingConnection$DelegatingStatement.executeUpdate(DelegatingDataSource.java:392) at com.inductiveautomation.ignition.gateway.localdb.DatasourceDBInterface.runUpdateQuery(DatasourceDBInterface.java:220) at com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl$SingleConnectionDBInterface.runUpdateQuery(LocalDBManagerImpl.java:489) at com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl.updatePersistentRecords(LocalDBManagerImpl.java:392) at com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl.updatePersistentRecords(LocalDBManagerImpl.java:334) at com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl.updatePersistentRecords(LocalDBManagerImpl.java:324) at com.company.project.cell.ignition.IgnitionCellTier.setup(Unknown Source) at com.inductiveautomation.ignition.gateway.modules.ModuleManagerImpl$LoadedModule.setup(ModuleManagerImpl.java:1970) at com.inductiveautomation.ignition.gateway.modules.ModuleManagerImpl.setup(ModuleManagerImpl.java:226) at com.inductiveautomation.ignition.gateway.SRContext.startupInternal(SRContext.java:919) at com.inductiveautomation.ignition.gateway.redundancy.RedundancyManagerImpl.startup(RedundancyManagerImpl.java:182) at com.inductiveautomation.ignition.gateway.SRContext.asyncInit(SRContext.java:708) at com.inductiveautomation.ignition.gateway.SRContext.access$200(SRContext.java:187) at com.inductiveautomation.ignition.gateway.SRContext$2.run(SRContext.java:536) at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$ThrowableCatchingRunnable.run(BasicExecutionEngine.java:526) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) at java.util.concurrent.FutureTask.run(FutureTask.java:138) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:207) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:619) Caused by: org.hsqldb.HsqlException: unexpected token: ) at org.hsqldb.error.Error.parseError(Error.java:99) at org.hsqldb.ParserBase.unexpectedToken(ParserBase.java:795) at org.hsqldb.ParserBase.checkIsSimpleName(ParserBase.java:399) at org.hsqldb.ParserDQL.readColumnNameList(ParserDQL.java:679) at org.hsqldb.ParserDQL.readColumnNames(ParserDQL.java:660) at org.hsqldb.ParserDQL.readColumnNames(ParserDQL.java:651) at org.hsqldb.ParserDDL.compileAlterTableAddPrimaryKey(ParserDDL.java:3571) at org.hsqldb.ParserDDL.compileAlterTable(ParserDDL.java:662) at org.hsqldb.ParserDDL.compileAlter(ParserDDL.java:281) at org.hsqldb.ParserCommand.compilePart(ParserCommand.java:222) at org.hsqldb.ParserCommand.compileStatements(ParserCommand.java:91) at org.hsqldb.Session.executeDirectStatement(Session.java:1203) at org.hsqldb.Session.execute(Session.java:1002) at org.hsqldb.jdbc.JDBCStatement.fetchResult(JDBCStatement.java:1822) ... 25 more` ---
**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\ **Post date:** [February 21, 2012, 9:21pm UTC](https://forum.inductiveautomation.com/t/java-sql-sqlsyntaxerrorexception-with-persistentrecord/4038/4 "2012-02-21T21:21:39Z")
Hi, 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: ```auto 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 log4j.properties file (add the following, the file is in the “data” folder) and restart the gateway: ```auto log4j.logger.com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl=DEBUG ``` 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. Regards, ---
**Author:** ![bloeper](https://avatars.discourse-cdn.com/v4/letter/b/ac91a4/32.png) [@bloeper](https://forum.inductiveautomation.com/u/bloeper)\ **Post date:** [February 21, 2012, 9:50pm UTC](https://forum.inductiveautomation.com/t/java-sql-sqlsyntaxerrorexception-with-persistentrecord/4038/5 "2012-02-21T21:50:33Z")
That identity field seems to have done the trick, thanks! ---
**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\ **Post date:** [February 21, 2012, 9:59pm UTC](https://forum.inductiveautomation.com/t/java-sql-sqlsyntaxerrorexception-with-persistentrecord/4038/6 "2012-02-21T21:59:02Z")
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. Regards, ---
**Author:** ![bloeper](https://avatars.discourse-cdn.com/v4/letter/b/ac91a4/32.png) [@bloeper](https://forum.inductiveautomation.com/u/bloeper)\ **Post date:** [February 22, 2012, 2:24am UTC](https://forum.inductiveautomation.com/t/java-sql-sqlsyntaxerrorexception-with-persistentrecord/4038/7 "2012-02-22T02:24:04Z")
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. ---
**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\ **Post date:** [February 22, 2012, 11:46pm UTC](https://forum.inductiveautomation.com/t/java-sql-sqlsyntaxerrorexception-with-persistentrecord/4038/8 "2012-02-22T23:46:03Z")
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. Regards, ---
**Author:** ![bloeper](https://avatars.discourse-cdn.com/v4/letter/b/ac91a4/32.png) [@bloeper](https://forum.inductiveautomation.com/u/bloeper)\ **Post date:** [May 7, 2012, 3:04pm UTC](https://forum.inductiveautomation.com/t/java-sql-sqlsyntaxerrorexception-with-persistentrecord/4038/9 "2012-05-07T15:04:53Z")
[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. Regards,[/quote] 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.