Well, this time I would like to share this tip to create a service interface to retrieve data from Database with SAP PO/PI.
When you develop the message types to get SQL data you must to check the names and the structure of the Data Types, for example, this is a simple structure to do this query:
SELECT VC_PEDIMENTO, INVNUM, VC_PRC_FILE_FLG FROM MYTABLE WHERE (INVNUM='4900005421')
For the request sender message type you can write your own structure with any label names:
For the request receiver message type, the name of the structure is very important because when we retrieve the data is necessary to write in the receiver structure the same label names and add «_ response» (internally SAP PO create the XML with this convention)
note: the attribute hasQuot it’s used to add single quotes in the field on the WHERE clause.
Then in receiver message type your structure It must be like this, notice the «_response» on the two first elements, the are the same of the previous request structure:
The label statement_response it’s not mapped because in the runtime execution will be added. And that’s it, now the mappings look like this, the request:
The important fields are mapped with:
- action: has the SELECT operation
- table: the name of the table on DB
- access: has the all fields to retrieve, they are mapped with a constant with an empty blank space character.
- key1: it’s the fields on WHERE clause, it has the attribute to add single quotes
The response:
The fields are mapped one to one except statement_response.
If you want to know more about the document formats and attributes on JDBC adapter I recommend check this document.
Cheers,
Hi Jaehoo, thanks for the blog. I have problem getting this to work.
I have a Proxy to Stored Procedure (SQL) scenario where i cannot seem to make the fields to receive the information, as if i have something wrong on my souce DT.
If i remove my «Response» from my Operation Mapping and run my test from SAP, i (obviously) get an error and then it shows me the response from the SP on a very strange structure.
Something like this:
FT18
100373 ALLEN BROS
I18002
X
3367 WHITE CORN
264-ST18-
FN18
2019-06-18 11:30:00.0
4500045287
EV19169001
010000032400
86230.000
As you can see the «_response» was added. and that is the way i have my DTs.
But i don’t know why it added a TAG «response_1».
My question is, should i follow this exact structure on my DT and MT?.
hope you can help.
should i follow this exact structure on my DT and MT?.
Hi, the structure is defined by DT, but the names only matter on the receiver side, check this:
# Proxy side (names don’t matter)
**request**
MT_whatever_req
DT_whatever_req
**response**
MT_whatever_res
DT_whatever_res
# JDBC side (names matter):
**request**
MT_sender_something
DT_sender_something
**response**
MT_sender_something_response
DT_sender_something_response
Saludos