Adding a default setting to an OPCUA device setting with ' results in crash on module import

Matthew_Plumb · November 4, 2022, 10:46pm

Hello,

I am creating a new OPCUA device module using the SDK. I would like to add a device setting which is the default datetime format to use when parsing timestamps.

Within the DeviceSettings class that extends PersistentRecord I have the following code:

static {
EVENT_TIME_FIELD_FORMAT.setDefault("yyyy-MM-dd'T'HH:mm:ss.SSSSSSX");
}

This corresponds to the required string that the DateTimeFormatter in Java needs to represent a literal T in the middle of the string.

Unfortunately this causes a crash on module load due to SQLite not being able to parse and store this.

I can change ' to '' but this has the effect as appearing as '' in my default within configuration page.

Is there anyway around this? Error seen on gateway on import of module is shown below.

Thanks!!

Matt

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "T": syntax error)
at org.sqlite.core.DB.newSQLException(DB.java:909)
at org.sqlite.core.DB.newSQLException(DB.java:921)
at org.sqlite.core.DB.throwex(DB.java:886)
at org.sqlite.core.NativeDB._exec_utf8(Native Method)
at org.sqlite.core.NativeDB._exec(NativeDB.java:87)
at org.sqlite.jdbc3.JDBC3Statement.executeUpdate(JDBC3Statement.java:116)
at com.inductiveautomation.ignition.gateway.localdb.DelegatingDataSource$DelegatingConnection$DelegatingStatement.executeUpdate(DelegatingDataSource.java:421)
at com.inductiveautomation.ignition.gateway.localdb.AbstractDBInterface.runUpdateQuery(AbstractDBInterface.java:222)
at com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl$SingleConnectionDBInterface.runUpdateQuery(LocalDBManagerImpl.java:748)
at com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl.updatePersistentRecords(LocalDBManagerImpl.java:549)
at com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl.updatePersistentRecords(LocalDBManagerImpl.java:466)
at com.inductiveautomation.ignition.gateway.localdb.LocalDBManagerImpl.updatePersistentRecords(LocalDBManagerImpl.java:455)
at com.inductiveautomation.ignition.gateway.opcua.server.DeviceManager.registerDeviceType(DeviceManager.kt:146)
at com.inductiveautomation.ignition.gateway.opcua.OpcUaExtensionManager.registerDeviceType(OpcUaExtensionManager.kt:35)
at com.inductiveautomation.ignition.gateway.opcua.server.api.AbstractDeviceModuleHook.serviceReady(AbstractDeviceModuleHook.kt:50)
at com.inductiveautomation.ignition.gateway.services.ModuleServicesManagerImpl.subscribe(ModuleServicesManagerImpl.java:96)
at com.inductiveautomation.ignition.gateway.opcua.server.api.AbstractDeviceModuleHook.startup(AbstractDeviceModuleHook.kt:37)
at com.accenture.kafka_opcua_device.ModuleHook.startup(ModuleHook.java:50)
at com.inductiveautomation.ignition.gateway.modules.ModuleManagerImpl$LoadedModule.startup(ModuleManagerImpl.java:2433)
at com.inductiveautomation.ignition.gateway.modules.ModuleManagerImpl.startupModule(ModuleManagerImpl.java:1226)
at com.inductiveautomation.ignition.gateway.modules.ModuleManagerImpl$2.call(ModuleManagerImpl.java:771)
at com.inductiveautomation.ignition.gateway.modules.ModuleManagerImpl.executeModuleOperation(ModuleManagerImpl.java:947)
at com.inductiveautomation.ignition.gateway.modules.ModuleManagerImpl.installModuleInternal(ModuleManagerImpl.java:737)
at com.inductiveautomation.ignition.gateway.modules.ModuleManagerImpl$InstallCommand.execute(ModuleManagerImpl.java:1903)
at com.inductiveautomation.ignition.gateway.modules.ModuleManagerImpl$Receiver.receiveCall(ModuleManagerImpl.java:1856)
at com.inductiveautomation.ignition.gateway.redundancy.QueueableMessageReceiver.receiveCall(QueueableMessageReceiver.java:47)
at com.inductiveautomation.ignition.gateway.redundancy.RedundancyManagerImpl.dispatchMessage(RedundancyManagerImpl.java:930)
at com.inductiveautomation.ignition.gateway.redundancy.RedundancyManagerImpl$ExecuteTask.run(RedundancyManagerImpl.java:1005)
at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$ThrowableCatchingRunnable.run(BasicExecutionEngine.java:538)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)

Kevin.Herron · November 4, 2022, 10:58pm

What type of field is this? I can't make any sense of this error alone, need more details about what's going on here.

Matthew_Plumb · November 4, 2022, 11:02pm

Right. Sorry about that.
/**
Event Time Field Format is a date time formatting string of how it is expected the timestamp will appear in the messages.
*/
public static final StringField EVENT_TIME_FIELD_FORMAT = new StringField(META, "EventTimeFieldFormat");

Kevin.Herron · November 4, 2022, 11:06pm

Huh. This just looks like a bug in the simple ORM system behind the settings records... it's just not sanitizing that String before running the query.

PGriffith · November 4, 2022, 11:07pm

Yeah... you might be able to hand-escape it? Maybe wrap the whole thing in '? Or add (doubled?) backslashes before your single quotes?

Kevin.Herron · November 4, 2022, 11:08pm

I think this might work?

EVENT_TIME_FIELD_FORMAT.setDefault("yyyy-MM-dd''T''HH:mm:ss.SSSSSSX");

edit: meh, no, that shows up on the page and in the field value... try one of Paul's.

Matthew_Plumb · November 4, 2022, 11:10pm

Using ''T'' results in

Matthew_Plumb · November 4, 2022, 11:18pm

Using \\'T\\' results in

Matthew_Plumb · November 4, 2022, 11:22pm

Looks like this is the one:

Appreciate everyone's help!!