SQL Plugin

The SQL input plugin allows continually polling an SQL server database. The plugin is able to remember its last position based on an increasing numeric column. This plugin supports Microsoft SQL server, Oracle SQL Server, and also SAP Hana Server.

Configuration location:

Console Application	Service Application

Console Application

Service Application

[Edge Main Folder]/configs/configurations/InputPlugins/SQL

These configs are only used for the Edge instance Luna.Console, executed directly from the [Edge Main Folder]. Click the "Service Application" tab above to check the location for Edge services.

C:/ProgramData/Senseforce/Luna Data/[YourServiceName]/configurations/InputPlugins/SQL

You can install several SF Edge service instances onto one device - for example to collect data of several machines from a local data collection device. Each service instance needs it's own set of configurations. When using the Senseforce Edge Service Installer, the default configurations are automatically installed for any service.

Use the default DataMappings.xml for most use cases.

SqlSettings.xml

The SqlSettings.xml defines where to find the SQL Server database file and how to open it.

Example configuration

Description
Provider (line 6)	Defines the type of database you want to connect to. Choose between either MsSql (Microsoft SQL Server), Oracle (Oracle SQL Server), or Hana (SAP HANA). (Note: The setting is case sensitive)
ServerAddress (line 7)	Defines where to find the database server
ConnectionTimeoutS (line 8)	Connection timeout in seconds. Defaults to 30 seconds.
Database (line 9)	Which database to open
Security (line 10)	Whether to use windows user credentials to log in to the server. If set to true, this bypasses the UserId and Password settings
Password (line 12)	Password for authenticating the edge software
UserId (line 13)	Username for authenticating the edge software
WorkstationId (line 14)	Id to identify the edge (free to set for most cases)
ConnectionString (line 15)	Adding the setting "ConnectionString" to the SqlServer configuration allows to directly enter a connection string used for connection establishment. Note: If this setting is used, all other settings are ignored.

<?xml version="1.0" encoding="utf-8" ?>
<MsSqlConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                    xsi:schemaLocation="https://senseforce.io/ ..\..\Schemas\mssqlsettings.xsd" >
  <SqlServer> <!--Configuration for Sql-Server-->
    <!-- Set the database provider: MsSql or Oracle or Hana (case sensitive) -->
    <Provider>MsSql</Provider>
    <ServerAdress>serveradress\SQLEXPRESS</ServerAdress>
    <ConnectionTimeoutS>18</ConnectionTimeoutS>
    <Database>Saege1</Database>
    <Security>false</Security> <!--Valid values: true, false; If true, the current windows user is used for authentication-->
    <PacketSize>8192</PacketSize> <!-- May be varied between 512 and 32767; Default: 8192-->
    <Password>testPW</Password>
    <UserId>testUser</UserId>
    <WorkstationId>myWorkstation</WorkstationId>
    <!-- <ConnectionString>Somestring</ConnectionString>-->
  </SqlServer>

  <!--Specify file for message mappaing-->
  <Mappings>
    <FilePath>$(LunaAppDataPath)configurations/InputPlugins/SQL</FilePath>
    <FileName>DataMappings.xml</FileName>
  </Mappings>

  <!--Specify file where the data blocks to read are defined-->
  <DataConnections>
    <FilePath>$(LunaAppDataPath)configurations/InputPlugins/SQL</FilePath>
    <FileName>DataDefinition.xml</FileName>
  </DataConnections>

  <!--Specify file for logging-->
  <Logger>
    <!--LogLevel: Debug,Information,Warning,Error,Critical,None-->
    <LogLevel>Information</LogLevel>
  </Logger>
</MsSqlConfiguration>

DataDefinition.xml

The DataDefinition.xml defines which table and which columns to read.

Example configuration

The following exemplary configuration polls the table CounterTest (line 6) every second (line 10) and reads the columns Id, Timestamp, and Counter (lines 13 to 27). A file called PersistId.json (line 47) is used to remember, which line with which Id was last read (line 16).

Settings

Setting	Description
Name (line 6)	Name of table to read
OrderBy (line 7)	Which column to order the table by. Use the column marked as IsLastReadId for most use cases
OrderDir (line 8)	Use ASC in all cases
NumberToRead (line 9)	Maximum number of rows to read in a single reading operation
PollingTimeS (line 10)	Polling interval in seconds
OnlySendChanges (line 11)	Defines, whether only changes in columns should be transmitted. For most SQL use cases, false is appropriate
Name (line 14)	Defines the name of which column to read
FilterOnly (line 15)	Flag indicating whether this column is used only for filtering
IsLastReadId (line 16)	If set to true, this column is used to remember, which row was the last read. Make sure that this column is used in the OrderBy setting (line 7) and it is implemented as incrementing number or incrementing timestamp in the SQL table to read.
IsTimestamp (line 20)	If set to true, this column is handled as an internal timestamp datapoint. Must be UNIX timestamp in milliseconds. It is highly recommended to have one column marked as a timestamp.
Filter (line 28)	The filter block allows defining 'where' clauses in the SQLITE select statement. One or more Filter-blocks may be defined in a wrapping Filters-block (see line 34ff.)
Condition (line 29)	Whether to connect this filter logically AND or OR with other filters. Not relevant, if only one filter is defined. Allowed values: OR, AND
Operator (line 30)	Allowed values: Greater, Less, GreaterOrEqual, LessOrEqual, Equal or NotEqual
FilterName (line 31)	Value to compare the column. In the example, only rows where column Counter equals 1 are read
MandatoryCondition	Optional. Filter marked with MandatoryCondition = true must evaluate to true, otherwise, the reading process is halted until the filter evaluates to true. If the condition in a row is not met, all the entries are read until the row which does not meet the condition.
MandatoryConditionSkipTimeMs	Optional: If a filter with MandatoryCondition = true is set for longer than MandatoryConditionSkipTimeMs (in milliseconds), the mandatory condition is skipped and the halting line is read anyhow. Note: The column definitions must contain a column with flag IsTimestamp =true
MathOperation	Optional. One or more MathOperation-blocks may be defined in a wrapping MathOperations-block (see line 48ff.).
Operation (line 52)	Defines the mathematical operation to perform. Allowed values: Add, Subtract, Multiply, Divide, Power If several operations are defined, the operations are applied consecutively, starting with the top-most defined operation.
Value (line 54)	Right-hand-side value of the mathematical operation. In the example, the read value of column Counter is multiplied by 1000

<?xml version="1.0" encoding="utf-8" ?>
<DataConnectionsConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                       xsi:schemaLocation="https://senseforce.io/ ..\..\Schemas\dataConnections_sqlite.xsd" >
  <Datas>
    <Table>
      <Name>CounterTest</Name>
      <OrderBy>Id</OrderBy>
      <OrderDir>ASC</OrderDir>
      <NumberToRead>100</NumberToRead>
      <PollingTimeS>1</PollingTimeS>
      <OnlySendChanges>false</OnlySendChanges>
      <Columns>
        <Column>
          <Name>Id</Name>
          <FilterOnly>false</FilterOnly>
          <IsLastReadId>true</IsLastReadId>
        </Column>
        <Column>
          <Name>Timestamp</Name>
          <IsTimestamp>true</IsTimestamp>
          <FilterOnly>false</FilterOnly>
        </Column>
        <Column>
          <Name>Counter</Name>
          <IsTimestamp>false</IsTimestamp>
          <FilterOnly>false</FilterOnly>
          <!-- You can eigher use a single filter  -->
	  <Filter>
            <Condition>OR</Condition>
            <Operator>Equal</Operator>
            <FilterName>1</FilterName>
	  </Filter>
	  <!-- .. or many filters as well as in the example below -->
	  <!-- <Filters>
            <Filter>
              <Condition>AND</Condition>
              <Operator>Greater</Operator>
              <FilterName>8</FilterName>
            </Filter>
            <Filter>
              <Condition>OR</Condition>
              <Operator>Equal</Operator>
              <FilterName>5</FilterName>
            </Filter>
          </Filters> -->
    	  <!-- Define which mathematical operations you want to perform with the data -->
    	  <!-- List of Mathematical operations -->
    	  <MathOperations>
    	    <!-- Definition of mathematical operation -->
    	    <MathOperation>
    	    <!-- Definition of operation type (valid: Add, Subtract, Divide, Multiply, Power) -->
    	      <Operation>Multiply</Operation>
    	      <!-- Define the second mathematical operand (first one is the variable itself) -->
    	      <Value>1000</Value>
    	    </MathOperation>
    	  </MathOperations>
        </Column>
      </Columns>
      <PersistLastId>
        <FilePath>$(LunaAppDataPath)configurations/InputPlugins/SQL</FilePath>
        <FileName>PersistId.json</FileName>
      </PersistLastId>
    </Table>
  </Datas>
</DataConnectionsConfig>

Example DataDefinition.xml

Example SqlSettings.xml