Skip to Content
Technical Articles

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.

5 Comments
You must be Logged on to comment or reply to a post.
  • 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