> For the complete documentation index, see [llms.txt](https://inistate.gitbook.io/home/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://inistate.gitbook.io/home/advanced/integration/sql-database-integration/from-inistate-to-sql-database.md).

# From Inistate to SQL Database

### Things to Take Note

When performing integration between **Inistate** and a **SQL database**, ensure the following steps and validations are followed:

#### 1. Mapping File Preparation

* Prepare a **mapping file** that defines how each field from **Inistate** corresponds to the **SQL database columns**.
* Ensure that all required fields are included in the mapping file.
* Double-check that optional fields are also handled properly to avoid missing data.

#### 2. Data Type Validation

* Verify that each mapped field matches the **data type** defined in the SQL database.
* Examples:
  * Text → `VARCHAR`, `NVARCHAR`
  * Integer, Number → `INT`, `DECIMAL`
  * Date Time, Date → `DATETIME`, `DATE`
* Ensure that the data from Inistate does not conflict with SQL type restrictions (e.g., trying to insert a string into a numeric field).

#### 3. Value Length Check

* Validate the **maximum length** allowed by the SQL database for each column.
* Example:
  * `VARCHAR(50)` → Ensure the incoming string is 50 characters or less.
* Truncate or handle values exceeding the defined length to avoid **insertion errors**.

#### 4. Null & Default Handling

* Check whether the SQL column allows **NULL values**.
* If not, ensure default values are provided during integration.

#### 5. Data Integrity & Constraints

* Validate data against **primary keys**, **foreign keys**, and **unique constraints**.
* Ensure that reference data exists before inserting dependent records.

### Sample Code

{% tabs %}
{% tab title="Logic Script" %}
{% hint style="info" %}
To find `{fieldName}`, you are required to have access to **Alpha** or use **Network** in Developer Tools `[F12]`.
{% endhint %}

```python
resultList = []

# search
entries = Module("{YOUR-MODULE-NAME}").findAll({'$and':
    [
        { 
            'Audit.UpdatedDate': { '$gt': Inputs['lastSync'] }
        },
        {
            'Audit.UpdatedBy': { '$ne': 'External' }
        }
    ]
}).OrderBy(lambda x: x.UpdatedDate).Take({Count})

debug("entries", entries)

# format SQL value
def sqlString(val):
    if val is not None:
        if isinstance(val, basestring) == False:
            val = str(val)

        return val.replace("'", "''")

    return val
    
def sqlBoolean(val):
    if val is True:
        return 1
    
    return 0
    
# prepare data
def prepareRow(x, items):
    row = JObject()
    row['NO'] = items.IndexOf(x) + 1
    row['Type'] = x['Type']
    row['Description'] = x['Description']
    row['Amount'] = x['Amount']
    row['IsActive'] = 1 if x['Is Active'] == True else 0
    return row

def preparePayload(data):
    payloadData = JObject()
    payloadData['FirstName'] = sqlString(data['First Name'])
    payloadData['LastName'] = sqlString(data['Last Name'])
    payloadData['Email'] = sqlString(data['Email'])
    payloadData['PhoneNumber'] = data['Phone Number']
    payloadData['Department'] = sqlString(data['Department'])
    payloadData['Position'] = sqlString(data['Position'])
    payloadData['DateOfJoining'] = data['Date Of Joining']
    payloadData['DateOfBirth'] = data['DOB']
    payloadData['Salary'] = data['Salary']
    payloadData['IsActive'] = sqlBoolean(data['Is Active'])
    payloadData['DocumentID'] = data.DocumentId
    payloadData['Benefits'] = JArray.FromObject(data['Benefits'].Select(lambda x: prepareRow(x, data['Benefits'])))
    payloadData['LastModified'] = data.UpdatedDate
    return payloadData

for entry in entries:
    payloadData = preparePayload(entry)
    resultList.append(payloadData)

# results
debug('resultList', resultList)
outputs['results'] = JArray.FromObject(resultList)

```

{% hint style="warning" %}
If the information type is **Module** or **User**, use `data.displayField({displayName})`.\
\
For Example:

```python
payloadData['Name'] = sqlString(data.displayField('Name'));
```

\
To avoid errors caused by deleted records during synchronization.
{% endhint %}

{% hint style="warning" %}
The **`LastModified`** must be **included** and positioned as the **last field** in the payload.
{% endhint %}
{% endtab %}
{% endtabs %}

### Sample SQL Template

{% tabs %}
{% tab title="SQL Template" %}

```liquid
{% for staff in payload %}
IF EXISTS (SELECT 1 FROM Staff WHERE DocumentID = '{{ staff.DocumentID }}')
BEGIN
  UPDATE Staff
  SET 
    FirstName = N'{{ staff.FirstName }}',
    LastName = N'{{ staff.LastName }}',
    Email = N'{{ staff.Email }}',
    PhoneNumber = N'{{ staff.PhoneNumber }}',
    Department = N'{{ staff.Department }}',
    Position = N'{{ staff.Position }}',
    DateOfJoining = {% if staff.DateOfJoining and staff.DateOfJoining != '' %}N'{{ staff.DateOfJoining }}'{% else %}NULL{% endif %},
    DateOfBirth = {% if staff.DateOfBirth and staff.DateOfBirth != '' %}N'{{ staff.DateOfBirth }}'{% else %}NULL{% endif %},
    Salary = {{ staff.Salary }},
    IsActive = {{ staff.IsActive }}
  WHERE DocumentID = '{{ staff.DocumentID }}';
END
ELSE
BEGIN
  INSERT INTO Staff (
    DocumentID, FirstName, LastName, Email, PhoneNumber, 
    Department, Position, DateOfJoining, DateOfBirth, Salary, IsActive
  )
  VALUES (
    '{{ staff.DocumentID }}',
    N'{{ staff.FirstName }}',
    N'{{ staff.LastName }}',
    N'{{ staff.Email }}',
    N'{{ staff.PhoneNumber }}',
    N'{{ staff.Department }}',
    N'{{ staff.Position }}',
    {% if staff.DateOfJoining and staff.DateOfJoining != '' %}N'{{ staff.DateOfJoining }}'{% else %}NULL{% endif %},
    {% if staff.DateOfBirth and staff.DateOfBirth != '' %}N'{{ staff.DateOfBirth }}'{% else %}NULL{% endif %},
    {{ staff.Salary }},
    {{ staff.IsActive }}
  );
END

{% if staff.Benefits and staff.Benefits.size > 0 %}
  DELETE FROM Benefits
  WHERE StaffID = '{{ staff.DocumentID }}';
    AND NO NOT IN (
      {% for benefit in staff.Benefits %}
        {{ benefit.NO }}{% if forloop.last == false %}, {% endif %}
      {% endfor %}
    );

  {% for benefit in staff.Benefits %}
    IF EXISTS (
      SELECT 1 
      FROM Benefits 
      WHERE StaffID = '{{ staff.DocumentID }}'
        AND NO = {{ benefit.NO }}
    )
    BEGIN
      UPDATE Benefits
      SET 
        Type = N'{{ benefit.Type }}',
        Description = N'{{ benefit.Description }}',
        Amount = {{ benefit.Amount }},
        IsActive = {{ benefit.IsActive }}
      WHERE StaffID = '{{ staff.DocumentID }}'
        AND NO = {{ benefit.NO }};
    END
    ELSE
    BEGIN
      INSERT INTO Benefits (StaffID, NO, Type, Description, Amount, IsActive)
      VALUES (
        '{{ staff.DocumentID }}',
        {{ benefit.NO }},
        N'{{ benefit.Type }}',
        N'{{ benefit.Description }}',
        {{ benefit.Amount }},
        {{ benefit.IsActive }}
      );
    END
  {% endfor %}
{% else %}
  DELETE FROM Benefits
  WHERE StaffID = '{{ staff.DocumentID }}';
{% endif %}

{% endfor %}
```

{% hint style="warning" %}
It needs to be converted into a string format before being added to `jobs.json`.
{% endhint %}
{% endtab %}

{% tab title="Extras" %}

{% endtab %}
{% endtabs %}

### Jobs json

The jobs.json file defines the set of connector tasks to run during integration.

Each job entry specifies:

* **name**: Logical name of the task.
* **enabled**: `true`/`false` to turn this job on or off.
* **connectionString**: A valid ADO-style string.
* **debug**: Enable detailed logging.
* sqlTemplate: Your SQL template script to select changed records.
* **hook**: The callback URL to receive each batch of synced rows.
* **lastSync**: Timestamp of the last run.
* **interval**: Polling interval in minutes.

{% tabs %}
{% tab title="jobs.json" %}

```json
{
  "SQLSync": [
    {
      "name": "{NAME}",
      "enabled": true,
      "connectionString": "Data Source={SERVER};Initial Catalog={DATABASE};User Id={DB_USERNAME};Password={DB_PASSWORD};",
      "to": "sql",
      "debug": false,
      "sqlTemplate": "{SQL TEMPLATE}",
      "hook": "https://api.inistate.com/api/automationHook/hook/{MODULE-ID}-{LOGIC-NAME}",
      "lastSync": "{LAST_SYNC_DATE_TIME}",
      "interval": 5
    }
  ]
}
```

{% endtab %}

{% tab title="Extras" %}

{% endtab %}
{% endtabs %}