Skip to Content
Technical Articles
Author's profile photo Pieter Janssens

Resolving date serialization issues when using an external OData v2 service with CAP

Introduction

After reading this blog you will be able to resolve the date formatting serialization issues that occur when consuming an OData v2 external web service from within your CAP project. The solution is generic and works for all properties of type Date or DateTime(Offset), regardless the name of the property.

If you are new to remote services in CAP, I recommend reading this excellent blog series by Jhodel Cailan or the handsonsapdev episode 72 by DJ Adams.

In its most simple form you can use an external service inside your cap project and forward the incoming OData query to the remote service as such:

this.on('READ', User, request => {
	return service.tx(request).run(request.query);
});

Next to OData v4, cds also supports OData v2 for external services. I quickly learned that mixing these two is not plug-and-play. I was implementing an external service for the User entity from SuccessFactors in my CAP project, when I first noticed a serialization issue.

<message>
An error occurred during serialization of the entity collection. An error occurred during serialization of the entity with the following key(s): userId: <removed>. 

Serialization of the 'lastModified' property failed. Invalid value /Date(1592906797000)/ (JavaScript string). A string value in the format YYYY-MM-DD must be specified as value for type Edm.Date.
</message>

I had to look into the specific differences between the OData versions to understand what I had to do next. OData v2 formats its dates in JavaScript like epoch format, while OData v4 formats the date in the ISO-8601 standard (YYYY-MM-DDTHH:mm:ss.sssZ).

EDM Data Type OData v2 values OData v4 values
Edm.Date /Date(1593785119732)/ 2020-07-03
Edm.DateTime /Date(1593785119732)/ 2020-07-03T14:05:19.732Z
Edm.DateTime /Date(1593785119732+0000)/ 2020-07-03T14:05:19.732Z
Edm.DateTime /Date(-241750800000)/ 1962-05-04T23:00:00.000Z

Currently, cds is not taking care of the conversion between v2 and v4 date formats. I have been informed by Olena Timrova that the automatic conversion is on the roadmap, but it’s unknown when this will be implemented.

Implementing a Conversion Logic

To resolve this issue I had to make sure to return OData v4 compatible values when reading data and to supply OData v2 date formats when I am writing back changes to an external OData v2 service. I came up with the following generic approach which can be used with any entity.


module.exports = async srv => {

    const ext = await cds.connect.to('SF_User')

    const { User } = ext.entities;

    const convertEpochToIsoDate = (jsEpoch) => {
        return convertEpochToIsoDateTime(jsEpoch).substring(0,10)
    }

    const convertEpochToIsoDateTime = (jsEpoch) => {
        return new Date(eval(jsEpoch.replace(/\//g,""))).toJSON()
    }

    const convertIsoToJSEpoch = (isoDateTime) => {
        return "/Date(" + (new Date(isoDateTime)).getTime() + ")/"
    }

    const convertDatesv2Tov4 = (req,entity) => {
        Object.keys(entity).forEach( key => {
            if (!/\/Date\(-?\d+(\+\d+)?\)\//.test(entity[key])) 
                return
            if(req.target.elements[key].type === "cds.Date")
                entity[key] = convertEpochToIsoDate(entity[key])
            if(req.target.elements[key].type === "cds.Timestamp")
                entity[key] = convertEpochToIsoDateTime(entity[key])
        })
        return entity
    }

    const convertDatesv4Tov2 = (req) => {
        Object.keys(req.data).forEach( key => {
            if (req.target.elements[key].type === "cds.Date" || req.target.elements[key].type === "cds.DateTime")
                req.data[key] = convertIsoToJSEpoch(req.data[key])
        })
    }

    srv.on(['CREATE','UPDATE'], User, async req => {
        convertDatesv4Tov2(req)
        return await ext.tx(req).run(req.query)
    })

    srv.on('READ', User, async req => {
        const result = await ext.tx(req).run(req.query)
        result.map(entity => convertDatesv2Tov4(req, entity))
        return result;
    })

}

Reading

For all entities in the entity or entityset I loop over all its properties. Any property value that matches the format of a OData v2 date(time) value is identified after which I check defined CSN type and decide which conversion is necessary.

For the conversion I am using the JavaScript eval statement which will take any string and execute it as JavaScript code. This has to be taken with great caution as it can be a security vulnerability, but because I am checking for strict JavaScript epoch Date syntax beforehand I don’t think this can be exploited (let me know in the comments if you think otherwise).

Writing

For all entries in the entity that are of type cds.Date or cds.DateTime I convert the data to the format “/Date(<epoch>)/”, which is then submitted to the OData v2 service.

Conclusion

The solution I have shown will work for CREATE, READ and UPDATE. There is additional custom logic required to support $filter on properties of the type date or datetime. In the future custom logic for these conversions for date formats between OData v2 and v4 will become obsolete (once it is taken care off by cds itself). Until then, a custom implementation as shown in this article can be used to take care of this on the service implementation level.

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jhodel Cailan
      Jhodel Cailan

      Hi Pieter Janssens ,

      Awesome blog you have here! Thanks for sharing what you've learned about cap and sharing your solution.

      Thanks for the mention by the way!

      Cheers!

      Jhodel

      Author's profile photo Volker Buzek
      Volker Buzek

      excellent analysis, explanation and showcase ?

      and wouldn't that be cool if CAP were OS and you could just PR your generic converter?!?

      another use-case to accelerate and push hard for the Open Source move of CAP Daniel Hutzel Sebastian Schmidt Former Member Gregor Wolf DJ Adams

      Author's profile photo Venu Ravipati
      Venu Ravipati

      Hi Pieter Janssens,

      Great blog on the date formats in different versions and the conversion requirements. Helped a lot.

      Thank you,

      Venu

       

      Author's profile photo Florian Bähler
      Florian Bähler

      Great! Thank you for sharing!!

       

      Best regards

      Author's profile photo Holger Schäfer
      Holger Schäfer

      Hi Pieter,

      thx for sharing, I ran into same issue!

      Additionally, i am also using some helpers:

      NPM package https://www.npmjs.com/package/object-path

      const objectPath = require("object-path");

      to be used for path like access and manipulate CQN props.

      Also using the reflection API is quite helpful, needing this declarations:

          const db = await cds.connect.to('db')   // connect to database service    
          const csn = await cds.load('db')        // load model from source
          let reflected = cds.reflect(csn)        // reflected model    
      

      Here is a usage example, supporting request forwarding from CAP v4 to ExternalService using v2.

          this.on('READ', 'SalesOrder', async (req) => {
              // handle keys, if specified to differ between collection and plain object
              const id = req.data.SalesOrder
              // get request as CQN query object that should be pass throughed
              let { query } = req
      
              if (id) {
                  // default orderBy not allowed
                  objectPath.del(query, 'SELECT.orderBy')
                  // remove select columns to remove $select and return all props
                  objectPath.del(query, 'SELECT.columns')
              } else {
                  // delete query.SELECT.columns because API returns error with setted values
                  objectPath.del(query, 'SELECT.columns')
                  // add where filter conditions
                  objectPath.ensureExists(query, 'SELECT.where', []);
                  query.SELECT.where.push(
                      { ref: ['SoldToParty'] }, '=', { val: '150000' }, "and",
                      { ref: ['SalesOrderType'] }, '=', { val: 'ZUTA' }
                  )
      
                  /* $search currently not supported
                   * where: [{ func: 'contains', args: [ {val:'abc'}
                   * Feature not supported: .func in where clause of SELECT statement. 
                   * Workaround: transform it by using filter
                  */
              }
      
              // share request context with the external service 
              // and use CQN input from fluent query API
              const result = await soSrv.tx(req).run(query)
              let results = (id) ? [result] : result
      
              // post process results for neccessary odata v2 to v4 conversion
              results.map(function (element) {
                  // https://blogs.sap.com/2020/07/08/resolving-date-serialization-issues-when-using-an-external-odata-v2-service-with-cap/
                  element = convertDatesv2Tov4(req, element)
                  // remove empty nav properties to get rid of serialization error
                  // -> The entity contains data for 'to_Item' which does not belong to the structural or navigation properties of the type
                  reflected.foreach('Association',
                      e => objectPath.del(element, e.name),
                      A_SalesOrder.elements
                  )
      
                  return element
              })
      
              return (id) ? results[0] : results
          })

      Even if SAP is recommenting SAP Cloud SDK for this, this is quite nice and transparent to forward the same request with all used uri params and beeing able to add just custom filter.

      I think, this can not easily be done with SAP Cloud SDK JavaScript, because it does not support CQN Queries. 

      For everthing with POST i am currently using @JHodel npm cdse solution to be able to easily support POST with CSRF-Token instead of beeing forced to us the cloud sdk for small requests

      Alternatively for POST i would currently also recommend the SAP Cloud SDK.

      Maybe this is a time saver, if you want to do something similar.

      Best Regards

      Holger

      Author's profile photo Rufat Gadirov
      Rufat Gadirov

      Hi Pieter Janssens

      thank you for your blog resolving date serialization issues. I both tested your solution but found another as well. Basically, to prevent the serialization issue I just modified the csn-flile, meaning that I changed for the OData V2 external CSN the datetypes from cds.Timestamp to cds.Date for every required date data element.

      This solved the issue for me very easily.

      BR
      Rufat

      Author's profile photo Sujit Kumar
      Sujit Kumar

      Hi Pieter Janssens,

       

      I am facing issue in converting the $filter values.

      I am using an external gateway sevice to call from cap. But the date is being converted to V4 format.

       

      It would be helpful if you can guid me on this.

       

      Thanks

      Sujit