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. Alternatively you can provide a JSON/XML aggregate to instead of the temporary table.

Example: Deep Inserts in SAP Gateway

For example, consider the GWSAMPLE_BASIC service. On GWSAMPLE_BASIC, there is a SalesOrder table. When creating a SalesOrder, you must specify a new SalesOrderLineItem and an existing BusinessPartner. 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.

SalesOrderLineItems: The following statements add two SalesOrderLineItems to the SalesOrder:

INSERT INTO SalesOrderLineItems#TEMP (ProductID, Quantity) VALUES ('P001', 15)

INSERT INTO SalesOrderLineItems#TEMP (ProductID, Quantity) VALUES ('P002', 10)

BusinessPartners: The following statement specifies the existing BusinessPartner:
```
INSERT INTO BusinessPartners#TEMP (BusinessPartnerID) 
VALUES ('BP001')
```

Note: BusinessPartner can also be specified via the CustomerID property directly on the SalesOrder. This example is not using that column due to the possibility that a suitable foreign key may not always be available.

The CData Tableau Connector for SAP Gateway will assume that the BusinessPartner already exists and will only link to the existing references since only the primary keys was specified. When more than just the primary key is defined, such as the example for SalesOrderLines, the CData Tableau Connector for SAP Gateway 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 LinkedToLineItems and LinkedToBusinessPartner columns:

INSERT INTO SalesOrders (Note, LinkedToLineItems, LinkedToBusinessPartner) 
VALUES ('New sales order', 'SalesOrderLineItems#TEMP', 'BusinessPartners#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 SalesOrderLineItems#TEMP (ProductID, Quantity) VALUES ('P001', 15)");

stat.executeUpdate("INSERT INTO SalesOrderLineItems#TEMP (ProductID, Quantity) VALUES ('P002', 10)");

stat.executeUpdate("INSERT INTO BusinessPartners#TEMP (BusinessPartnerID) VALUES ('BP001')");

stat.executeUpdate("INSERT INTO SalesOrders (Note, LinkedToLineItems, LinkedToBusinessPartner) VALUES ('New sales order', 'SalesOrderLineItems#TEMP', 'BusinessPartners#TEMP')");

stat.close();

Using Aggregates

Below is an example of providing multiple Order Line Items as a JSON aggregate:

INSERT INTO SalesOrderSet (Note, CustomerID, LinkedToLineItems) VALUES ('New sales order', '0100000001', '
    [
      {
          "ProductID": "HT-1001",
          "Quantity": 2,
          "Note": "Test aggregate 1",
          "DeliveryDate": "2024-12-20 13:20:10"
      },
      {
          "ProductID": "HT-1001",
          "Quantity": 4,
          "Note": "Test aggregate 2",
          "DeliveryDate": "2024-12-20 13:20:10"
      }
  ]
  ')