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

To find {fieldName}, you are required to have access to Alpha or use Network in Developer Tools [F12].

resultList = []

# search
entries = Module("{YOUR-MODULE-NAME}").findAll({'$and':
    [
        { 
            'Audit.UpdatedDate': { '$gt': Inputs['lastSync'] }
        }
    ]
}).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)

The LastModified must be included and positioned as the last field in the payload.

Sample SQL Template

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

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.

{
  "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
    }
  ]
}

PreviousSQL Database Integration NextScripting (Beta)

Last updated 21 hours ago