Improving SAP Integration Suite with a sorting mechanism for DataStore entries

[MarianVatafu]

Introduction

Few days ago came to me a requirement to reprocess a number of entries from a DataStore. I said no problem, we could do this in two ways : Using the Select operator or using the SAP Integration Suite's DataStores API.

Soon, I realized that none of these 2 options would do the job.

Option 1 would always extract the last entry created. In my case, the entry was getting deleted after the Select step, but after that put back into the DataStore because the process was failing. This was resulting an infinite loop of processing the same entry over and over. 

Besides that, we all know that the Select operator has a limitation that it's able to fetch only xml-based content, and if some of you want to store JSON content, the step will fail.

Option 2 was my only hope, and with full confidence I went into Postman to check out the API. My enthusiasm didn't last very long, because as soon as I tried any query option I came across the message 

The query option $orderby is not supported, or even The query option $top is not supported. Went ahead and checked the API specifications on SAP Business Accelerator Hub and was completely shocked that none of the entities offered any query options or sorting mechanisms. In the meantime, I also realized that any call that is being made in our cloud tenant costs money, so even if it was working, doing that for a lot of times would increase the monthly licensing cost.

Is this the end ? Not on my watch !

Searching for a new method

I started reading the SAP Community blogs, and soon enough I came across r_herrmann's blog about How to read/write CPI Datastore from Groovy. Before explaining what he explained, I would re-iterate his disclaimer, that the functions are not officially documented, and you should be careful when using them. r_herrmann explains that are 2 methods of accessing the DataStore through Groovyscript : via DataStoreService-class and via DataStore-class.

Both methods are fully working, but with some limitations, both of them being able to output only the content of the entries. Besides that, the DataStoreService-class method needs to have an entry ID as input, and the DataStore-class will be able to output a certain amount of entries, even if you set a high limit here.

def dsEntryArray = dataStore.select("DatastoreName", "ContextName", 10)

Finding a potential candidate

I was already losing my hope, when I scrolled a bit down and decided to take a look at all the class details he provided. Taking them row by row, I saw that for DataStore-class, we have this, which looked really interesting.

public List<MetaData> selectMetaData(String storeName, String id, Date alertAt, int numRows, Long excludeRowBefore) throws DataStoreException
public List<MetaData> selectMetaData(String storeName, String qualifier, String id, Date alertAt, int numRows, Long excludeRowBefore) throws DataStoreException
public List<MetaData> selectMetaData(String storeName, String qualifier, String id, Date alertAt, int numRows, Long excludeRowBefore, boolean excludeNonRetry) throws DataStoreException

At that moment I decided to give it a try and see if I'm able to use any of these methods to my advantage.

I started with declaring the CamelContext and DataStore instance 

    def camelCtx = message.exchange.getContext()
    DataStore dataStore = (DataStore)camelCtx.getRegistry().lookupByName(DataStore.class.getName())

 After that, I called the selectMetaData method, while using a header as 'limit' for limiting dinamically the number of entries I am looking at. 

    // Fetch metadata for multiple entries
    def metaDataList = dataStore.selectMetaData("TestDataStore", null, null, null, limit, null)

In order to see what available options I have, I did :

def metaDataDetails = metaDataList.collect { meta ->
    meta.metaClass.methods*.name // Get available methods of MetaData
}.flatten().unique().join("\n")

// Return available methods as body
message.setBody(metaDataDetails)

and when I looked at the output, I realised that I'm on the good path :

equals
getClass
hashCode
notify
notifyAll
toString
wait
getId
getMplId
getQualifier
getStoreName
getTid
getVersion
getAlertDate
getCreationDate
getExpiresDate
getRetries
getRetryAt

with all these methods, I triggered again the script with this part updated, in order to see what can I use.

def metaDataDetails = metaDataList.collect { meta ->
    """ID: ${meta.getId()}
    MPL ID: ${meta.getMplId()}
    Qualifier: ${meta.getQualifier()}
    Store Name: ${meta.getStoreName()}
    TID: ${meta.getTid()}
    Version: ${meta.getVersion()}
    Alert Date: ${meta.getAlertDate()}
    Creation Date: ${meta.getCreationDate()}
    Expires Date: ${meta.getExpiresDate()}
    Retries: ${meta.getRetries()}
    Retry At: ${meta.getRetryAt()}
    """
}.join("\n")

// Return the extracted metadata
message.setBody(metaDataDetails)

and got this back :

ID: entry1
    MPL ID: AGfdWw9mRCQaOvCkUaWK5uvKoOlC
    Qualifier: null
    Store Name: TestDataStore
    TID: 433957
    Version: null
    Alert Date: 2025-03-23 12:26:55.996
    Creation Date: 2025-03-21 12:26:55.996
    Expires Date: 2025-04-20 12:26:55.996
    Retries: 0
    Retry At: null

 

Building my solution

This is it ! That's what I needed ! To my surprise, this method is actually auto-sorting the entries based on the Creation Date, but because I am having trust issues, i also included a sorting mechanism.

In the end, this is the final script, for you to use.

import com.sap.gateway.ip.core.customdev.util.Message
import com.sap.esb.datastore.DataStore
import com.sap.esb.datastore.Data
import org.osgi.framework.*
import groovy.xml.MarkupBuilder

Message processData(Message message) {
    
    def headers = message.getHeaders()
    def limit = headers.get("limit") as Integer;
    // Get CamelContext and DataStore instance
    def camelCtx = message.exchange.getContext()
    DataStore dataStore = (DataStore)camelCtx.getRegistry().lookupByName(DataStore.class.getName())
    
    def metaDataList = dataStore.selectMetaData("TestDataStore", null, null, null, limit, null)
    
    metaDataList.sort { a, b ->
    a.getCreationDate() <> b.getCreationDate() // Ascending order 

    // Extract the first entry and create a separate XML structure
    def firstEntryWriter = new StringWriter()
    def firstEntryXml = new MarkupBuilder(firstEntryWriter)

    if (metaDataList.size() > 0) {
        def firstMeta = metaDataList[0]
        firstEntryXml.root {
            entry {
                id(firstMeta.getId())
                creationDate(firstMeta.getCreationDate())
            }
        }
    }

    message.setBody(firstEntryWriter.toString())

    return message
}

It's taking the first entry ( based on Creation Date ), and outputs the entry id and the creation date. The ID can be used in my reprocessing mechanism to get, delete, reprocess, and re-add it back in the bottom of the list. This way, my interfaces will iterate through the complete list.

Conclusions

 In the end, I was able to find a solution to my problem using trial and error, scrolling through endless documentation. I am confident to say that in the future this will come as a feature for SAP Integration Suite on an official channel, and I won't have to do little tricks like this above to sort DataStore entries. 

If you have any questions, feel free to reply here, or even go to the dedicated Q&A Section for SAP Integration Suite.

Thank you for your time !