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 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.