CODEWITHSUNDEEP
mule
AKB
AKB

Reputation: 5938

mule database iterating data and mapping

I am trying to fetch data from db, do mapping and upload to cloud for each record from db one by one. I have done below approach:

select data from db, map each record from the db, then upload.

Mule flow:

<flow name="psi2sfdcFlow2" doc:name="psi2sfdcFlow2">
    <http:inbound-endpoint exchange-pattern="request-response" host="${hostname}" port="${port}" path="psi2sfdc" doc:name="HTTP"/>
    <flow-ref name="psi-update-Reading" doc:name="Flow Reference"/>
    <db:select config-ref="PostgreSQL" doc:name="PSI-Select">
        <db:parameterized-query><![CDATA[SELECT * FROM clients_int WHERE int_status = 'Reading']]></db:parameterized-query>
    </db:select>
    <object-to-string-transformer returnClass="java.lang.Object" doc:name="Object to String"/>
    <foreach doc:name="For Each">
        <invoke name="db2sfdcmapping" object-ref="createAccount" method="getPayloadData" methodArguments="#[message.payload]" doc:name="Invoke"/>
        <logger message="===Uplaod each row from db to cloud==== " level="INFO" doc:name="Logger"/>
    </foreach>
</flow>

Error:

ERROR 2015-06-17 14:24:56,735 [[psi2sfdc].connector.http.mule.default.receiver.02] org.mule.exception.DefaultMessagingExceptionStrategy: 
********************************************************************************
Message               : Object "java.lang.String" not of correct type. It must be of type "{interface java.lang.Iterable,interface java.util.Iterator,interface org.mule.routing.MessageSequence,interface java.util.Collection}" (java.lang.IllegalArgumentException). Message payload is of type: String
Type                  : org.mule.api.MessagingException
Code                  : MULE_ERROR--2
Payload               : [{phone=772-567-7461, pr_specialist_email=null, sic_code=5211, status=ACTIVE, address1=722 THIRD PLACE, fax=772-567-5054, naics_code=null, client_id=1630, bus_name=HBS INC, address2=null, contact=JANIE LUE, bus_name2=null, country=USA, termination_date=null, state=FL, zip=32962, client_fed_ids={59-1457513}, last_pay_date=2009-11-06, city=VERO BEACH, int_status=Reading}, {phone=713-668-9484, pr_specialist_email=null, sic_code=3714, status=DNE, address1=1140 AVE S, fax=null, naics_code=null, client_id=172, bus_name=FRANK'S SUPPLY OF DALLAS, address2=null, contact=PAM DEL BELLO, bus_name2=null, country=USA, termination_date=null, state=TX, zip=75050, client_fed_ids=null, last_pay_date=null, city=GRAND PRARIE, int_status=Reading}, {phone=(910)854-5000, pr_specialist_email=null, sic_code=null, status=DNE, address1=2720 IMMANUEL ROAD, fax=(910)854-0020, naics_code=null, client_id=2693, bus_name=SCIULLO INTERIOR SYSTEMS, address2=null, contact=Frank Sciullo, bus_name2=null, country=USA, te...
JavaDoc               : http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/MessagingException.html
********************************************************************************
Exception stack is:
1. Object "java.lang.String" not of correct type. It must be of type "{interface java.lang.Iterable,interface java.util.Iterator,interface org.mule.routing.MessageSequence,interface java.util.Collection}" (java.lang.IllegalArgumentException)
  org.mule.util.collection.EventToMessageSequenceSplittingStrategy:57 (null)
2. Object "java.lang.String" not of correct type. It must be of type "{interface java.lang.Iterable,interface java.util.Iterator,interface org.mule.routing.MessageSequence,interface java.util.Collection}" (java.lang.IllegalArgumentException). Message payload is of type: String (org.mule.api.MessagingException)
  org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor:32 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/MessagingException.html)
********************************************************************************
Root Exception stack trace:
java.lang.IllegalArgumentException: Object "java.lang.String" not of correct type. It must be of type "{interface java.lang.Iterable,interface java.util.Iterator,interface org.mule.routing.MessageSequence,interface java.util.Collection}"
    at org.mule.util.collection.EventToMessageSequenceSplittingStrategy.split(EventToMessageSequenceSplittingStrategy.java:57)
    at org.mule.util.collection.EventToMessageSequenceSplittingStrategy.split(EventToMessageSequenceSplittingStrategy.java:22)
    at org.mule.routing.CollectionSplitter.splitMessageIntoSequence(CollectionSplitter.java:29)
    at org.mule.routing.Foreach$CollectionMapSplitter.splitMessageIntoSequence(Foreach.java:245)
    at org.mule.routing.outbound.AbstractMessageSequenceSplitter.process(AbstractMessageSequenceSplitter.java:56)

Java file:

public class Db2sfdc {

    public List<Map<String, Object>> getPayloadData(@Payload String src){
            System.out.println("Src-->"+src);
            Map<String, Object> sfdcFields = new HashMap<String, Object>();
            List<Map<String, Object>> accountList = new ArrayList<Map<String,Object>>();
            sfdcFields.put("ID", "001m000000In0p5AAB");
            sfdcFields.put("Current_WSE_Count__c", "20");
            accountList.add(sfdcFields);
            return accountList ;
    }
}

Question:

1)Do I need to use object-to-string-transformer to get the data from db and map in java component?

2) How do I upload/send the data each row from db. When i use for each i am getting above error since I have converted db data to string transformer. I am using java component to transform instead of Data Mapper.

EDIT:

After removing object to string transformer, getting following error at the end, i.e after java mapping:

ERROR 2015-06-17 15:02:09,954 [[psi2sfdc].connector.http.mule.default.receiver.02] org.mule.exception.DefaultMessagingExceptionStrategy: 
********************************************************************************
Message               : java.io.NotSerializableException: org.postgresql.jdbc4.Jdbc4Array (org.apache.commons.lang.SerializationException). Message payload is of type: HttpResponse
Type                  : org.mule.execution.ResponseDispatchException
Code                  : MULE_ERROR--2
Payload               : org.mule.transport.http.HttpResponse@65b9ccef
JavaDoc               : http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/execution/ResponseDispatchException.html
********************************************************************************
Exception stack is:
1. org.postgresql.jdbc4.Jdbc4Array (java.io.NotSerializableException)
  java.io.ObjectOutputStream:1183 (null)
2. java.io.NotSerializableException: org.postgresql.jdbc4.Jdbc4Array (org.apache.commons.lang.SerializationException)
  org.apache.commons.lang.SerializationUtils:111 (null)
3. java.io.NotSerializableException: org.postgresql.jdbc4.Jdbc4Array (org.apache.commons.lang.SerializationException). Message payload is of type: HttpResponse (org.mule.execution.ResponseDispatchException)
  org.mule.transport.http.HttpMessageProcessTemplate:139 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/execution/ResponseDispatchException.html)
********************************************************************************
Root Exception stack trace:
java.io.NotSerializableException: org.postgresql.jdbc4.Jdbc4Array
    at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1183)
    at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:347)
    at org.apache.commons.collections.map.AbstractHashedMap.doWriteObject(AbstractHashedMap.java:1182)
    at org.mule.util.CaseInsensitiveHashMap.writeObject(CaseInsensitiveHashMap.java:142)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at java.io.ObjectStreamClass.invokeWriteObject(ObjectStreamClass.java:988)
    at java.io.ObjectOutputStream.writeSerialData(ObjectOutputStream.java:1495)
    at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1431)
    at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1177)

**

EDIT:2

Solved, after below changes:I have converted to json object after select query as:

<db:select config-ref="PostgreSQL" doc:name="PSI-Select">
   <db:parameterized-query><![CDATA[SELECT * FROM clients_int WHERE int_status = 'Reading']]></db:parameterized-query>
 </db:select>
 <foreach doc:name="For Each">
        <json:object-to-json-transformer returnClass="java.lang.Object" doc:name="Object to JSON"/>
        <logger message="===Main Flow #[payload]===" level="INFO" doc:name="Logger"/>
 </foreach>

Upvotes: 1

Views: 1849

Answers (1)

David Dossot
David Dossot

Reputation: 33413

Remove the object-to-string-transformer: it transforms the rows enumeration into a String, which prevents for-each to work.

EDIT: Moreover, because the inbound HTTP endpoint is request-response, it will need to return a response to the client. Mule will use the current payload (a Jdbc4Array instance) as the payload of the HTTP response and will fail.

You need to add a set-payload to set the body of the HTTP response to whatever you want to return to the caller. Use an http:response-builder to set response headers, cache directives... as well.

Alternatively, you can switch the HTTP inbound endpoint to one-way. This will return OK to the client immediately, and whether what happens down the flow succeeds or fails.

Upvotes: 2

Related Questions