JDBC Look Up for muliple records( I to Unbounded) in PI 7.1

[Former Member]

Hi,

I'm doing a JDBC Look Up in PI 7.1, here My look up is working for a single record ( whenever I got a single record from the source data) , But If my sorce data as comming multiple records( Root Node as I: Unbounded) then mapping level I'm getting an JDBC Exception.

My look up will fecth the Employee name based on the emplyee number (Employee no I'm getting from the payload)

If I get the single emplyee details THne my JDBC Look up is working fine and it is fetching the Employee name ans passing it to target side. where as If I get multiple emplyee details then my look up is not working.

Is it possible handle JDBC look up for muliple rows (in Paylod ) .

Please suggest me some options to work on this.

Regards

Jain

[stefan_grube · Accepted solution]

Is this your SQL statement?

SELECT "SEQUENCE_NO" FROM "ABC" WHERE SEQUENCE_NO=( SELECT MAX(SEQUENCE_NO) FROM ABC)

It seems that the outcome is only a single value.

So you have to add a function like UseOneAsMany, or something that triggers repeating of the node.

[Former Member · Accepted solution]

Hi,

Thanks for your response, while selecting time I'm getting a single value only. But from the source side I'm getting multiple records like

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

<ns0:mt_Testtructure xmlns:ns0="http://test/mapping">

<Emp_Info>

<Emp_Id>230</Emp_Id>

<Emp_FullName>RAM</EMmp_FullName>

</Emp_Info>

<Emp_Info>

<Emp_Id>231</Emp_Id>

<Emp_FullName>JAIN</EMmp_FullName>

</Emp_Info>

<Emp_Info>

<Emp_Id>232</Emp_Id>

<Emp_FullName>SONY</EMmp_FullName>

</Emp_Info>

</ns0:mt_TestStructure>

But target side I want the data like bellow format as per the structure

Because I have used teh JDBC look Up for t

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

<ns0:mt_Testtructure xmlns:ns0="http://test/mapping">

<Emp_Info>

<Emp_Id>230</Emp_Id>

<Emp_FullName>RAM</EMmp_FullName>

<Seq_No> 10005</Seq_No>

</Emp_Info>

<Emp_Info>

<Emp_Id>231</Emp_Id>

<Emp_FullName>JAIN</EMmp_FullName>

<Seq_No> 1006</Seq_No>

</Emp_Info>

<Emp_Info>

<Emp_Id>232</Emp_Id>

<Emp_FullName>SONY</EMmp_FullName>

<Seq_No> 1009</Seq_No>

</Emp_Info>

</ns0:mt_TestStructure>

Any clues ..?

Regards

Jain

[stefan_grube · Accepted solution]

    Emp_Info - index \
                        add - splitbyvalue - seq_no
                lookup /

[Shabarish_Nair]

/people/shabarish.vijayakumar/blog/2010/01/28/graphical-rfc-lookup-and-how-you-can-design-lookups-for-multiple-values-to-be-retrieved-in-a-single-call

did you go thru this?

[Former Member]

Check if you have done the "access" node mapping correctly. It should occuer for every repeating node in the source strcuture.

Thanks

Ram

[Former Member]

Have you selected the relevant checkbox in the function properties of the standard function ? If not the mapping runtime does not insert a context change after each value in the result queue.

Ram..

[Former Member]

Dear Ram,

Yes, I had selected that Check box( Insert Conext Chnage after each result value changed ) . Still I'm getting teh same error message .

So please help me to resolve teh issue.

The following Error I'm getting at Mapping debug level.

Cannot create target element /ns0:Messages/ns0:Message1/ns1:MT_COMPLAINTCODE_IN[2]/INSERT/ABC/access/SEQUENCE_NO. 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

15:26:17 Start of test

Compilation of MM_COMPLAINTCODE successful

u2022 Document start

u2022 Start tag [ns0:Messages]

u2022 Add raw attribute [ xmlns:ns0="http://sap.com/xi/XI/SplitAndMerge"]

u2022 Start tag [ns0:Message1]

u2022 Start tag [ns1:MT_COMPLAINTCODE_IN]

u2022 Add raw attribute [ xmlns:ns1="https:.//testin dev"]

u2022 Start tag [INSERT]

u2022 Start tag [ABC]

u2022 Add attribute: [action]=[INSERT]

u2022 Start tag [access]

u2022 Start tag [COMPLAINT_CODE]

u2022 Put value [121]

u2022 Close tag [COMPLAINT_CODE]

u2022 Start tag [NAME]

u2022 Put value [ABC]

u2022 Close tag [NAME]

u2022 Start tag [DESCRIPTION]

u2022 Put value []

u2022 Close tag [DESCRIPTION]

u2022 Start tag [WAR_COMPLAINT_GROUP]

u2022 Put value [222]

u2022 Close tag [WAR_COMPLAINT_GROUP]

u2022 Start tag [ACTION_TYPE]

u2022 Put value [N]

u2022 Close tag [ACTION_TYPE]

u2022 Start tag [ACTION_DATE]

u2022 Put value []

u2022 Close tag [ACTION_DATE]

u2022 Start tag [IS_DIRTY]

u2022 Put value [N]

u2022 Close tag [IS_DIRTY]

u2022 Before executing the call.

u2022 Execute statement [SELECT "SEQUENCE_NO" FROM "ABC" WHERE SEQUENCE_NO=( SELECT MAX(SEQUENCE_NO) FROM ABC)]

u2022 After executing the call.

u2022 closing accessor.

u2022 accessor closed.

u2022 Start tag [SEQUENCE_NO]

u2022 Put value [1004]

u2022 Close tag [SEQUENCE_NO]

u2022 Start tag [UNIQUE_IDENTIFIER]

u2022 Put value []

u2022 Close tag [UNIQUE_IDENTIFIER]

u2022 Close tag [access]

u2022 Close tag [ABC]

u2022 Close tag [INSERT]

u2022 Close tag [ns1:MT_COMPLAINTCODE_IN]

u2022 Start tag [ns1:MT_COMPLAINTCODE_IN]

u2022 Add raw attribute [ xmlns:ns1="https:.//testin dev"]

u2022 Start tag [INSERT]

u2022 Start tag [ABC]

u2022 Add attribute: [action]=[INSERT]

u2022 Start tag [access]

u2022 Start tag [COMPLAINT_CODE]

u2022 Put value [123]

u2022 Close tag [COMPLAINT_CODE]

u2022 Start tag [NAME]

u2022 Put value [ABC]

u2022 Close tag [NAME]

u2022 Start tag [DESCRIPTION]

u2022 Put value []

u2022 Close tag [DESCRIPTION]

u2022 Start tag [WAR_COMPLAINT_GROUP]

u2022 Put value [222]

u2022 Close tag [WAR_COMPLAINT_GROUP]

u2022 Start tag [ACTION_TYPE]

u2022 Put value [N]

u2022 Close tag [ACTION_TYPE]

u2022 Start tag [ACTION_DATE]

u2022 Put value []

u2022 Close tag [ACTION_DATE]

u2022 Start tag [IS_DIRTY]

u2022 Put value [N]

u2022 Close tag [IS_DIRTY]

Cannot create target element /ns0:Messages/ns0:Message1/ns1:MT_COMPLAINTCODE_IN[2]/INSERT/ABC/access/SEQUENCE_NO. 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

com.sap.aii.mappingtool.tf7.IllegalInstanceException: Cannot create target element /ns0:Messages/ns0:Message1/ns1:MT_COMPLAINTCODE_IN[2]/INSERT/ABC/access/SEQUENCE_NO. 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.processNode(AMappingProgram.java:411)

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.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.ibrep.server.mapping.exec.ExecuteXiMappingCommand.transformInternal(ExecuteXiMappingCommand.java:197)

at com.sap.aii.ibrep.server.mapping.exec.ExecuteXiMappingCommand.execute(ExecuteXiMappingCommand.java:94)

at com.sap.aii.ib.server.mapping.exec.CommandManager.execute(CommandManager.java:43)

at com.sap.aii.ibrep.server.mapping.ServerMapService.execute(ServerMapService.java:40)

at com.sap.aii.ibrep.server.mapping.MapServiceBean.execute(MapServiceBean.java:40)

at sun.reflect.GeneratedMethodAccessor780.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:592)

at com.sap.engine.services.ejb3.runtime.impl.RequestInvocationContext.proceedFinal(RequestInvocationContext.java:46)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:166)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_StatesTransition.invoke(Interceptors_StatesTransition.java:19)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:177)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_Resource.invoke(Interceptors_Resource.java:71)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:177)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_Transaction.doWorkWithAttribute(Interceptors_Transaction.java:38)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_Transaction.invoke(Interceptors_Transaction.java:22)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:177)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:189)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_StatelessInstanceGetter.invoke(Interceptors_StatelessInstanceGetter.java:16)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:177)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_SecurityCheck.invoke(Interceptors_SecurityCheck.java:21)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:177)

at com.sap.engine.services.ejb3.runtime.impl.Interceptors_ExceptionTracer.invoke(Interceptors_ExceptionTracer.java:16)

at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:177)

at com.sap.engine.services.ejb3.runtime.impl.DefaultInvocationChainsManager.startChain(DefaultInvocationChainsManager.java:133)

at com.sap.engine.services.ejb3.runtime.impl.DefaultEJBProxyInvocationHandler.invoke(DefaultEJBProxyInvocationHandler.java:164)

at $Proxy1652.execute(Unknown Source)

at sun.reflect.GeneratedMethodAccessor779.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:592)

at com.sap.engine.services.rmi_p4.P4DynamicSkeleton.dispatch(P4DynamicSkeleton.java:234)

at com.sap.engine.services.rmi_p4.DispatchImpl._runInternal(DispatchImpl.java:355)

at com.sap.engine.services.rmi_p4.server.ServerDispatchImpl.run(ServerDispatchImpl.java:69)

at com.sap.engine.services.rmi_p4.P4Message.process(P4Message.java:67)

at com.sap.engine.services.rmi_p4.P4Message.execute(P4Message.java:41)

at com.sap.engine.services.cross.fca.FCAConnectorImpl.executeRequest(FCAConnectorImpl.java:977)

at com.sap.engine.services.rmi_p4.P4Message.process(P4Message.java:57)

at com.sap.engine.services.cross.fca.MessageReader.run(MessageReader.java:55)

at com.sap.engine.core.thread.execution.Executable.run(Executable.java:115)

at com.sap.engine.core.thread.execution.Executable.run(Executable.java:96)

at com.sap.engine.core.thread.execution.CentralExecutor$SingleThread.run(CentralExecutor.java:314)

Cannot create target element /ns0:Messages/ns0:Message1/ns1:MT_COMPLAINTCODE_IN[2]/INSERT/ABC/access/SEQUENCE_NO. 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

15:26:21 End of test

Regards

Jain

[former_member200962]

But If my sorce data as comming multiple records( Root Node as I: Unbounded) then mapping level I'm getting an JDBC 
Exception.
Cannot create target element /ns0:Messages/ns0:Message1/ns1:MT_COMPLAINTCODE_IN
[2]/INSERT/ABC/access/SEQUENCE_NO.

Is there any sort of multimapping that you have implemented or have you changed the occurence of Root Node in Message Mapping? Any particular reason for doing this?

When you say Multiple Records, how does your source message look like?