Tables

The driver 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 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 may either submit JSON / XML data, or you may create a temporary table for the navigation property and then reference the temporary table in the insert to the base table. Sumit the XML / JSON or reference the temporary table in the appropriate navigation property column on the base table. Each navigation property column is prefixed with the word "Linked".

Example: Deep Inserts using XML / JSON

To submit XML or JSON data, simply supply the values for the table the navigation property is referencing in XML or JSON format. If you are familiar with the OData standard, you should not be submitting values in the standard. The XML / JSON used here is simply a means of supplying multiple values ot the CData JDBC Driver for OData.

For example, consider the Orders table in Northwind odata.org test service. To create a new Order, you specify the Products ordered, Customer, Employee, and Shipper. To do so, you need to specify the Customer, Order_Details, Shipper, and Employee navigation properties.

Customer: The following XML represents a new Customer:

  <Row>
    <CustomerID>VINET</CustomerID>
	<CompanyName>Vins et alcools Chevalier</CompanyName>
	<ContactName>Paul Henriot</ContactName>
	<ContactTitle>Accounting Manager</ContactTitle>
	<Address>59 rue de l'Abbaye</Address>
	<City>Reims</City>
	<PostalCode>51100</PostalCode>
	<Country>France</Country>
	<Phone>26.47.15.10</Phone>
	<Fax>26.47.15.11</Fax>
  </Row>

Order_Details: The following JSON add two Products to the Order:

  [
    {
	  "ProductID": 72,
	  "UnitPrice": 34.80,
	  "Quantity": 5,
	  "Discount": 0
	},
	{
	  "ProductID": 42,
	  "ProductID": 9.80,
	  "ProductID": 10,
	  "ProductID": 0
	}
  ]

Employee: The following XML specifies the existing Employee:
```
  <Row>
    <EmployeeID>5</EmployeeID>
  </Row>
```
Shipper: The following JSON specifies the existing Shipper:
```
  [
    {
      "ShipperID": 3
    }
  ]
```

In order to execute the insert, simply reference or include as string literals the complete XML / JSON. For example:

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', '{ ... }', '<Row>...</Row>', ?, ?)

Example: Deep Inserts using Temporary Tables

If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory. Keeping with the Northwind example, 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 OData 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 OData 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

Below is the complete code to create the new Order:

Connection conn = DriverManager.getConnection("jdbc:odata:URL=http://services.odata.org/Northwind/Northwind.svc;");
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();