The driver exposes tables for every entity set and singleton defined on the SAP ByDesign root URL. Entities on these tables may be inserted, updated, or deleted using standard SQL insert, update, or delete statements.

Executing Deep Inserts with SQL

The driver 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

For example, consider a table, Orders. To create a new Order, you specify the Products ordered, Customer, Employee, and Shipper. To do so, you need to 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.

• Customer: The following statement creates a new Customer:
  

  INSERT INTO Customers#TEMP (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country, Phone, Fax) 
  VALUES ('VINET', 'Vins et alcools Chevalier', 'Paul Henriot', 'Accounting Manager', '59 rue de l''Abbaye', 'Reims', '51100', 'France', '26.47.15.10', '26.47.15.11')

• Order Details: The following statements add two Products to the Order:
  

  INSERT INTO Order_Details#TEMP (ProductID, UnitPrice, Quantity, Discount) VALUES (72, 34.80, 5, 0)
  
  INSERT INTO Order_Details#TEMP (ProductID, UnitPrice, Quantity, Discount) VALUES (42, 9.80, 10, 0)

• Employee: The following statement specifies the existing Employee:
  

  INSERT INTO Employees#TEMP (EmployeeID) 
  VALUES (5)

• Shipper: The following statement specifies the existing Shipper:
  

  INSERT INTO Shippers#TEMP (ShipperID) VALUES (3) 

The CData JDBC Driver for SAP ByDesign will assume that the Shipper and Employee 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 Customer and Order_Details, the CData JDBC Driver for SAP ByDesign 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 LinkedOrder_Details, LinkedCustomer, LinkedEmployee, and LinkedShipper columns:

INSERT INTO Orders (CustomerID, EmployeeID, ShipVia, ShipName, ShipAddress, ShipCity, ShipPostalCode, ShipCountry, OrderDate, LinkedOrder_Details, LinkedCustomer, LinkedEmployee, LinkedShipper) VALUES ('VINET', 5, 3, 'Paul Henriot', '59 rue de l''Abbaye', 'Reims', '51100', 'France', '07/04/1996', 'Order_Details#TEMP', 'Customers#TEMP', 'Employees#TEMP', 'Shippers#TEMP')

Code Example

Connection conn = DriverManager.getConnection("jdbc:sapbydesign:URL=https://my999999.sapbydesign.com;User=username;Password=password;ServiceType=AnalyticsService;ServiceName=servicename;");
Statement stat = conn.createStatement();
stat.executeUpdate("INSERT INTO Customers#TEMP (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country, Phone, Fax) VALUES ('VINET', 'Vins et alcools Chevalier', 'Paul Henriot', 'Accounting Manager', '59 rue de l''Abbaye', 'Reims', '51100', 'France', '26.47.15.10', '26.47.15.11')");

stat.executeUpdate("INSERT INTO Order_Details#TEMP (ProductID, UnitPrice, Quantity, Discount) VALUES (72, 34.80, 5, 0)");

stat.executeUpdate("INSERT INTO Order_Details#TEMP (ProductID, UnitPrice, Quantity, Discount) VALUES (42, 9.80, 10, 0)");

stat.executeUpdate("INSERT INTO Employees#TEMP (EmployeeID) VALUES (5)");

stat.executeUpdate("INSERT INTO Shippers#TEMP (ShipperID) VALUES (3)");

stat.executeUpdate("INSERT INTO Orders (CustomerID, EmployeeID, ShipVia, ShipName, ShipAddress, ShipCity, ShipPostalCode, ShipCountry, OrderDate, LinkedOrder_Details, LinkedCustomer, LinkedEmployee, LinkedShipper) VALUES ('VINET', 5, 3, 'Paul Henriot', '59 rue de l''Abbaye', 'Reims', '51100', 'France', '07/04/1996', 'Order_Details#TEMP', 'Customers#TEMP', 'Employees#TEMP', 'Shippers#TEMP')");

stat.close();