cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC Lookup error

Former Member
0 Kudos

I am doing a JDBC lookup for my file to Idoc interface and have the below error,please advice

Moni error

<Trace level="1" type="T">RuntimeException during appliction Java mapping com/sap/xi/tf/_MM_File_In_To_File_Out_ Thrown: com.sap.aii.mappingtool.tf7.MessageMappingException: Runtime exception when processing target-field mapping /ns0:MT_File_Out/root/HAE_ACTN_TYPE; root message: Exception:[com.sap.aii.mapping.api.StreamTransformationException: Error when calling an adapter by using the communication channel CC_JDBC_Lookup_Receiver (Party: , Service: BC_JDBC_Lookup, Object ID: 838a43df1e913876a9b76a1e604a4f27) The channel with object ID 838a43df1e913876a9b76a1e604a4f27 could not be found in the Integration Server Java Cache. Check if the channel exists in the Integration Builder Directory and execute a refresh of the Java Cache.] in class com.sap.xi.tf._MM_File_In_To_File_Out_ method lookupHcmValues[CP, 991, com.sap.aii.mappingtool.tf7.rt.Context@270a1d63] at com.sap.aii.mappingtool.tf7.AMappingProgram.processNode(AMappingProgram.java:463) at com.sap.aii.mappingtool.tf7.AMappingProgram.processNode(AMappingProgram.java:411) at com.sap.aii.mappingtool.tf7.AMappingProgram.processNode(AMappingProgram.java:411) at com.sap.aii.mappingtool.tf7.AMappingProgram.start(AMappingProgram.java:503) at com.sap.aii.mappingtool.tf7.Transformer.start(Transformer.java:134) at com.sap.aii.mappingtool.tf7.AMappingProgram.transform(AMappingProgram.java:635) at com.sap.aii.ib.server.mapping.execution.JavaMapping.executeStep(JavaMapping.java:92) at com.sap.aii.ib.server.mapping.execution.Mapping.execute(Mapping.java:60) at com.sap.aii.ib.server.mapping.execution.MappingHandler.map(MappingHandler.java:87) at com.sap.aii.ib.server.mapping.execution.MappingHandler.map(MappingHandler.java:71) at com.sap.aii.ibrun.sbeans.mapping.MappingRequestHandler.handleMappingRequest(MappingRequestHandler.java:119) at com.sap.aii.ibrun.sbeans.mapping.MappingRequestHandler.handleRequest

UDF Code

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

String retVal = "";

// Build the Query String

StringBuilder sb = new StringBuilder();

sb.append(" SELECT");

sb.append(" HAE_CNTRL12_TYP");

sb.append(" ,HAE_ACTVTY_CODE");

sb.append(" ,HAE_ACTN_TYPE");

sb.append(" ,HAE_ACTN_RSN_CODE");

sb.append(" ,HAE_EMP_GRP_CODE");

sb.append(" ,HAE_EMP_SBGRP_CODE");

sb.append(" FROM");

sb.append(" PISTAGE.HCM_ACTION_EE");

sb.append(" WHERE");

sb.append(" HAE_CNTRL12_TYP = '" + control12Type + "'" );

sb.append(" AND HAE_ACTVTY_CODE = '" + activityCode + "'" );

try{

//Determine a channel, as created in the Configuration

channel = LookupService.getChannel("BC_JDBC_Lookup","CC_JDBC_Lookup_Receiver");

//Get a system accessor for the channel. As the call is being made to an DB, an DatabaseAccessor is obtained.

accessor = LookupService.getDataBaseAccessor(channel);

//Execute Query and get the values in resultset

resultSet = accessor.execute(sb.toString());

Iterator rows = resultSet.getRows();

Map rowMap = (Map)rows.next();

retVal = "";

retVal += (String)rowMap.get("HAE_ACTN_TYPE") + "\t";

retVal += (String)rowMap.get("HAE_ACTN_RSN_CODE") + "\t";

retVal += (String)rowMap.get("HAE_EMP_GRP_CODE") + "\t";

retVal += (String)rowMap.get("HAE_EMP_SBGRP_CODE");

return(retVal);

//for(Iterator rows = resultSet.getRows();rows.hasNext();){

// Map rowMap = (Map)rows.next();

// result.addValue((String)rowMap.get("URole"));

//}

}catch(Exception ex){

throw new StreamTransformationException(ex.getMessage());

//result.addValue(ex.getMessage());

}finally{

try{ if (accessor != null) { accessor.close(); } }catch(Exception e){ /* Ignore */ }

}

Accepted Solutions (0)

Answers (1)

Answers (1)

baskar_gopalakrishnan2
Active Contributor
0 Kudos

Suggestion: If your PI server is 7.1, why dont you use graphical jdbc lookup. So that you dont need the UDF.

follow this link /people/jin.shin/blog/2008/02/15/sap-pi-71-mapping-enhancements-series-graphical-support-for-jdbc-and-rfc-lookups

If you need to use only UDF then see below

Looks like you are trying to retrieve only the first record of the table. why?

If the values does not exist for even one field you might get null pointer exception .

Question

Why do you select 6 fields in the query and retrieve resultset data only for the last 4 fields?

Suggestion:

Remove first two fields from the query and maintain order both in select query and retrieve part of the resultset.

Important thing: You have to specify the column name exactly as appear in the database.

Also, try to check not null for each field in the resultset and see you get the desired result

Example:

if(rowMap.get("HAE_ACTN_TYPE") != null){

retVal += (String)rowMap.get("HAE_ACTN_TYPE") + "\t";

} else{

retVal = "";

}

Assign the values to target fields after checking whether the value comes from the table or not.

Hope the above information might be helpful.

Former Member
0 Kudos

I am passing " HAE_CNTRL12_TYP"," ,HAE_ACTVTY_CODE" in the input and get the 6 fields as output, the udf output is split into 6 fileds using another UDF(split).The DB guy gave me the Select code

when I see my CC in the adapter engine CC monitoring there is no message flow and the channel is green,which means the channel is not called at all,I am getting a mapping error since there is no value passed to the output fields, any suggestions?

now I get this error below

<Trace level="1" type="T">RuntimeException during appliction Java mapping com/sap/xi/tf/_MM_Lookup_ Thrown: com.sap.aii.mappingtool.tf7.IllegalInstanceException: Cannot create target element /ns0:MT_Lookup_Recv/root. Values missing in queue context. Target XSD requires a value for this element, but the target-field mapping does not create one. Check whether the XML instance is valid for the source XSD, and whether the target-field mapping fulfils the requirement of the target XSD at com.sap.aii.mappingtool.tf7.AMappingProgram.processNode(AMappingProgram.java:369) at com.sap.aii.mappingtool.tf7.AMappingProgram.processNode(AMappingProgram.java:411) at com.sap.aii.mappingtool.tf7.AMappingProgram.start(AMappingProgram.java:503) at com.sap.aii.mappingtool.tf7.Transformer.start(Transformer.java:134) at com.sap.aii.mappingtool.tf7.AMappingProgram.transform(AMappingProgram.java:635) at

Former Member
0 Kudos

I made changes to my lookup code as below by passing 2 inputs and get single output field

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

String result = new String();

Query = "Select HAE_CNTRL12_TYP,HAE_ACTVTY_CODE from PISTAGE.HCM_ACTION_EE where HAE_CNTRL12_TYP ='" + control12Type + " AND HAE_ACTVTY_CODE =" + activityCode + "'" ;

try {

channel =

LookupService.getChannel( "BC_GeoRestrictions_SAP_To_Legacy","CC_GeoRestrictions_JDBC_Receiverr");

accessor = LookupService.getDataBaseAccessor(channel);

resultSet = accessor.execute(Query);

//Iterator rows = resultSet.getRows();

for(Iterator rows = resultSet.getRows();rows.hasNext();){

Map rowMap = (Map)rows.next();

result = (String) rowMap.get("HAE_ACTN_TYPE");

}

}

catch(Exception ex) {

result = ex.getMessage();

}

return result;

I got this output with a successful flag in moni

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:MT_Lookup_Recv xmlns:ns0="http://www.**************LOOKUP">

- <root>

<HAE_CNTRL12_TYP>CP</HAE_CNTRL12_TYP>

<HAE_ACTVTY_CODE>991</HAE_ACTVTY_CODE>

<HAE_ACTN_TYPE>Error when calling an adapter by using the communication channel CC_GeoRestrictions_JDBC_Receiverr (Party: , Service: BC_GeoRestrictions_SAP_To_Legacy, Object ID: 46d3720f87313f6eb741b6659afde508) The channel with object ID 46d3720f87313f6eb741b6659afde508 could not be found in the Integration Server Java Cache. Check if the channel exists in the Integration Builder Directory and execute a refresh of the Java Cache.</HAE_ACTN_TYPE>

</root>

</ns0:MT_Lookup_Recv>

Former Member
0 Kudos

Hi,

Does your channel CC_GeoRestrictions_JDBC_Receiverr is under Business System BC_GeoRestrictions_SAP_To_Legacy

and if so, can you please check the channel is fine and is started already.

Also monitor the JDBC Communication channel you will get the hint.

Babu

rajasekhar_reddy14
Active Contributor
0 Kudos

Check your communication channel is having * in party , if yes then remove it...it will work.