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.
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
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
Hi Pieter Janssens,
Great blog on the date formats in different versions and the conversion requirements. Helped a lot.
Thank you,
Venu
Great! Thank you for sharing!!
Best regards
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
to be used for path like access and manipulate CQN props.
Also using the reflection API is quite helpful, needing this declarations:
Here is a usage example, supporting request forwarding from CAP v4 to ExternalService using v2.
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
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
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