How to consume sql blob object and save it as zip file in camel

[bharadwaj <bh...@gmail.com>]

Hi,

I have sql data base : which have zip file content in terms of BLOB , Using
camel i need to consume it and store it as zip file in file directory.

Can anybody help me out.

Thanks,
Bharadwaj nakka 



--
View this message in context: http://camel.465427.n5.nabble.com/How-to-consume-sql-blob-object-and-save-it-as-zip-file-in-camel-tp5768380.html
Sent from the Camel - Users mailing list archive at Nabble.com.

Re: How to consume sql blob object and save it as zip file in camel

[bharadwaj <bh...@gmail.com>]

The BLOB object is an xml type to do process as u described. BLOB may have
any type IMAGE , PDF, ZIP etc..

in my case i am storing zip file which contains list of multi format files
as a BLOB in data base.

camel sql component has outputType=SelectOne which give us stream object
using stream object we can unmarshal to ZipFileDataFormat which will give us 
the each file from zip file. 

next we can marshal to ZipFileDataFormat  and save entire zip file to file
system :) 






--
View this message in context: http://camel.465427.n5.nabble.com/How-to-consume-sql-blob-object-and-save-it-as-zip-file-in-camel-tp5768380p5768386.html
Sent from the Camel - Users mailing list archive at Nabble.com.

Re: How to consume sql blob object and save it as zip file in camel

[Reji Mathews <co...@gmail.com>]

Read the data from database using jdbc component.

It will return ArrayList of Hashmaps. Write a java processor to loop on
this Arraylist which is in the camel body, read this blob object from the
the MAP. read it as a binary file and write it to file endpoint after
setting it in camel exchange body.

Let me know what gets written in file location.

Check the code snippet below..

<camel:when xmlns:cam="
http://soap.crmapi.util.organization.com/campaigns/xsd/Campaigns-v1.0">
   <camel:simple>${header.operationName} ==
'getNewCampaignMessagesCount'</camel:simple>
   <camel:convertBodyTo type="java.lang.String" />
<camel:log loggingLevel="INFO" logName="organization-ib-CampaignsService"
message="ProductManagmentService_CampaignsService
getNewCampaignMessagesCount Input_Request : ${body}"/>
<camel:setProperty propertyName="CustomerID">
<camel:xpath>//cam:getNewCampaignMessagesCount/cam:accountID/text()</camel:xpath>
</camel:setProperty>
<camel:log message="Customer ID = ${property.CustomerID}"/>
<setBody>
<simple>SELECT COUNT(*) AS UNREADCNT FROM
[${properties:AnalyticsDBName}].[dbo].[${properties:MessageBoardTableName}]
WHERE CustomerID='${property.CustomerID}' AND Message_Status ='Unread' AND
ExpiryDate >= GETDATE()</simple>
</setBody>
<to uri="jdbc:dataSource"/>
<process ref="ProcessDBData"/>
<camel:log loggingLevel="INFO" logName="organization-ib-CampaignsService"
message="ProductManagmentService_CampaignsService
getNewCampaignMessagesCount Response : ${body}"/>
  </camel:when>


Checkout the java processor I have used to pick the data whatever is
returned from JDBC component.




package com.organization.jdbcrecordprocess;
import org.apache.commons.lang.StringEscapeUtils;

import java.util.List;
import java.util.Map;

import org.apache.camel.Exchange;
import org.apache.camel.Processor;

public class ProcessJDBCRecord implements Processor {

@SuppressWarnings("unchecked")
@Override
public void process(Exchange exchange) throws Exception {
// TODO Auto-generated method stub
List<Map<String, Object>> records = null;
StringBuilder returnData = null;
String operation = (String) exchange.getIn().getHeader("operationName");
if (operation.equalsIgnoreCase("getCampaignMessages")) {
records = (List<Map<String, Object>>) exchange.getIn().getBody();
returnData = new StringBuilder("");
for (Map<String, Object> record : records) {
returnData
.append("<ms:campaignMessage xmlns:ms=\"
http://soap.crmapi.util.organization.com/campaigns/xsd/2015/03\
"><ms:shortDescription>");
returnData.append(record.get("ShortDescription")==
null?"":StringEscapeUtils.escapeXml((String)record.get("ShortDescription")));
returnData.append("</ms:shortDescription><ms:longDescription>");
returnData.append(record.get("LongDescription")==
null?"":StringEscapeUtils.escapeXml((String)record.get("LongDescription")));
returnData.append("</ms:longDescription><ms:imageUrl>");
returnData.append(record.get("ImageUrl")==
null?"":StringEscapeUtils.escapeXml((String)record.get("ImageUrl")));
returnData.append("</ms:imageUrl><ms:status>");
returnData.append(record.get("Message_Status")==
null?"":record.get("Message_Status"));
returnData.append("</ms:status><ms:messageId>");
returnData.append(record.get("ID")== null?"":record.get("ID"));
returnData.append("</ms:messageId><ms:expiryDate>");
returnData.append(record.get("ExpiryDate")==
null?"":record.get("ExpiryDate"));
returnData.append("</ms:expiryDate></ms:campaignMessage>");

}
exchange.getIn()
.setBody(
"<cam:getCampaignMessagesResponse xmlns:cam=\"
http://soap.crmapi.util.organization.com/campaigns/xsd/Campaigns-v1.0\">"
+ returnData.toString()
+ "</cam:getCampaignMessagesResponse>");
} else if (operation.equalsIgnoreCase("getNewCampaignMessagesCount")) {
records = (List<Map<String, Object>>) exchange.getIn().getBody();
returnData = new StringBuilder("");
for (Map<String, Object> record : records) {
returnData.append("<cam:count>");
returnData.append((record.get("UNREADCNT")));
returnData.append("</cam:count>");
}
exchange.getIn()
.setBody(
"<cam:getNewCampaignMessagesCountResponse  xmlns:cam=\"
http://soap.crmapi.util.organization.com/campaigns/xsd/Campaigns-v1.0\">"
+ returnData.toString()
+ "</cam:getNewCampaignMessagesCountResponse>");
}

}
}


Handle your blob object accordingly in the body. Maybe you might have to do
some type casting. am not sure. Checkout usign debugging whats the class
type and do a type convertor to make it File object and write it to file:
endpoint.



Cheers
Reji

On Fri, Jun 19, 2015 at 10:03 AM, bharadwaj <bh...@gmail.com> wrote:

> Hi,
>
> I have sql data base : which have zip file content in terms of BLOB , Using
> camel i need to consume it and store it as zip file in file directory.
>
> Can anybody help me out.
>
> Thanks,
> Bharadwaj nakka
>
>
>
> --
> View this message in context:
> http://camel.465427.n5.nabble.com/How-to-consume-sql-blob-object-and-save-it-as-zip-file-in-camel-tp5768380.html
> Sent from the Camel - Users mailing list archive at Nabble.com.
>