You can translate the question and the replies:

REST Webservice as DataSource with optional parameters

I am using a REST Webservice as a Datasource. To get the data, the Webservice GET function takes three Parameters "accountingClientId", "customerId" and "invoiceId". The first two are mandatory, the third is optional. I managed to do this by using ^ExecuteIfIsNotNull function in the URI of the GET, https://service.company.com/Finance/OpenedPost?accountingClientId=@{accountingClientId}&customerId=@{customerId}^ExecuteIfIsNotNull("&invoiceId=",@invoiceId,"","") and setting "invoiceId" to OPT in the view search method by VQL: CREATE WRAPPER JSON "ds_egecko_financeOpenPost_4" FOLDER = '/01 - connectivity/02 - base views' DATASOURCENAME="ds_egecko_financeOpenPost" TUPLEROOT '/JSONFile' ROUTE HTTP 'http.CommonsHttpClientConnection,120000' GET 'OpenedPost?accountingClientId=@{accountingClientId}&customerId=@{customerId}^ExecuteIfIsNotNull("&invoiceId=",@invoiceId,"","")' OUTPUTSCHEMA ("JSONFile" = 'JSONFile' : REGISTER OF ( "INVOICEID" = 'INVOICEID' : 'java.lang.String' (OPT) EXTERN, "CUSTOMERID" = 'CUSTOMERID' : 'java.lang.String' (OBL) (DEFAULTVALUE='1009283') EXTERN URIPARAM, "ACCOUNTINGCLIENTID" = 'ACCOUNTINGCLIENTID' : 'java.lang.String' (OBL) (DEFAULTVALUE='60') EXTERN URIPARAM, "JSONArray" = 'JSONArray' : ARRAY OF ( "JSONArray" = 'JSONArray' : REGISTER OF ( "opNr" = 'opNr' : 'java.lang.String', "customerId" = 'customerId' : 'java.lang.Integer', "openAmountHauseCurrency" = 'openAmountHauseCurrency' : 'java.lang.Long', "openAmountInvoiceCurrency" = 'openAmountInvoiceCurrency' : 'java.lang.Long', "dunningDate" = 'dunningDate' : 'java.lang.String', "invoiceId" = 'invoiceId' : 'java.lang.String', "invoiceDate" = 'invoiceDate' : 'java.lang.String', "invoiceType" = 'invoiceType' : 'java.lang.String', currency = 'currency' : 'java.lang.String', "opText" = 'opText' : 'java.lang.String' ) ) ) ); CREATE TABLE "ds_egecko_financeOpenPost_4" I18N de ( "INVOICEID":int, "CUSTOMERID":int, "ACCOUNTINGCLIENTID":int, "JSONArray":"ds_egecko_financeOpenPost_JSONArray" ) FOLDER = '/01 - connectivity/02 - base views' CACHE OFF TIMETOLIVEINCACHE DEFAULT ADD SEARCHMETHOD "ds_egecko_financeOpenPost"( I18N de CONSTRAINTS ( ADD "INVOICEID" (=) OPT ONE ADD "CUSTOMERID" (=) OBL ONE ADD "ACCOUNTINGCLIENTID" (=) OBL ONE ADD "JSONArray" NOS ZERO () ADD "JSONArray" NOS ZERO () ADD "JSONArray"."opNr" NOS ZERO () ADD "JSONArray"."customerId" NOS ZERO () ADD "JSONArray"."openAmountHauseCurrency" NOS ZERO () ADD "JSONArray"."openAmountInvoiceCurrency" NOS ZERO () ADD "JSONArray"."dunningDate" NOS ZERO () ADD "JSONArray"."invoiceId" NOS ZERO () ADD "JSONArray"."invoiceDate" NOS ZERO () ADD "JSONArray"."invoiceType" NOS ZERO () ADD "JSONArray".currency NOS ZERO () ADD "JSONArray"."opText" NOS ZERO () ) OUTPUTLIST ("JSONArray" ) WRAPPER (json "ds_egecko_financeOpenPost_4") ); My Problem is now, that, although I get a complete result from the view, the returned INVOICEID is always NULL. It works for the other two parameters.
user
27-09-2022 10:59:00 -0400

1 Answer

Hi, From the description, I understand that you have created a REST web service as a data source with two mandatory parameters and one optional parameter. The optional parameter is always returned as null. In general, when I create a data source with [interpolation variables](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#using-the-whereexpression-variable), the base views created over this source will have a field for each interpolation variable. The interpolation variable is added to the view, not in the underlying schema of the database. For your scenario, the optional parameter is null because you have passed the value only for the two mandatory fields whereas the optional parameter takes null as the default value. You could refer to the [Using the WHEREEXPRESSION Variable](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#using-the-whereexpression-variable) section of the Virtual DataPort Administration Guide for more information. Hope this helps!
Denodo Team
28-09-2022 08:31:00 -0400
You must sign in to add an answer. If you do not have an account, you can register here