Tables

The connector exposes tables for every entity set and singleton defined on the OData service document. Entities on these tables may be inserted, updated, or deleted using standard SQL insert, update, or delete statements.

Executing Deep Inserts with SQL

The connector supports OData deep inserts, in which you simultaneously create a base entity and link it to related entities, by specifying navigation properties. To specify Navigation Properties for an entity, you create a temporary table for the navigation property and then reference the temporary table in the insert to the base table. Reference the temporary table in the appropriate navigation property column on the base table. Each navigation property column is prefixed with the word "Linked".

Note that you must define the temporary tables and insert to the base entity within the same connection.

Example: Deep Inserts in SAP SuccessFactors

For example, consider the ExternalUser table. When creating an ExternalUser, you may also specify an ExtAddressInfo, ExtEmailInfo, ExtPersonalInfo, and ExtPhoneInfo. To do so, you can specify the following navigation properties.

Creating Temporary Tables

Insert the related entities into temporary tables that correspond to each navigation property. You can specify an existing entity's primary key or you can insert a new entity.

ExtAddressInfo: The following statement creates a new ExtAddressInfo:

INSERT INTO ExtAddressInfo#TEMP (address1, city, country, zipCode) 
VALUES ('123 Fake Street', 'Springfield', 'USA', '58008')

ExtEmailInfo: The following statements add two ExtEmailInfos to the ExternalUser:

INSERT INTO ExtEmailInfo#TEMP (EmailAddress, EmailType, isPrimary) VALUES ('[email protected]', 'business', true)

INSERT INTO ExtEmailInfo#TEMP (EmailAddress, EmailType, isPrimary) VALUES ('[email protected]', 'personal', false)

ExtPersonalInfo: The following statement specifies the existing ExtPersonalInfo:
```
INSERT INTO ExtPersonalInfo#TEMP (personalInfoId) 
VALUES (5)
```
ExtPhoneInfo: The following statement specifies the existing ExtPhoneInfo:
```
INSERT INTO ExtPhoneInfo#TEMP (phoneInfoId) VALUES (3) 
```

The CData Tableau Connector for SAP SuccessFactors will assume that the ExtPhoneInfo and ExtPersonalInfo already exist and will only link to the existing references since only the primary keys were specified for either. When more than just the primary key is defined, such as the examples for ExtAddressInfo and ExtEmailInfo, the CData Tableau Connector for SAP SuccessFactors will attempt to create new entries - triggering the deep insert.

Inserting the Entity

In the INSERT statement for the base entity, reference the temporary tables in the LinkedExtAddressInfo, LinkedExtEmailInfo, LinkedExtPersonalInfo, and LinkedExtPhoneInfo columns:

INSERT INTO ExternalUser (password, productName, userId, userName, LinkedExtAddressInfo, LinkedExtEmailInfo, LinkedExtPersonalInfo, LinkedExtPhoneInfo) 
VALUES ('password', 'productname', 'newuserid', 'newusername', 'ExtAddressInfo#TEMP', 'ExtEmailInfo#TEMP', 'ExtPersonalInfo#TEMP', 'ExtPhoneInfo#TEMP')

Code Example

Below is the complete code to create the new ExternalUser:

Connection conn = DriverManager.getConnection(connectionStr);
Statement stat = conn.createStatement();
stat.executeUpdate("INSERT INTO ExtAddressInfo#TEMP (address1, city, country, zipCode) VALUES ('123 Fake Street', 'Springfield', 'USA', '58008')");

stat.executeUpdate("INSERT INTO ExtEmailInfo#TEMP (EmailAddress, EmailType, isPrimary) VALUES ('[email protected]', 'business', true)");

stat.executeUpdate("INSERT INTO ExtEmailInfo#TEMP (EmailAddress, EmailType, isPrimary) VALUES ('[email protected]', 'personal', false)");

stat.executeUpdate("INSERT INTO ExtPersonalInfo#TEMP (personalInfoId) VALUES (5)");

stat.executeUpdate("INSERT INTO ExtPhoneInfo#TEMP (phoneInfoId) VALUES (3)");

stat.executeUpdate("INSERT INTO ExternalUser (password, productName, userId, userName, LinkedExtAddressInfo, LinkedExtEmailInfo, LinkedExtPersonalInfo, LinkedExtPhoneInfo) 
VALUES ('password', 'productname', 'newuserid', 'newusername', 'ExtAddressInfo#TEMP', 'ExtEmailInfo#TEMP', 'ExtPersonalInfo#TEMP', 'ExtPhoneInfo#TEMP')");

stat.close();