Make sure sql ext is enabled. If it isn’t go to Settings → Ext and enable it.


Than go to Connector app and create new sql connection

Note that except standard tags you should add sqlSyncHisExpr tag and set it to function. It needs for syncing history using this sql connector. This function should take 3 parameters: sqlConn, his, range and return mapped history data what will be written to his record


importHisSql.axon
(sqlConn, his, range) => do
  query: "SELECT * FROM [dbo].[" + his->sqlPointId + "]"
  if (range != null) query = query + " WHERE ts between '" + range.start.format("YYYY-MM-DD hh:mm:ss")  + "' and '" + range.end.format("YYYY-MM-DD hh:mm:ss") + "'"
  else if (his.has("hisEnd")) query = query + " WHERE [dbo].[" + his->sqlPointId + "].[ts] > ('" + his->hisEnd.format("YYYY-MM-DD hh:mm:ss") + "')" // ('2017-10-15 10:34:09')

  data: sqlQuery(sqlConn, query)
  
  mapped: data.map((row) => do
    {
     ts: dateTime(row-> ts.date, row-> ts.time, "Chicago"), 
     val: row-> value
     //if (row->value=="TRUE" or row->value=="FALSE") row->value.lower().parseBool() else row->value.parseNumber(),
    }
  end)  
  mapped
end

After creating you can test new connector with sqlPing() function or hit Ping button in Connectors app.

If the ping is successful it will update your connector record with tags indicating version of the database and JDBC driver.

  • No labels