Skip to main content

Data provisioning

A generic database connector can be used to enable the data provisioning to SQL (Microsoft SQL Server and Oracle-based systems are supported).

Review the following practical example to learn how to use this connectivity.

Configuring data provisioning using the generic database connector (practical example)​

Goal: Update the email of an identity in a database table named Identity. The unique key used to locate the record is UID.

Prerequisites: Ensure that the connector is configured correctly. See Generic SQL - SQL generic database.

  1. Define the data model.
<connectorDataModel xmlns="http://schemas.omada.net/ops/2015/ConnectorDataModelML" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <properties>
    <property name="UID" dataType="stringType"/>
    <property name="Email" dataType="stringType"/>
  </properties>
  <objects>
    <object name="Identity">
      <objectDetails>
        <objectDetail name="Schema" value="dbo" />
      </objectDetails>
      <objectProperties>
        <objectProperty isKey="true">UID</objectProperty>
        <objectProperty>Email</objectProperty>
      </objectProperties>
    </object>
  </objects>
</connectorDataModel>
  • UID is marked as the key (isKey="true") that is used in the WHERE clause during updates and deletes.
  • Email is the attribute we want to update in the database.
  • The object name (Identity) must match the name of the table in the target database.
  • <objectDetail name="Schema" value="dbo" /> defines the schema used in the database query, and must match the actual schema where the table resides.

You can use the following object details:

  • Schema - the database schema where the related objects (tables or procedures) reside.
  • IsStoredProcedure - indicates whether operations use stored procedures instead of inline SQL.
  • ProcedureName - the base name of the stored procedure associated with the object.
  • CreateProcedureName - the stored procedure's name used to create a new record.
  • UpdateProcedureName - the stored procedure's name used to update an existing record.
  • DeleteProcedureName - the stored procedure's name used to delete a record.
  • ProcedureParameters - a comma- or semicolon-separated set of parameters used by the main stored procedure.
  • CreateProcedureParameters - a comma- or semicolon-separated set of parametersrequired by the create stored procedure.
  • UpdateProcedureParameters - a comma- or semicolon-separated set of parametersrequired by the update stored procedure.
  • DeleteProcedureParameters - a comma- or semicolon-separated set of parametersrequired by the delete stored procedure.
  • CreateSqlTemplate - an SQL template used to generate INSERT statements (when stored procedures are not used).
  • UpdateSqlTemplate - an SQL template used to generate UPDATE statements.
  • DeleteSqlTemplate - an SQL template used to generate DELETE statements.
  1. Configure the task mapping to map the identity attributes to the database fields:
  • UID is mapped to DOLM_IDENTITYID.
  • Email is mapped to DOLM_EMAIL.
  • Standard fields such as Object Id, Object type, and Operation are required for the task to run correctly.

Expected result:

Example identity: Anna Becker

  • UID: ABAA
  • Current email: ABAA@megamart.com

After changing the identity's email to annabecker@megamart.com, the provisioning is triggered and the connector executes the following statement:

UPDATE dbo.Identity
SET Email = 'ANNABECKER@MEGAMART.COM'
WHERE UID = 'ABAA';

The change is reflected directly in the database: