JDBC Lookup error

[Former Member]

Hi,

I am performing the JDBC mapping lookup with the help of this link. Link: [https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/2219] [original link is broken] [original link is broken] [original link is broken];

1. Construct your Query String

2. Determine the channel by specifying the Party, Service, Communication channel. Or only System, Communication channel

3. Get a SystemAccessor for the channel.

4. Execute your Query and get the resultset.

5. Loop through the resultset and get the required value. If you want to store more than 1 value, then use container and global container variables for storing them.

6. Close the System Accessor

Until step 3 is fine. For executing the Query, I get this following error.

Error while parsing or executing XML-SQL document: ERROR occured parsing request:com.sap.engine.lib.xml.parser.NestedSAXParserException: Fatal Error: com.sap.engine.lib.xml.parser.ParserException: Name expected: 0x32(:main:, row:1, col:308)(:main:, row=1, col=308) -> com.sap.engine.lib.xml.parser.ParserException: Name expected: 0x32(:main:, row:1, col:308)

My SQL query will be giving a unique value. The coding is followed as in the above link and little changes snippet of the code is as follows :

param = container.getTransformationParameters();

RECEIVER_SYSTEM =(String) param.get(StreamTransformationConstants.RECEIVER_SERVICE);

String FIELD_ID = "Empty";

DataBaseResult resultSet = null;

resultSet = accessor.execute(Query);

Iterator rows = resultSet.getRows();

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

FIELD_ID = String.valueOf(rowMap.get("XXX_ID"));

return FIELD_ID;

[JoelTrinidade · Accepted solution]

Hi Shashikaran,

There has been a mistake where the parsing of the xml document is not taking place properly. In your error log is clearly mentioned that there is a problem with udf code. The error also mentions what is expected of it.

Regards

joel

[Former Member]

Hi Praveen,

Thanks, I tried , this gives an error. it has to be rows.next()

My point is for first query it is working fine in XI. But it will give me two outputs. I need a unique value. In order to get the unique value, im adding this where rownum<2 to the query. Because of this, Im getting the following error :

com.sap.aii.mapping.lookup.LookupException: Problem when calling an adapter by using communication channel JDBC_RCV_DWN_DEV_ORACLE_ETE_001_SoldTo_001 (Party: , Service: DWN_DEV_ORACLE_ETE_001, Object ID: b788b3a17ef03314a2cd12a5a7de4940) XI AF API call failed. Module exception: (No information available). Cause Exception: 'ERROR occured parsing request:com.sap.engine.lib.xml.parser.NestedSAXParserException: Fatal Error: com.sap.engine.lib.xml.parser.ParserException: Name expected: 0x32(:main:, row:1, col:308)(:main:, row=1, col=308) -> com.sap.engine.lib.xml.parser.ParserException: Name expected: 0x32(:main:, row:1, col:308)

[former_member181985]

Hi,

I am sorry my code will give a class cast exception............

>I need a unique value.

Can you explain more.............

[Former Member]

Hi ,

I have a problem here. I am able to get the output for one sql statement which is as follows :

SELECT ABC FROM (select ABC FROM Table1.Table2 WHERE Field1='29509' order by timestamp desc);

This should return 2 values. In XI im getting the last value. However, when I append the SQL statement to get a unique value. It is throwing the above mentioned exception.

Desired SQL statement :

SELECT ABC FROM (select ABC FROM Table1.Table2 WHERE Field1='29509' order by timestamp desc) where rownum<2

Does XI has a limited length for SQL query or is there something else. Please help.

[former_member181985]

>In XI im getting the last value.

resultSet = accessor.execute(Query);

Iterator rows = resultSet.getRows();

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

FIELD_ID = String.valueOf(rowMap.get("XXX_ID"));

The above coding gives you the second value in the resultSet.

So, try with following code.

resultSet = accessor.execute(Query);
Iterator rows = resultSet.getRows();
Map rowMap = (Map)rows;
return( String.valueOf(rowMap.get("XXX_ID")) );

Thanks,

- Gujjeti.