Added #JDBC as logging option to the Open-Source Automation-Gateway Frankenstein. Values from #OPCUA servers can now also be logged to relational databases – #sql is still so great and powerful! Tested with  #postgresql  #mysql and #mssqlserver … fetching history values via the integrated #graphql server is also included…

You have to add the JDBC driver to your classpath and set the appropriate JDBC URL path in the Frankenstein configuration file – see an example below. PostgreSQL, MySQL and Microsoft SQL Server JDBC drivers are already included in the build.gradle file (see lib-jdbc/build.gradle) and also appropriate SQL statements are implemented for those relational databases. If you use other JDBC drivers you can add the driver to the lib-jdbc/build.gradle file as runtime only dependency and you may specify SQL statements for insert and select in the configuration file.

You can specify the table name in the config file with the option “SqlTableName”, if you do not specify the table name then “events” will be used as default name.

Create a table with this structure. For PostgreSQL, MySQL and Microsoft SQL Server the table will be created on startup automatically.

CREATE TABLE IF NOT EXISTS public.events
  (
      sys character varying(30) NOT NULL,
      nodeid character varying(30) NOT NULL,
      sourcetime timestamp without time zone NOT NULL,
      servertime timestamp without time zone NOT NULL,
      numericvalue numeric,
      stringvalue text,
      status character varying(30) ,
      CONSTRAINT pk_events PRIMARY KEY (system, nodeid, sourcetime)
  )
  TABLESPACE ts_scada;

Configuration of Frankenstein for JDBC Logging:

Database:
  Logger:
    - Id: postgres
      Type: Jdbc
      Enabled: true
      Url: jdbc:postgresql://nuc1:5432/scada
      Username: system
      Password: manager
      SqlTableName: events     
      Logging:
        - Topic: opc/opc1/path/Objects/Demo/SimulationMass/SimulationMass_SByte/+
        - Topic: opc/opc1/path/Objects/Demo/SimulationMass/SimulationMass_Byte/+

Because the SQL dialect can be slightly different with other databases, you can specify the insert and select SQL statement in the config file:

Database:
  Logger:
    - Id: log1
      Type: Jdbc
      Enabled: true
      Url: jdbc:other://nuc1:1111/scada
      Username: system
      Password: manager
      SqlTableName: events     
      SqlInsertStatement: > 
        INSERT INTO events (sys, nodeid, sourcetime, servertime, numericvalue, stringvalue, status)
         VALUES (?, ?, ?, ?, ?, ?, ?)
         ON CONFLICT ON CONSTRAINT PK_EVENTS DO NOTHING  
      SqlQueryStatement: >
        SELECT sourcetime, servertime, numericvalue, stringvalue, status
         FROM events
         WHERE sys = ? AND nodeid = ? AND sourcetime >= ? AND sourcetime <= ? 

The history data can also be queried via the GraphQL interface of Frankenstein, see an example query here:

{
  a:NodeValue(System:"opc1", NodeId: "ns=2;s=Scalar_Static_Int64") {
    Value
    mysql:History(Log: "mssql", LastSeconds: 600) {
      Value
      SourceTime
      ServerTime
    }
    pgsql:History(Log: "postgres", LastSeconds: 600) {
      Value
      SourceTime
      ServerTime
    }    
  }
  b:NodeValue(System:"opc1", NodeId: "ns=2;s=Scalar_Static_String") {
    Value
    mysql:History(Log: "mysql", LastSeconds: 600) {
      Value
      SourceTime
      ServerTime
    }
    pgsql:History(Log: "postgres", LastSeconds: 600) {
      Value
      SourceTime
      ServerTime
    }    
  }  
}