How to call a SQL getdate function in an OData URL?

[Jitendra_Kansal]

Hi Experts,

I just wanted to know if there is a way to call a function in an OData service URL?

e.g.

I have a table in the SQL backend system and to access a set of information, customer has provided a SQL statement like below:

select * from testTable where code='123' and pricedate>=getdate()-365 order by pricedate

Now, i have done with OData modeling for the given SQL table and generated one service document URL (using IGW in SMP 3.0).

http://localhost:8080/gateway/odata/SAP/testTable;v=1

If i had to convert this SQL statement select * from testTable where code='123'  into OData URL then it would be like:

http://localhost:8080/gateway/odata/SAP/testTable;v=1?$filter=code eq '123'

But how to pass a function in the OData URL as mentioned in the given SQL statement? s

select * from testTable where code='123' and pricedate>=getdate()-365 order by pricedate

CC:

Can you please guide?

Rgrds

JK

[fenil_doshi]

Hi All,

I think this is possible with having complex type support for Database and which is currently not available as per this documentation Enabling Database Content as OData Services - Data Integration using Integration Gateway - SAP Libra... so I think once we have the complex type support with Database then we can move ahead with this.

Any other solution are also welcomed.

Regards,

Fenil.

[fenil_doshi]

Hi Experts,

I have almost similar requirement as Jitendra has but my only concern is that I don't have Select * query but different fields in the resopnse output for different query and not all the Database fields are required, This will be done by Custom Query feature of IGW but my only concern is that every time the response output is separate for separate set of DB queries then how will that suffice with the IGW Constraint that we need to have exact same Entity (Structure wise) w.r.t Database Tables. I understand that we can run lot many different oData functions on the Entity Set but still it won't suffice all the queries. Any suggestions on this from you guys will be great.

Regards,

Fenil.

[kammaje_cis]

JK,

I was wondering if it is simpler as below.

Whichever language you code the client, there should be an easy function to calculate a date one year old.

fromDate = getDate() - 365.   //In your client language

http://localhost:8080/gateway/odata/SAP/testTable;v=1?$filter=code eq '123' and pricedate ge '<fromDate>'&$orderBy=pricedate

[Andre_Fischer]

Hi JK,

you should implement a function import.

How to do this is described in the following blog from Chandrashekhar Mahajan.

http://scn.sap.com/community/gateway/blog/2014/03/06/let-s-code-crudq-and-function-import-operations...

Best Regards,

Andre

[Jitendra_Kansal]

Thanks for replying. But i believe "Function Import" is not supported in SMP 3.0 Integration gateway. Is there any workaround for the same?

Regards

JK

[bjoernw]

Hi Jitendra,

I checked this out for you. Function imports are supported in Integration Gateway, however only for the ODC (SAP Gateway) data source. The note with the restrictions will be updated accordingly.

However, for the JDBC data source it is possible to create some custom scripting, where it is possible to manipulate your SQL query. The following methods are available for you, and I believe that you could implement the above requirement in the processRequestSQL method:

processRequestData(Message)

processRequestSQL(Message)

processResponseData(Message)

processResponseResult(Message)

Kind regards

Björn

[fenil_doshi]

Hi Bjoern,

I have even tried that but I am won't get complex type as an response output with that custom scripting with JS/Groovy, I am not sure about the requirement which Jitendra has but for that can give his inputs that was he able to make it for that.

Regards,

Fenil.