CAP: Change sort order type for HANA

[iashishsingh]

Hello,

When using SAP HANA with CAP on OData v4, the order by queries seem to be using the Dictionary order, case sensitive, accent sensitivesort order type. Is there a way to change this to Dictionary order, case insensitive via CAP? Or is it something that can only be configured at the database configuration level?

The type of sort orders are mentioned here

Thanks!

[gregorw]

Maybe this HANA Cloud documentation helps: BINARY SORT AS DEFAULT DICTIONARY SORTING

[iashishsingh]

Thanks gregorw! This was very helpful. Since every read call is a new session (is it?), would it be a good idea to set the session variable before every read on the service level?

this.before("READ", async (req) => {     
    await cds.tx(req).run(`SET 'CASE_SENSITIVE' = 'FALSE'`); 
})

[iashishsingh]

On further tests, looks like running it before all the READ queries is a bad idea as it interferes with all kind of views which are dependent on case based usage and it adversely affects the outcomes. It might a prudent to only call it for the desired READ scenarios and switch it off afterwards.

Please let me know if you have any other suggestions. Thanks!

[iashishsingh]

Hi david.kunz2,

Is there a way to support/achieve this via CDS?

Many thanks!