Salesforce attachments stored as binary data needs to be rendered viewable.... help!

Question

Salesforce attachments stored as binary data needs to be rendered viewable.... help!

GaMusicMan

Say Hey Kid

Points: 674
More actions
April 15, 2011 at 8:40 am

#237845

Hello Everyone,
When we extract attachments using Salesforce's (Cloud) data loader and store the attachments in a SQL server table field, what we get is a binary file which is nothing but the base 64 encoded form of what the attachment contains. For us that data is not of much use as we can’t make any sense of it. We need to be able extract the attachments and render the data in a viewable form. Thiese attachments can contain everythinf from Images, Text, .pdfs etc.
Has anyone used SQL Server 2005 and/or any of it functions...etc to render attachements that are stored as binary data, viewable. Please let me know as this is very important to me. Thank you.
We are using SQL Server 2005 Developer Edition...

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I think you guys are expecting something from SQL that it doesn't provide. SQL just stores the data, in this case binary data. It doesn't know what that data is or what it means. And SQL doesn't render anything. Rendering (ie making it viewable) is the job of the client, SQL just provides the data.

You say that you can't make sense of the data, what do you mean? You know that it is base64 encoded so you can easily get back to the raw binary image in .net, so what is the issue?

CEWII

GaMusicMan Say Hey Kid Points: 674 More actions · Answer 2

I cvan appreciate your saying that SQL Servers function is to store, but SQL Server has a lot of other capabilities with the CLR...

I do not know much about .Net. I am an SSIS developer and looking for any advice as to whether or not SSIS has the capability to render the binary data in its originally viewable form prior to the attachment being stored in a SQL Server table - field.

I would be more than receptive to some guidance or direction as to how I might do this in .Net. I have Visual Studio 2005.

I do appreciate your time.

Anthony

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Keep in mind when you say render you are implying a display of the data. The only portion of SQL that really qualifies is SSRS, and I don't think it really helps in this regard..

I don't have the base64 to binary code available right at the moment. However you can google something like "convert base64 to byte .net" and you should be able to be get a code snippet to convert the data in .net. what are you using as a front-end to try and display this?

CEWII

GaMusicMan Say Hey Kid Points: 674 More actions · Answer 4

This is my first time having to rerender the binary data that is stored in the table field, so I am not sure what to use on the front end to render the data. I could possible use excel 2007...

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

Sounds like a Salesforce or forceAmp question. Have you contacted support?

http://www.forceamp.com/support.htm

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Lowell SSC Guru Points: 323516 More actions · Answer 6

Lowell (4/15/2011)
it depends on what you mean by viewable;
see this forum post on examples of how to
RE: How to retrieve Image file from a SQL Table
Sean Lange provided a fine example of streaming the results out to a web page, based on knowing the mime type, which is proably what you were looking for.
I provided an example of dumping the binary objects to disk.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

Excel is ok IF you are getting excel files. Your code has to handle other types as well, like pdf since you mentioned it earlier.

The simple answer is that this is neither an SSIS or a SQL question. It needs to be resolved by the front-end. The code to handle the conversion from base64 is readily available and quite frankly I would handle the conversion to binary at load time and not worry about it later at query time. No matter what though you still need to build an app to display or spawn an existing app to display the data. And this app is not SSIS or SQL..

Sorry..

CEWII

GaMusicMan Say Hey Kid Points: 674 More actions · Answer 8

I understand. It is out of the province of SQL Server SSIS. I see no transforms available in SSIS to accomplish this feat. I will turn this over to the Java crew here for front end support/programming. I certainly appreciate your assistance. I will also not post in multiple forums again. i'm new here and thought that I was supposed to post to all of the furums that might be applicable. Boy I sured learned not to do thaqt. Thanks agin 🙂

GaMusicMan Say Hey Kid Points: 674 More actions · Answer 9

I will check out the link Lowell provided annd also contact SalesForce support. Thank you all for your assistance and posting guidance...

COZYROC One Orange Chip Points: 28499 More actions · Answer 10

The code to convert base64 to binary is one line:

Dim result() As Byte = Convert.FromBase64String( base64str )

If you are indeed SSIS developer, I would recommend you get a good book for SSIS and also learn VB.NET programming. Programming skills are really important in SSIS to be productive.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/