Skip to content

SQL Connector Worked Examples

Two complete configurations for the SQL Connector, each with the Object Types document that describes its tables. Start from the one closest to your database.

The two worked examples compared The Microsoft SQL Server example is an HR application with Person and Department Object Types; a Person's ManagerId references another Person and its DepartmentId a Department. Its key, EmployeeId, is an int identity column, which JIM reads back as the External ID when it creates a row. Its multi-valued attribute is PhoneNumbers, Text values from the dbo.EmployeePhones table. The Oracle Database example is an HR schema with Person and Group Object Types; a Person's MANAGER_ID references another Person. Its key, EMPLOYEE_ID, is a sequence-backed NUMBER(10), which imports as a Long Number. Its multi-valued attribute is Members, references to Person from the HR.GROUP_MEMBERS table. Exporting a new Person in the first inserts the row, reads the identity value back, then inserts the phone numbers; exporting a Group in the second inserts its row and one GROUP_MEMBERS row per member. Either way, an object's row and its related-table rows are written in one transaction. Microsoft SQL Server an HR application Oracle Database an HR schema with groups Object Types Person, Departmenta manager and a department as references Person, Groupa manager as a reference Key EmployeeId, an identityread back as the External ID EMPLOYEE_ID NUMBER(10)sequence-backed; a Long Number Related table PhoneNumbersText values, from dbo.EmployeePhones Membersreferences to Person,from HR.GROUP_MEMBERS Exporting a new object a Person: inserts the rowreads the identity back, theninserts the phone numbers a Group: inserts the rowthen one GROUP_MEMBERS row per member Either way, an object's row and its related-table rows are written in one transaction The two worked examples compared The Microsoft SQL Server example is an HR application with Person and Department Object Types; a Person's ManagerId references another Person and its DepartmentId a Department. Its key, EmployeeId, is an int identity column, which JIM reads back as the External ID when it creates a row. Its multi-valued attribute is PhoneNumbers, Text values from the dbo.EmployeePhones table. The Oracle Database example is an HR schema with Person and Group Object Types; a Person's MANAGER_ID references another Person. Its key, EMPLOYEE_ID, is a sequence-backed NUMBER(10), which imports as a Long Number. Its multi-valued attribute is Members, references to Person from the HR.GROUP_MEMBERS table. Exporting a new Person in the first inserts the row, reads the identity value back, then inserts the phone numbers; exporting a Group in the second inserts its row and one GROUP_MEMBERS row per member. Either way, an object's row and its related-table rows are written in one transaction. Microsoft SQL Server an HR application Object TypesPerson, Departmenta manager and a department as references KeyEmployeeId, an identityread back as the External ID Related tablePhoneNumbersText values, from dbo.EmployeePhones Exporting a new objecta Person: inserts the rowreads the identity back, then inserts the phone numbers Oracle Database an HR schema with groups Object TypesPerson, Groupa manager as a reference KeyEMPLOYEE_ID NUMBER(10)sequence-backed; a Long Number Related tableMembersreferences to Person, from HR.GROUP_MEMBERS Exporting a new objecta Group: inserts the rowthen one GROUP_MEMBERS row per member Either way, an object's row and its related-table rows are written in one transaction

Microsoft SQL Server: an HR application

dbo.Employees has an IDENTITY key, a ManagerId pointing at another employee and a DepartmentId pointing at dbo.Departments; phone numbers are in dbo.EmployeePhones.

Object Types for a SQL Server HR schema
{
  "objectTypes": [
    {
      "name": "Person",
      "schema": "dbo",
      "table": "Employees",
      "anchorColumns": [ "EmployeeId" ],
      "columns": [
        { "name": "ManagerId", "referencesObjectType": "Person" },
        { "name": "DepartmentId", "referencesObjectType": "Department" }
      ],
      "relatedTables": [
        {
          "attributeName": "PhoneNumbers",
          "schema": "dbo",
          "table": "EmployeePhones",
          "valueColumn": "PhoneNumber",
          "joinColumns": [ "EmployeeId" ]
        }
      ]
    },
    {
      "name": "Department",
      "schema": "dbo",
      "table": "Departments",
      "anchorColumns": [ "DepartmentId" ]
    }
  ]
}

After the schema import: EmployeeId (int) is the External ID and is set only when a row is created; ManagerId and DepartmentId are references; PhoneNumbers is a multi-valued Text attribute; HireDate (datetime2) is read in the Database Time Zone; IsActive (bit) is a Boolean. Exporting a new Person inserts the row, reads the identity value back as the External ID, then inserts the phone numbers.

Oracle Database: an HR schema with groups

HR.EMPLOYEES has a sequence-backed EMPLOYEE_ID NUMBER(10) key and a MANAGER_ID; HR.GROUPS has members in HR.GROUP_MEMBERS.

Object Types for an Oracle HR schema
{
  "objectTypes": [
    {
      "name": "Person",
      "schema": "HR",
      "table": "EMPLOYEES",
      "anchorColumns": [ "EMPLOYEE_ID" ],
      "columns": [
        { "name": "MANAGER_ID", "referencesObjectType": "Person" }
      ]
    },
    {
      "name": "Group",
      "schema": "HR",
      "table": "GROUPS",
      "anchorColumns": [ "GROUP_ID" ],
      "relatedTables": [
        {
          "attributeName": "Members",
          "schema": "HR",
          "table": "GROUP_MEMBERS",
          "valueColumn": "EMPLOYEE_ID",
          "joinColumns": [ "GROUP_ID" ],
          "referencesObjectType": "Person"
        }
      ]
    }
  ]
}

After the schema import: EMPLOYEE_ID is a Long Number (change it to Number on the Schema tab if it is to flow to Employee Number); IS_ACTIVE NUMBER(1) is a Number unless Treat NUMBER(1) Columns as Boolean is on; HIRE_DATE (DATE) is read in the Database Time Zone; Members is a multi-valued reference to Person. Exporting a Group inserts its row and one GROUP_MEMBERS row per member in one transaction.

Reading through a view and writing to a table

If the application publishes a view for readers but takes writes on the underlying table, either point one Connected System at the table for both directions, or use two Connected Systems: one on the view for import, one on the table for export. A single Object Type reads and writes the same source.