Getting error in JDBC lookup

[Former Member]

Hi ,

I am developing a scenario file to file with the help of JDBC lookup. Message successfully process in MONI but when I check message payload in Technical Routing desired result not appears it will show some text like

"The returned object is not of type LinkedList: com.sap.aii.adapter.xi.ms.XIMessage" please suggest why this error occurred

Regards,

Amit Shivhare

[shivhare]

i am using wrong query statement

correct one is given blow .

Query = "Select UPDATE_USER, INTEGRATIONNAME from CMS_PAR_MONITOR where DUMMY_KEY = " + UName[0] + " ";

[Former Member]

Hi Amit,

Did you first test your lookup in your mapping? Test it first in message mapping and see whether you are having the right results or not.

Also if it processed successfully in moni can you see for any error in receiver communication channel monitoring?

Regards,

---Satish

[shivhare]

Hi Satish,

in JAVA UDF i have put wrong CC. now i have put rec. JDBC channel in UDF but not getting error during mapping testing

Cannot produce target element /ns0:MT_JDBCLOOKUP_REC/row/UPDATE_USER. Check xml instance is valid for source xsd and target-field mapping fulfills requirements of target xsd

my database table structure is

DUMMY_KEY NOT NULL VARCHAR2(200)

INTEGRATIONNAME NOT NULL VARCHAR2(60)

DEPLOYMENTNAME NOT NULL VARCHAR2(49)

COMPONENTINSTANCE1 NOT NULL VARCHAR2(40)

COMPONENTINSTANCE2 NOT NULL VARCHAR2(40)

MAXMEMORY NOT NULL NUMBER(4)

MAXCPU NOT NULL NUMBER(3)

FT_LB NOT NULL VARCHAR2(2)

HMA1 NOT NULL VARCHAR2(200)

HMA2 NOT NULL VARCHAR2(200)

ACTIVEFLAG NOT NULL VARCHAR2(5)

UPDATE_USER VARCHAR2(50)

UPDATE_DATE NOT NULL TIMESTAMP(6)

and UDF

//write your code here

//write your code here

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

// Build the Query String

Query = "Select UPDATE_USER from CMS_PAR_MONITOR where INTEGRATIONNAME = '" + UName[0] + " ' ";

try{

//Determine a channel, as created in the Configuration

channel = LookupService.getChannel("Ser_Test","CC_JDBC_REC_JDBCLookUP");

//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(Query);

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

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

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

}

}

catch(Exception ex){

result.addValue(ex.getMessage());

}

finally{

try{

if (accessor!=null) accessor.close();

}

catch(Exception e){

result.addValue(e.getMessage());

}

}

regards, amit

[Former Member]

Hi Amit, The column name is not matching with the table column name..

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

[ravi_raman2]

Amit,

You are getting your result as a Array, and you are reading it as a LinkedList from your error code.

Your error can be resolved if you cast your object into type LinkedList.

Please wrap this in a try catch block, when trying to read into the result list, read it directly into an Object of type LinkedList, that will throw a classCastException, That is failing so your target mapping fields are not getting created.

Catch the classCastException, cast the object into linkedList and your mapping will be fine.

Regards

Ravi Raman

[shivhare]

Hi Ravi/ rambabu,

First of all thanks to both you.

as suggest by Ram I have change the UDF code but still getting error . I have done one more change in JDBC receiver communication channel inplace of using XML SQL query I chose option native SQL and now it is working fine. But now getting exception during message mapping "XI AF API call failed. Module exception" and JDBC CC

"Error 'java.sql.SQLException: ORA-00900: invalid SQL statement ' executing service raw2sql with SQL statement '<?xml version="1.0" encoding="UTF-8" ?><ns1:lookup returnType="LinkedList" xmlns:ns1="http://sap.com/xi/lookup"><statement><table action="SQL_QUERY"><access>Select UPDATE_USER from CMS_PAR_MONITOR where DUMMY_KEY = &apos; 11 &apos; </access></table></statement></ns1:lookup>'"

Regards,

Amit

[shivhare]

Ravi,

I am not getting you can you please updated this to my given code .

Regards,

Amit

[Former Member]

In the select query your using the column "update_user" but you are ssigning with URole field its not avaialble in your table,

replace with ---

---rowMap.get("update_user");

Also if your query return multiple rows you need to declare a array list as below

ArrayList newupdate = new ArrayList();

inside for loop....

newupdate.add(rowMap.get(("update_user"));

Regards

Ramg

[shivhare]

Hi raj/and -- ,

I have done change according to you . but still not able to resolve problem . i have done some some change in JDBC receiver adpater

JDBC adapter des.

CC name CC_JDBC_REC_JDBCLookUP

Message protocal XML SQL format

Sender message Type

MT_JDBC_Sender

row

DUMMY_KEY

MT_JDBCLOOKUP_REC

row

UPDATE_USER

UDF

public void DBLookup(String[] UName,ResultList result,Container container){

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

//ArrayList newupdate = new ArrayList();

// Build the Query String

Query = "Select UPDATE_USER from CMS_PAR_MONITOR where DUMMY_KEY = '" + UName[0] + " ' ";

try{

//Determine a channel, as created in the Configuration

channel = LookupService.getChannel("Ser_Test","CC_JDBC_REC_JDBCLookUP");

//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(Query);

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

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

//newupdate.add(rowMap.get("UPDATE_USER"));

result.addValue((String)rowMap.get("UPDATE_USER"));

}

}

catch(Exception ex){

result.addValue(ex.getMessage());

}

finally{

try{

if (accessor!=null) accessor.close();

}

catch(Exception e){

result.addValue(e.getMessage());

}

WHEN I TEST MESSAGE getting error

16:39:44 Start of test

Compilation of MM_JDBCLookup successful Cannot produce target element /ns0:MT_JDBCLOOKUP_REC/row/UPDATE_USER. Check xml instance is valid for source xsd and target-field mapping fulfills requirements of target xsd Cannot produce target element /ns0:MT_JDBCLOOKUP_REC/row/UPDATE_USER. Check xml instance is valid for source xsd and target-field mapping fulfills requirements of target xsd

16:39:47 End of test

please help me

[shivhare]

Hi ,

I have change my data type structure and now it working .But still facing one issue in UDF

If I coded hard core query in UDF (Query = "Select UPDATE_USER from CMS_PAR_MONITOR where DUMMY_KEY = '11' ";) it is given successful result but

But when I try to put DUMMY_KEY in runtime then message testing successfully process but not generated any target XML instances

Query = "Select UPDATE_USER from CMS_PAR_MONITOR where DUMMY_KEY = ' " + UName[0] + ' "";

please help me to resolve this issue .

Regards,

Amit