Multi-Inserts & Transactions via Batch Request

[dmtine]

Hello,

I have two questions about OData in connection with the Teiid / WildFly 11.

1) In my case the Teiid is connected to a database of a MariaDB Server 10.2 or alternatively to a MS SQL Server 2017.

Via OData I want to insert several records with one request into a table x.

For this I have found the procedure "Batch Request" in the documentation.

However, I am not completely satisfied with the performance when it comes to a large number of data records. For comparison: Using SQL I can also insert several records with a single insert statement.

Is there a comparable possibility under OData/Teiid to upload several records with a single post-request?

2) How can I perform transactions with OData/Teiid?

I have tried the following: In a BatchRequest, I tried to insert multiple records within a changeset with multiple post-requests. This worked perfectly. However, to test the transaction, I have included a syntax error in the last post-request. I expected no record to be created in the database due to the error. However, I noticed that all previous records were created. What did I do wrong or how can I ensure a real transaction within a changeset?

Many greetings

DMTINE

[shawkins]

> Is there a comparable possibility under OData/Teiid to upload several records with a single post-request?

No there is nothing similar to a JDBC prepared statement batch.  OData batch processing is simply a collection of requests.  Our OData framework, olingo, simply breaks a batch request down into individual requests for our processing.

> I have tried the following: In a BatchRequest, I tried to insert multiple records within a changeset with multiple post-requests. This worked perfectly. However, to test the transaction, I have included a syntax error in the last post-request. I expected no record to be created in the database due to the error. However, I noticed that all previous records were created. What did I do wrong or how can I ensure a real transaction within a changeset?

We will create a wrapping XA transaction for each change set.  However looking at the Olingo BatchRequest logic, it will still commit the transaction based upon the outcome of only the first change.  So it looks like there's a bug there:  olingo-odata4/BatchRequest.java at 1fef3a131ea4cc334151a7f10b67cab6bb424239 · apache/olingo-odata4 · GitHub

Can you log a Teiid issue, and we'll follow up with the olingo fix?

[shawkins]

I've created https://issues.apache.org/jira/browse/OLINGO-1355 to track this on the odata side.  If the error is specifically a 400 response code, then the transaction will still be committed.  Any other error code greater than 400 will result in a rollback.