# 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:** 

{% tabs %}
{% tab title="Console Application" %}

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

{% hint style="danger" %}
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. 
{% endhint %}
{% endtab %}

{% tab title="Service Application" %}

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

{% hint style="info" %}
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.
{% endhint %}
{% endtab %}
{% endtabs %}

{% hint style="info" %}
Use the default DataMappings.xml for most use cases. 
{% endhint %}

## 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)  | <p>Adding the setting "ConnectionString" to the SqlServer configuration allows to directly enter a connection string used for connection establishment. </p><p><strong>Note</strong>: If this setting is used, all other settings are ignored.</p> |

```markup
<?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).

{% hint style="danger" %}
Make sure that the column marked with **IsLastReadId** = true (line 16) is implemented as an incrementing number in the SQL database table.&#x20;
{% endhint %}

#### 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)             | <p>The filter block allows defining 'where' clauses in the SQLITE select statement. </p><p>One or more Filter-blocks may be defined in a wrapping Filters-block (see line 34ff.)</p>                                                                                                           |
| Condition (line 29)          | <p>Whether to connect this filter logically AND or OR with other filters. Not relevant, if only one filter is defined.</p><p><strong>Allowed values</strong>: OR, AND</p>                                                                                                                      |
| 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)          | <p>Defines the mathematical operation to perform.</p><p><strong>Allowed values</strong>: Add, Subtract, Multiply, Divide, Power</p><p>If several operations are defined, the operations are applied consecutively, starting with the top-most defined operation.</p>                           |
| Value (line 54)              | Right-hand-side value of the mathematical operation. In the example, the read value of column **Counter** is **multiplied** by **1000**                                                                                                                                                        |

```markup
<?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>
```

{% file src="/files/-Lt1NqZt43\_SVKDR1K7Z" %}
Example DataDefinition.xml
{% endfile %}

{% file src="/files/-ME2WkATQhccJJG\_d9XI" %}
Example SqlSettings.xml
{% endfile %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://manual.senseforce.io/manual/senseforce-edge/edge-configuration/input-plugins/ms-sql-plugin.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.