• Home|
  • Blog|
  • How to replicate database transactions in OData environment?

ERP Guidebook

How to replicate database transactions in OData environment?

November 20, 2022

In the very first article on OData in our blog, we presented design rules engineered for this type of REST API to provide clear and concise answers to the respective questions for full deployment. We will continue to explore the subject of OData in our series and explain more about how to handle operation sequences that need to be performed as a business transaction.

Relying on Novacura’s knowledge and experience, we have the opportunity to engage our experts in an interview that will help us create a rules-based presentation. For the following article, we interviewed our expert Albin Lundberg, Novacura Manager Flow Solution.

In this article, we will review alternative approaches to communicating complementary dependencies and constraints in OData.

From the previous blog, we know that OData is a typical REST API that is stateless. As a result, you cannot roll back a previously made API call (or a series of calls). What if there is such a need?

It depends on the integration case and requirements. But generally, the client must track each transaction. In some cases, you can do batch calls with several transactions in one call. This is however limited by the implementation of the service or projection itself. From what I have seen there’s seldom support for the creation of a complex structure with just one batch call. Parent and child entities must be created in separate calls. Calls for creating child entities often require the key value of the parent.
If the client software doesn’t support the tracking of calls and rollback on any creation errors, there’s the option to do a database customization and deploy a Custom Projection.
Hopefully, we will see better support for complex payloads in future versions of IFS Cloud.

The example above shows a sample batch request with the operations as follow:  Retrieve → a change set that contains the following requests: Insert and Update → a second retrieve request

Source: https://www.odata.org/

What if there is a whole set of operations that are executed sequentially and are treated as a single business transaction in the real world (and were supported by database transactions in the previous PLSQL-based API)? What to do then?

As mentioned above on the topic of rollback. The same rules apply to sequential operations. Sometimes Batch calls are possible if the projection supports it, Projection Configurations can be a solution for creating new basic entity relationships. But in most cases, you must do each API method call individually. If standard API calls still aren’t enough for some reason your last resort is deploying custom projections.

Let’s take the example that you want to create a customer in a transaction and add several options like bank accounts for this one customer. How to track a full entity structure in IFS to not lose data like bank accounts or/and addresses?

The client system needs to track if the full entity structure is created or not. The fact that a single PL/SQL block can result in many OData calls results in new demands on the client or middleware. Not only individual calls; but the business logic also needs to be implemented in a different way.

Can you somehow estimate how developers would handle “transactions” by manually remembering all the steps performed (to take off), and how often is this supported by IFS API as an operation?

They are mainly built to support the Aurena Client. Not built for integration. In the latest IFS cloud versions, Premium APIs have surfaced. These projections are meant to ease third-party integration. When writing this, none of our solutions have utilized them but they will hopefully grow in number and improve third-party integration with the IFS cloud.

When you interact with IFS using PL/SQL API, you normally open database transactions. You modify and add new records to different tables, and you commit it. Before committing these operations, some other systems will see these changes. Operations are immediately visible in IFS, even when you haven’t finished the transaction, and even if you would need to roll it back when something went wrong. This might cause a data consistency problem.

Is there any way to perform operations in a “test-fly” mode and confirm them at once, so that they would not be visible to other IFS modules until the last operation in this transaction was performed correctly? 

No, it’s not possible.

If we want to modify multiple records at once in IFS API operations by calling the API endpoint and passing the entire record as a parameter. How can you perform the reverse operation?

You can do batch calls for other operations than creation. So, if the batch is supported by the projection, you can do batch updates, batch deletes, etc. Using a middleware such as Novacura Flow, you can always do a loop over records to achieve the same result.

There’s no support for reversing actions. The client is responsible for tracking changes and request history.

Is it possible for IFS to provide very complex API endpoints that perform the entire transaction at once?

In some cases it’s possible to interact with more than one entity per call: hopefully, we’ll see more of that in the future. There are concepts in the OData protocol that supports tree structure payloads, called “deep insert”. This is not currently supported in IFS Cloud. IFS has premium APIs designed to ease integration and you can also do batch calls in some cases to reduce the number of API calls.

Summary

There are different ways to replicate database transactions in OData. There are also significant limitations that will not be handled by a single rollback action. The client may require new resources from its supporting services with new features. The needs of REST API clients may evolve, which will define the future development of OData and exceed its functionality at the same time.

In the next article, we will continue exploring OData and its parameters. We will review other alternatives to OData in IFS Cloud. To learn more about the IFS Cloud, OData, and Novacura services, subscribe to our blog and view related posts.

INTERESTED IN LEARNING MORE ABOUT IFS CLOUD ODATA?

The article you are about to read is part of a broader series on the IFS Cloud OData API. Here's a link to the main article, which lists all the major barriers you may encounter when upgrading to IFS Cloud:

 

Introduction – barriers you can meet when moving to IFS Cloud and OData API

 

Below we present a whole list of articles belonging to the IFS Cloud OData series:

  1. The OData basics in the context of IFS Cloud
  2. How to replicate database transactions in OData for IFS Cloud
  3. Alternatives for OData in IFS Cloud
  4. Authentication, Authorization and Initialization in the OData API of IFS Cloud
  5. OData Efficiency with the remote cloud installation
  6. IFS customizations in OData
  7. Dealing With Legacy Systems
Contact our expert
Table of contents
Roll back in OData? Set of operations? A customer in a transaction How to handle transactions? Perform a “test-fly”? Modify records in IFS API Complex API endpoints Summary

Do you have a question?:

Please fill out this form