UDF for stored procedure doesn't work with data from parameters

[former_member451120]

Hey gurus,

I am facing something weird with an UDF. I have a requeriment where I need to call 3 stored procedures where they may have a dependence between each other. For example, I need to execute a first SP that provides me a value to use in a second stored procedure, and its result need to be used as parameter in a third SP and the data that came from this last one is going to be sent via mail.

So for this purpose I am using one sender JDBC channel for calling first SP, one receiver MAIL channel for sending the mail (both in the same integration flow) and one JDBC receiver channel for calling the rest of the SPs via UDFs.

Everythings seems to look good, but I found an issue with the UDF.

The code is next one:

Channel channel = null;
Map rowMap = null;
DataBaseAccessor accessor = null;
DataBaseResult resultSet = null;

String Query = "CALL SP1(55)";

try{

channel = LookupService.getChannel("BS_XXX","CC_JDBC_LOOKUP_Receiver");

accessor = LookupService.getDataBaseAccessor(channel);

resultSet = accessor.execute(Query);
for(Iterator rows = resultSet.getRows();rows.hasNext();){
rowMap = (Map)rows.next();
result.addValue((String)rowMap.get("field_name"));
}
}
catch(Exception ex){
result.addValue(ex.getMessage());
}

Query is the call to the stored procedure that receives a value (55 as example) that I got from the first call done once interface starts.

Using this UDF I am able to obtain the values I need. But this "55" is hardcoded in the UDF, so I added a parameter to the UDF so I can receive the value from a mapping field.

When I test this UDF via this way, I am getting next error:

Exception during processing the payload. Error when calling an adapter by using the communication channel CC_JDBC_LOOKUP_Receiver (Party: , Service: BS_XXX, Object ID: d00c03ada97a3758oa9395ec0b41a297) XI AF API call failed. Module exception: (No information available). Cause Exception: 'Error processing request in sax parser: Error when executing statement for table/stored proc. 'table' (structure 'statement'): java.sql.SQLException: A syntax error has occurred.'. com.sap.aii.mapping.lookup.LookupException: Error when calling an adapter by using the communication channel CC_JDBC_LOOKUP_Receiver (Party: , Service: BS_XXX, Object ID: d00c03ada97a3758ba9395ec0b41a297) XI AF API call failed. Module exception: (No information available). Cause Exception: 'Error processing request in sax parser: Error when executing statement for table/stored proc. 'table' (structure 'statement'): java.sql.SQLException: A syntax error has occurred.'.

This is really weird for me, as the string for calling the UDF is exactly equal as when I hardcoded (I checked this and it's the same).

So hardcoding the SP parameter in the UDF works, but passing the value (as string) is giving me that exception.

Any ideas? May this be some kind of bug?

Thanks in advanced!

edit: the UDF code when I am receiving the value as a parameter is next one:

String Query = "CALL SP1(";
Query = Query.concat(mapping_field_value + ")");

[former_member451120 · Accepted solution]

I generated a new UDF and seems to be working. Issue was not solved from that UDF but I am able to do what I need now.

I am leaving the code just in case is useful for anyone:

GlobalContainer globalContainer;
MappingTrace trace;
java.util.Map map;
trace = container.getTrace();
globalContainer = container.getGlobalContainer();
//map = globalContainer.getTransformationParameters();
  
//Variables Used
String Query = " ";
Channel channel = null;
DataBaseAccessor accessor = null;
DataBaseResult resultSet = null;
Query = "CALL SP1(" + input_parameter + ")";
String returnValue = "";
try{
  trace.addInfo("Query: " + Query);
channel = LookupService.getChannel( "BS_XXX","CC_JDBC_LOOKUP_Receiver");
accessor = LookupService.getDataBaseAccessor(channel);
resultSet = accessor.execute(Query);
for(Iterator rows = resultSet.getRows();rows.hasNext();)
{
	java.util.Map rowMap = (java.util.Map) rows.next();
	returnValue =  returnValue + "FIELD1: " + (String) rowMap.get("field1name") + ", ";
//	returnValue =  returnValue + "FIELD2: " + (String) rowMap.get("FIELD2") + "\r\n";
}    
}catch(Exception ee){
  returnValue = "";
}
return returnValue;