How to check JDBC SQL Query Syntax and verify the query results inside a User Defined Function of the Lookup API.
As of SAPNW04 Exchange Infrastructure (PI) SPS13, Mapping Runtime in Integration Server provides an API for JDBC, SOAP and RFC lookups. The Lookup API is executed on the SAP J2ee Engine in the message mapping program via the Adapter Engine. Apart from lookup API, the mapping runtime also provides the Mapping Trace Object which enables to transfer messages to the mapping trace where it can be viewed in the monitoring Transaction: SXMB_MONI. All the methods of the trace output are encapsulated in MappingTrace class. The AbstractClass inherits all the methods from MappingTrace class.
Sometimes when using Mapping lookups for JDBC complex queries like a Table Join statement, checking the JDBC syntax is tedious. During runtime it is very hard to troubleshoot the query syntax, like the query statement passing to the corresponding Database and whether if it is executed successfully or not. Using the methods available from the Abstarct Trace class, we can pass the JDBC Query statement and the query results as parameters inside the User Defined Function code and check the results immediately.
Below is a Mapping program example where i am using the Mapping API runtime to query a table in a MSSQL database using the JDBC Adapter. The SQL Query Statement is a Table Join. The source field is a Unit of Measure (IDoc Segment) field: GMEIN and the traget feild is: _name. My mapping requirement is to get the full name of the Unit of Measure from a SQL Table Join. The Userdefined Function name is: 'GetCommonUOM'. Figure 1. below shows the Message Mapping Program.
The code required to add the Trace objects inside the User defined fucntion is shown below in the Figuure 2. All the remaing code must be included in the function for the JDBC query using the Mapping API. The initial JDBC SQL Statement is as follows. JDBC Synatax is very case sensitive and considers all empty spaces to be part of the statement.
String query = " Select DataEnrichmentDetails.OutputValue " + "from DataEnrichmentDetails, DataEnrichmentHeader " + "where DataEnrichmentDetails.ParentId = DataEnrichmentHeader.DataEnrichmentFieldId " + " and DataEnrichmentDetails.InputValue = '"+uom[0]+"' " + "and DataEnrichmentHeader.EnrichmentFieldName LIKE 'UOM' " + "and DataEnrichmentHeader.TransactionType LIKE 'ALL' " ;
Once the mapping is completed between the Source and the Target fields, execute the mapping program in the Test tab folder as shown in the below Figure 3. Make sure the Communication channels for the JDBC Receiver is configured already in Integration Directory. You can check the test results at the bottom area for the table join query statement beign passed to the SQL database and the results of the query statement using the Mapping Trace Objects. In this example, my source input value in the query statement is 'PCE' and the result i am expecting is 'Piece'.
The final query Statement passed to the SQL Server is as follows. If the SQL query fails then the problem lies in the JDBC syntax of the query. You can always take the below SQL query and test this query against the MSSQL Query Analyzer. Troubleshoot the problem and adjust the JDBC query in the user defined function accordingly.
Select DataEnrichmentDetails.OutputValue from DataEnrichmentDetails, DataEnrichmentHeader where DataEnrichmentDetails.ParentId = DataEnrichmentHeader.DataEnrichmentFieldId and DataEnrichmentDetails.InputValue = 'PCE' and DataEnrichmentHeader.EnrichmentFieldName LIKE 'UOM' and DataEnrichmentHeader.TransactionType LIKE 'ALL'
For any Complex SQL query statements using the Mapping Trace objects, you can troubleshoot the JDBC syntax of the query statements and check the results of the query for JDBC Mapping Lookups. You can comment this code in your production landscape as you will be troublehsooting this porblem in the Developemnt landscape.
4 Comments
