How to implement support for toupper and tolower in $filter
Introduction
Today I got a question whether the function toupper( ) is supported with $filter in SAP Gateway.
The answer is no, but you can handle the filter tree yourself in the GET_ENTIYSET method of your data provider extension class.
This wouldn’t help in the concrete context of the question that I got because here toupper( ) should be used with an OData Service that was published via the Annotation @OData.publish : true.
But the Referenced Data Source approach would help here because we can handle the toupper( ) call in the data provider extension class.
If the Statement doesn’t match our requirements we will call the get_entityset method in the super class which is handled by the SADL Framework.
However if you try to use toupper( ) for another property you will stll get an error message.
Solution 1: Referenced data source to the rescue
For this blog I created a Service Builder project ZGW_TOUPPER based on the CDS view SEPM_I_Product_E using Referenced Data Source (RDS) with just one entity type SEPM_I_Product_EType.
The following coding only supports $filter statements like the following:
/sap/opu/odata/SAP/ZGW_TOUPPER_SRV/SEPM_I_Product_E?$filter=toupper(ProductCategory) eq ‘SPEAKERS’&$format=json
The Response would look like the following excerpt of a JSON response:
{
"d" : {
"results" : [
{
"__metadata" : {
"id" : "http://vegtwy1mst.wdf.sap.corp:50008/sap/opu/odata/SAP/ZGW_TOUPPER_SRV/SEPM_I_Product_E('')",
"uri" : "http://vegtwy1mst.wdf.sap.corp:50008/sap/opu/odata/SAP/ZGW_TOUPPER_SRV/SEPM_I_Product_E('')",
"type" : "ZGW_TOUPPER_SRV.SEPM_I_Product_EType"
},
"ID" : "",
"Product" : "HT-1090",
"Width" : "12.000",
"Depth" : "10.000",
"DimensionUnit" : "CM",
"ProductPictureURL" : "/sap/public/bc/NWDEMO_MODEL/IMAGES/HT-1090.jpg",
"ProductValueAddedTax" : 1,
"Supplier" : "100000044",
"ProductBaseUnit" : "EA",
"Weight" : "3.000",
"WeightUnit" : "KG",
"Product_Text" : "",
"ProductType" : "PR",
"ProductCategory" : "Speakers",
"CreationDateTime" : "\/Date(1487150757000+0000)\/",
"LastChangedDateTime" : "\/Date(1487150757000+0000)\/",
"Price" : "39.00",
"Currency" : "EUR",
"Height" : "16.000"
},The GET_ENTITYSET method producttypeset_get_entityset retrieves the filter tree io_tech_request_context->get_filter_expression_tree( ).
It checks whether the function being used is actually toupper( ) and for simplicity we only support one property.
It then retrieves the filter string and we are using a new option available only as of 751 to use toupper in the WHERE clause of a OpenSQL Statement.
SELECT * FROM sepm_i_product_e WHERE upper( productcategory ) = @lv_literal INTO CORRESPONDING FIELDS OF TABLE @et_entityset.
If this prerequisites are not met we are calling the get_entityset method of the super calls where the SADL framework will handle our request.
A nice blog about case insensitive search by my colleague Horst Keller can be found here:
ABAP News for Release 7.51 – Case Insensitive Search in SQL and Other New Functions
But as said, if you would use toupper( ) for another property an error message would be raised.
Solution 2: CDS view development
Since the described use case was to use an OData Service that uses a CDS view as a data source a much easier approach would be to create a new CDS view with a new column upper_text that performs the conversion into upper text on data base Level.
This way you could either use OData.publish : true or Referenced Data Source without the need to write any specific code in the DPC_EXT class.
upper(text) as upper_text
This option does however require SAP AS ABAP 751 as described in the aforementioned blog from Horst Keller.
Source code
METHOD producttypeset_get_entityset.
DATA : lo_filter_tree TYPE REF TO /iwbep/if_mgw_expr_node,
lo_left_node TYPE REF TO /iwbep/if_mgw_expr_node,
lo_right_node TYPE REF TO /iwbep/if_mgw_expr_node,
lo_binary TYPE REF TO /iwbep/if_mgw_expr_binary,
lo_function TYPE REF TO /iwbep/if_mgw_expr_function,
lo_property TYPE REF TO /iwbep/if_mgw_expr_property,
lo_literal TYPE REF TO /iwbep/if_mgw_expr_literal,
lt_param_tab TYPE /iwbep/if_mgw_expr_function=>parameter_t,
lv_operator TYPE string,
lv_function TYPE string,
lv_literal TYPE string,
lv_property TYPE string,
lv_supported_filter_string TYPE string,
lv_filter_error TYPE string,
lv_wrong_filter TYPE abap_bool.
CONSTANTS : lc_kind_binary TYPE c LENGTH 1 VALUE 'B',
lc_kind_literal TYPE c LENGTH 1 VALUE 'C',
lc_kind_function TYPE c LENGTH 1 VALUE 'F',
lc_kind_property TYPE c LENGTH 1 VALUE 'P'.
lo_filter_tree = io_tech_request_context->get_filter_expression_tree( ).
IF lo_filter_tree IS BOUND.
IF lo_filter_tree->kind = lc_kind_binary. " 'B'
lo_filter_tree->prepare_converted_values( ).
lo_binary ?= lo_filter_tree.
lv_operator = lo_binary->operator.
lo_left_node = lo_binary->left_operand.
lo_right_node = lo_binary->right_operand.
IF lo_left_node IS BOUND.
IF lo_left_node->kind = lc_kind_function. " 'F' .
lo_function ?= lo_left_node.
lv_function = lo_function->function.
IF lv_function <> 'toupper'.
lv_filter_error = 'Only touppper is supported. '.
lv_wrong_filter = abap_true.
ENDIF.
lt_param_tab = lo_function->parameters.
IF lt_param_tab IS NOT INITIAL.
IF lt_param_tab[ 1 ]->kind = lc_kind_property.
lo_property ?= lt_param_tab[ 1 ].
lv_property = lo_property->property_name.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
IF lv_property = 'PRODUCTCATEGORY'.
ELSE.
" raise error message that filter string does not match the expected format
" an additional property was found in the filter string
lv_filter_error = 'Property:' && lv_property && ' is not supported. '.
lv_wrong_filter = abap_true.
ENDIF.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
ENDIF.
IF lo_right_node IS BOUND.
IF lo_right_node->kind = lc_kind_literal. " 'F' .
lo_literal ?= lo_right_node.
lv_literal = lo_literal->literal_converted.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
ENDIF.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
ENDIF.
IF lv_wrong_filter = abap_true.
RAISE EXCEPTION TYPE /iwbep/cx_mgw_busi_exception
EXPORTING
textid = /iwbep/cx_mgw_busi_exception=>business_error_unlimited
message_unlimited = lv_filter_error && lv_supported_filter_string.
ENDIF.
SELECT * FROM sepm_i_product_e WHERE upper( productcategory ) = @lv_literal
INTO CORRESPONDING FIELDS OF TABLE @et_entityset.
ENDMETHOD.
Hi Andre,
I just wanted to share my opinion here on the proposed solutions. I had been searching around for the right approach to handle case-sensitive filters and was not able to get a feasible and optimised way.
If my back-end system is HANA DB and I want to leverage my OData service enablement based on CDS Views(either via Reference Data Source or OData Publish = true), then ideally SAP framework should be able to correctly parse my requested URI(ToUpper or ToLower or no case conversion) and handle the filter on its own,The entity might have many such search fields and handling them explicitly in DPC_EXT or creating additional upper case fields would not be feasible.
Example : <...ServiceURL>/ZC_RES_SUBLOB?$filter=toupper(LOBDescription) eq 'WATCH'&$format=json
If I debug the GET calls we see the SQL query is directed to the class CL_SQL_STATEMENT method EXECUTE_QUERY with the generated SQL Statement. The logic of generating this SQL statement, for HANA DB, is in the local class LCL_EXECUTOR_HDB implementation.
So we would like the SAP framework to convert the below generated SQL to appropriate HANA functions UPPER or LOWER based on the request.
This way we completely rely on the standard framework to do the needful, rather than adding custom code or additional non-business related fields in the service.
Thanks & Regards,
Samson