Email message bodies - Compression?

Post questions on how to interpret your Bullhorn SQL Server backup of your Database.

Moderators: StaffingSupport, s.emmons

rkinney
User
Posts: 11
Joined: Wed Dec 31, 1969 8:00 pm

Email message bodies - Compression?

Post by rkinney » Tue Sep 08, 2009 1:36 am

Hi,

Can you explain the email message bodies? In our backup it appears that older message bodies are in BH_UserMessage.comments. This field is null for newer messages, but there is data in BH_UserMessage.commentsCompressed. Is this the message body, and if so how can it be un-compressed? Please advise.

apsmith
Site Admin
Posts: 2051
Joined: Wed Dec 31, 1969 8:00 pm

Re: Email message bodies - Compression?

Post by apsmith » Wed Sep 09, 2009 1:19 pm

rkinney,

The message body is contained in the database. After January of 2008, the message body is contained in the database in compressed (non-plain text) format.
The compression used is standard zip compression. If you can select out the text, any unzip program should be able to uncompress the text, allowing you to view the message body in plain text.

One program we have found which can uncompress the data programatically is Chilkat - http://www.chilkatsoft.com/
However it is still just standard zip compression which is used on the text.

Here is a snipped of code which can be used by your developer to systematically convert the message body.

public byte[] compressStringToImage(string charset, string inputString) {
Chilkat.Zip zip = new Chilkat.Zip();
zip.UnlockComponent(GetConfigString("chilkatSerial"));
zip.NewZip("test.zip");
Encoding encoding = Encoding.GetEncoding("iso-8859-1");
try {
encoding = Encoding.GetEncoding(charset.ToLower() == "utf-8" || charset.Trim() == "" ? "iso-8859-1" : charset);
} catch {}
byte[] byteData = encoding.GetBytes(inputString);
zip.AppendData("test.txt", byteData);
Chilkat.ZipEntry entry;
entry = zip.FirstEntry();
byte[] compressedData = entry.Copy();
return compressedData;
//retu
Andrew Smith
Director, Technical Operations
Developer Forum Admin

Gibbonas
User
Posts: 10
Joined: Thu Nov 19, 2009 5:48 pm

Re: Email message bodies - Compression?

Post by Gibbonas » Thu Nov 19, 2009 6:01 pm

I've tried reading the image data (compressedcomments) into a byte[] then writing it to a file but I cannot unzip the file with ChilKat or any other zip program. I just get errors or it says its a corrupt zip file. Is there something else that I need to do to read the email bodies from the compressedcomments field? I'm using c# code.
Thanks,
Aaron

apsmith
Site Admin
Posts: 2051
Joined: Wed Dec 31, 1969 8:00 pm

Re: Email message bodies - Compression?

Post by apsmith » Fri Nov 20, 2009 12:03 pm

Gibbonas,

Are you getting the same error for every message? Can you post the exact text of the error message?
Andrew Smith
Director, Technical Operations
Developer Forum Admin

Gibbonas
User
Posts: 10
Joined: Thu Nov 19, 2009 5:48 pm

Re: Email message bodies - Compression?

Post by Gibbonas » Wed Nov 25, 2009 11:44 am

I'm getting the same message pretty much every time.
Not enough bytes remaining to fulfill memory-data request.
Failed to read Zip (11)
I'm kinda thinking is has something to do with the way I'm reading the information from compressedcomments? Below is the code I'm using. To unzip it I'm just using the exact code from ChilKat... Thried both file and bytedata
//bool success = zip.OpenZip("C:\\temp\\temp.zip");
bool success = zip.OpenFromMemory(byteData);

cmd = new SqlCommand("Select commentscompressed as commentscompressed from bullhorn1.BH_UserMessage where usermessageid =" + usermessageid + "", conn);
conn.Open();

SQLDR = cmd.ExecuteReader();
SQLDR.Read();
byteData = null;

Encoding encoding = Encoding.GetEncoding("iso-8859-1");
//encoding = Encoding.GetEncoding("utf-8");

byteData = encoding.GetBytes(SQLDR["commentscompressed"].ToString());// (byte[])SQLDR["commentscompressed"];
SQLDR.Close();

fs = new FileStream("C:\\temp\\temp.zip", FileMode.Create, FileAccess.ReadWrite);
BinaryWriter bw = new BinaryWriter(fs);
for (int ii = 0; ii < byteData.Length; ii++)
{
bw.Write(byteData[ii]);
}
bw.Close();
conn.Close();

apsmith
Site Admin
Posts: 2051
Joined: Wed Dec 31, 1969 8:00 pm

Re: Email message bodies - Compression?

Post by apsmith » Tue Dec 01, 2009 12:02 pm

Gibbonas,

What does the binary data look like as you pull it from the database? I would start there, and try to work slowly up.

If this helps, this is how we are compressing the data on the insert:

Code: Select all

myCommand.Parameters["@commentsCompressed"].Value = Common.compressStringToImage(Msg.GetBodyCharset(), body);
Andrew Smith
Director, Technical Operations
Developer Forum Admin

Gibbonas
User
Posts: 10
Joined: Thu Nov 19, 2009 5:48 pm

Re: Email message bodies - Compression?

Post by Gibbonas » Tue Dec 22, 2009 11:35 am

The byte array looks good. For example I get something like:
{byte[364]}
[0]: 149
[1]: 146
[2]: 95
[3]: 79
[4]: 194
[5]: 48
[6]: 20
[7]: 197
[8]: 223
[9]: 77
[10]: 252...
I write this to a tempfile.zip and try to unzip it and nothing will unzip the file (cannot open as archive or Error in Zip structure...).
Thanks,
Aaron

Gibbonas
User
Posts: 10
Joined: Thu Nov 19, 2009 5:48 pm

Re: Email message bodies - Compression?

Post by Gibbonas » Mon Feb 08, 2010 9:57 pm

Has anyone else had trouble with this? I've been able to read resumes of varioius different formats storred in similar fashion but can't seem to read anything that would just be a zipped file that I could uncompress from this commentscompressed field.
Thanks for any help.

apsmith
Site Admin
Posts: 2051
Joined: Wed Dec 31, 1969 8:00 pm

Re: Email message bodies - Compression?

Post by apsmith » Tue Feb 09, 2010 10:26 am

Gibbonas,

Sorry for the long delay in responding, there seems to have been a glitch in the notification.

I spoke with the development staff and they seem to think that you may be only pulling a portion of the data out of the commentscompressed field. Can you confirm that your code is pulling the entitre contents of the field? Some clients will truncate at a certain threshhold.
Andrew Smith
Director, Technical Operations
Developer Forum Admin

Gibbonas
User
Posts: 10
Joined: Thu Nov 19, 2009 5:48 pm

Re: Email message bodies - Compression?

Post by Gibbonas » Mon Feb 15, 2010 1:26 pm

I've not had a problem with this code in the past reading resumes from SQL databases so I don't think it is a problem with the array. Essentially I could read the contents into a .zip file and then just uncompress it correct? I've tried several variations of this code and the previous code that I have posted.

cmd = new SqlCommand("Select commentscompressed as commentscompressed from bullhorn1.BH_UserMessage where usermessageid =" + conv.cr("usermessageid") + "", conn);
conn.Open();

rsAtt = cmd.ExecuteReader();

rsAtt.Read();
blob = null;
blob = new Byte[(rsAtt.GetBytes(0, 0, null, 0, int.MaxValue))];
rsAtt.GetBytes(0, 0, blob, 0, blob.Length);//.GetSqlXml(0);//
rsAtt.Close();

fs = new FileStream("C:\\temp\\temp.zip", FileMode.Create, FileAccess.Write);

fs.Write(blob, 0, blob.Length);
fs.Close();
conn.Close();

apsmith
Site Admin
Posts: 2051
Joined: Wed Dec 31, 1969 8:00 pm

Re: Email message bodies - Compression?

Post by apsmith » Mon Feb 15, 2010 1:40 pm

Gibbonas,

Have you tried getting a new copy of the backup? Redownloading it? If you already have, we could perhaps generate a new backup to see if there is anything corrupt about the file itself.
Andrew Smith
Director, Technical Operations
Developer Forum Admin

Gibbonas
User
Posts: 10
Joined: Thu Nov 19, 2009 5:48 pm

Re: Email message bodies - Compression?

Post by Gibbonas » Tue Feb 16, 2010 10:23 am

Yes I have 2 different backups that I have looked at.

Gibbonas
User
Posts: 10
Joined: Thu Nov 19, 2009 5:48 pm

Re: Email message bodies - Compression?

Post by Gibbonas » Thu Feb 18, 2010 10:45 am

In an effort to help a current customer, I’d like to know how to extract the compressed email bodies from their Bullhorn database. Could you please post that code in addition to the code that you’ve already posted to compress it? Any help you could provide would be greatly appreciated. Thanks very much for your help.

apsmith
Site Admin
Posts: 2051
Joined: Wed Dec 31, 1969 8:00 pm

Re: Email message bodies - Compression?

Post by apsmith » Thu Feb 18, 2010 11:37 am

Gibbonas,

I am working with Engineering on getting further resources to assist with this.
Andrew Smith
Director, Technical Operations
Developer Forum Admin

apsmith
Site Admin
Posts: 2051
Joined: Wed Dec 31, 1969 8:00 pm

Re: Email message bodies - Compression?

Post by apsmith » Thu Feb 18, 2010 12:52 pm

Gibbonas,

I spoke to development on your issues and here is their findings:

The fundamental assumption in your posts is that you can read the bytes from the database, and write them to a file with a .zip extension. That is not true.

What you should do is read them from the database, and pass them through Chilkat to produce uncompressed HTML.

Please see: http://www.example-code.com/csharp/zip-in-memory.asp

I think what you want is something like:

Chilkat.Zip zip = new Chilkat.Zip();
zip.UnlockComponent("your serial here");
zip.OpenFromMemory(bytesFromDatabase);
string html = zip.GetEntryByIndex(0).InflateToString2();

..the above code has not been confirmed to working, but should be used as an example to build on.
Andrew Smith
Director, Technical Operations
Developer Forum Admin

Post Reply