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

[pieterjanssens]

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 jhodel18 or the handsonsapdev episode 72 by dj.adams.sap.

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_ 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.