# 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 %}


---

# 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://inistate.gitbook.io/home/advanced/integration/sql-database-integration/from-inistate-to-sql-database.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.