Email message bodies - Compression?

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

Moderators: StaffingSupport, s.emmons

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

Re: Email message bodies - Compression?

Post by Gibbonas » Tue Feb 23, 2010 9:52 am

I purchased the Chilkat sofware you mentioned and tried using the example you also suggested from their site and still got errors trying to unzip the byte array. I went to Chilkat and gave them both the code you provided to store the email body to the database and my code to extract it to see if they could tell me what the error was that I was getting and why. They are "99.999%" sure that it is not a problem with the OpenFromMemory function and suggesting writing the byte array to a file and unzipping it with a program like Winzip. They say if I cannot write the byte array to a file and unzip it, then I don't have a valid zip file in the byte array. Below is my portion of code that is reading the information from the database and then calling the OpenFromMemory function where it fails.

Code: Select all

                
 SqlConnection conn = new SqlConnection("connection string");
                 SqlCommand cmd = new SqlCommand("Select commentscompressed from bullhorn1.BH_UserMessage  where usermessageid = " + usermessageid + "", conn); 
                 conn.Open();
                 SqlDataReader reader = cmd.ExecuteReader();

                     byte[] fdatabytes = (byte[])reader.GetValue(0);
                     success = zip.OpenFromMemory(fdatabytes);
I've had have several other developers take a look at this with me along with talking to Chilkat. What is it that we are all missing?

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

Re: Email message bodies - Compression?

Post by apsmith » Tue Feb 23, 2010 12:46 pm

Gibbonas,

Here is a working example that our development team has worked on:

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1 {
    class Program {
        static void Main(string[] args) {

            SqlConnection conn = new SqlConnection("***");
            SqlCommand cmd = new SqlCommand("select commentsCompressed from bullhorn1..BH_UserMessage where userMessageID = 10518", conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            reader.Read();
            Byte[] bytes = null;
            bytes = new Byte[(reader.GetBytes(0, 0, null, 0, int.MaxValue))];
            reader.GetBytes(0, 0, bytes, 0, bytes.Length);
            reader.Close();
            conn.Close();
            Console.WriteLine("Read " + bytes.Length + " bytes from database.");         

            Chilkat.Zip zip = new Chilkat.Zip();
            Console.WriteLine(zip.UnlockComponent("***"));
            zip.NewZip("test.zip");
            Chilkat.ZipEntry entry = zip.AppendCompressed("test.txt", bytes);
            Console.WriteLine(entry.InflateToString2());

            Console.ReadLine();
        }
    }
}

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 23, 2010 5:41 pm

That finally did it! Thank you for help.

jcasani1
User
Posts: 3
Joined: Wed Dec 31, 1969 8:00 pm

Re: Email message bodies - Compression?

Post by jcasani1 » Mon Mar 19, 2012 10:33 am

Andrew,

We are having issues in decompressing the emails. we tried decompressing using "Chilkat" and with the below code.
We were able to create the ZIP file but when we try to access it, it shows "ZIP file is Invalid or Corrupted".

Can you please give us the step by step procedure to decompress the emails?

Code Used for Decompression:-

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.chilkatsoft.CkByteData;
import com.chilkatsoft.CkZip;

public class BullHornExtract {
static {
try {
System.out.println(System.getProperty("java.library.path"));
System.loadLibrary("chilkat");

} catch (UnsatisfiedLinkError e) {
System.err.println("Native code library failed to load.\n" + e);
System.exit(1);
}
}

Connection m_Conn;

private boolean openConnection() {
// String connect to SQL server
String url = "jdbc:sqlserver://localhost:1433;databaseName=BH";

try {
// Loading the driver...
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (java.lang.ClassNotFoundException e) {
e.printStackTrace();
return false;
}

try {
// Buiding a Connection
m_Conn = DriverManager.getConnection(url, "user", "password");
if (m_Conn == null)
return false;
} catch (java.sql.SQLException e) {
e.printStackTrace();
}
return true;
}


public void run(){
Statement stmt=null;
ResultSet rs =null;
try {
if (!openConnection()){
return;
}

stmt = m_Conn.createStatement();
rs = stmt.executeQuery("SELECT commentsCompressed FROM Query where userMessageID=897");

while (rs.next()){

Blob blob = rs.getBlob(1);
InputStream in = blob.getBinaryStream();
ByteArrayOutputStream baos = new ByteArrayOutputStream();
int count=0;
byte[] data = new byte[1024];

while ((count=in.read(data))!=-1){
baos.write(data,0,count);
}
in.close();
byte[] compressed = baos.toByteArray();
baos.close();


//String comp = rs.getString(1);
//byte[] compressed = comp.getBytes();
System.out.println(compressed.length);
System.out.println(new String(compressed));

// unable to extract t1.zip with any Zip program
FileOutputStream faos = new FileOutputStream(new File("c:\\temp\\t1.zip"));
faos.write(compressed,0,compressed.length);
faos.flush();
faos.close();

// trying Chilkat
CkZip zip = new CkZip();
zip.UnlockComponent("license");
System.out.println(zip.version());

CkByteData ckbdata = new CkByteData();
ckbdata.appendByteArray(compressed);
//ckbdata.appendStr(comp);

// the code below says its not a valid Zip Stream
if (zip.OpenFromByteData(ckbdata)){
String xml = zip.getDirectoryAsXML();
System.out.println(xml);
String html = zip.GetEntryByIndex(0).inflateToString2();
System.out.println(html);
} else {
System.out.println("not a zip stream");
}

/*
ZipInputStream zin = new ZipInputStream(new ByteArrayInputStream(compressed));
ZipEntry ze = zin.getNextEntry();
System.out.println(ze.getCompressedSize());
*/
}

} catch (Exception e){
e.printStackTrace();
}

finally {
try {
if (m_Conn!=null)
m_Conn.close();

if (stmt!=null)
stmt.close();
if (rs!=null)
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
}

public static void main(String [] args){
BullHornExtract extract = new BullHornExtract();

extract.run();


}
}
Last edited by jcasani1 on Mon Mar 19, 2012 10:49 am, edited 1 time in total.

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

Re: Email message bodies - Compression?

Post by apsmith » Mon Mar 19, 2012 10:43 am

jcasani,

I am not sure what steps you are looking for? The example code above will perform the necessary decompression. What other details specifically are you looking for?
Andrew Smith
Director, Technical Operations
Developer Forum Admin

jcasani1
User
Posts: 3
Joined: Wed Dec 31, 1969 8:00 pm

Re: Email message bodies - Compression?

Post by jcasani1 » Mon Mar 19, 2012 10:58 am

Andrew,

Can you please check the code which i have posted in my previous post? Please let me know if we are taking the right approach for selecting and decompressing the image data.

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

Re: Email message bodies - Compression?

Post by apsmith » Mon Mar 19, 2012 11:22 am

jcasani,

Are you getting all emails as corrupted? Any coming out clean?

Also, your code seems quite a bit different from the sample code provided (that has worked for many clients), have you tried using the provided code exactly?
Andrew Smith
Director, Technical Operations
Developer Forum Admin

stevew
User
Posts: 3
Joined: Wed Jul 18, 2012 8:18 pm

Re: Email message bodies - Compression?

Post by stevew » Tue Oct 16, 2012 10:10 am

Do you have any sample code that would run in coldfusion?

Is there no way to write the binary file out directly to disk as a zip?

I've tried a number of different option which lead to corrupt .zip files like everyone else, and I found a sql 2000 utility that seems to work for other files, textcopy. It generates the local file for me, but again, still corrupt.

steve

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

Re: Email message bodies - Compression?

Post by apsmith » Tue Oct 16, 2012 10:30 am

Steve,

Unfortunately, we do not have any sample code aside from what is found in this forum.
Andrew Smith
Director, Technical Operations
Developer Forum Admin

Technigogo
User
Posts: 3
Joined: Fri Oct 19, 2012 4:53 pm

Re: Email message bodies - Compression?

Post by Technigogo » Fri Oct 19, 2012 5:08 pm

I am looking for a programmer to write code to extract emails from Bullhorn backup to a format that I can import into hosted exchange server. Please contact me at steve@technigogo.com

nickf77
User
Posts: 5
Joined: Wed Jul 10, 2013 5:40 pm

Re: Email message bodies - Compression?

Post by nickf77 » Wed Jul 24, 2013 8:19 am

A bit of guidance for anyone else out there struggling with this, as I picked my hair out for about a week straight trying to crack this compression.

A couple specific things I came across while unzipping email bodies:
  1. Use the sample C# code provided in this thread! The code's already been written for you - it's just a matter of changing the "conn" variable and looping through the rows with

    Code: Select all

    while (reader.read()) {     }
    .

    I tried mimicking this code in Java and VB.NET, but it seems that Chilkat's libraries (which have a 30 day free trial, mind you) differ slightly by language. Just use C#!
  2. If you're inserting the uncompressed messages back into a SQL database, watch their lengths. Everyone has different intentions once they break the compression on the messages, but I wanted to insert them back into a commentsUncompressed field because it was easier to insert them into my new CMS from a SQL database. Simply using an INSERT statement with the uncompressed message text in it worked for many emails, but threw an error on the longer ones (somewhere around 4000 characters I believe). If you run into that problem, use a DataSet and TableAdapter to edit the rows locally, then flush them all back to the database at one time.
  3. Depending on how many emails you have, RAM could be an issue. Even with 6GB and all other processes closed, I had to break up my conversions into batches of about 40,000 each. Just add a WHERE clause to the end of the connection string: "SELECT... FROM BH_UserMessage WHERE userMessageID > xxx AND userMessageID < xxx".
  4. Not sure if it was at the time of the original post, but InflateToString2() is not a method in Chilkat's Zip library. In its place, I used Inflate() to produce a byte[], then created a UTF8-encoded string from that byte[]. Small tweak, but took me a little but to figure that out.
    EDIT: UnzipToString() should work.

I'll admit, I'm usually not one to go out of my way to offer help to others after the fact :oops: but this time I truly empathize with others struggling here, especially since migrating data from Bullhorn is way harder than it should be (sorry BH admins reading this). If anyone else would like some advice or guidance, feel free to PM or email me and I'll try to help.
Last edited by nickf77 on Wed Aug 14, 2013 11:16 pm, edited 1 time in total.

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

Re: Email message bodies - Compression?

Post by apsmith » Wed Jul 24, 2013 11:50 am

Great post NIck!

Thanks for all the information.
Andrew Smith
Director, Technical Operations
Developer Forum Admin

clarcon
User
Posts: 7
Joined: Tue Jun 11, 2013 1:14 pm

Re: Email message bodies - Compression?

Post by clarcon » Wed Aug 07, 2013 2:05 pm

Nick,

I could use some help with this. I am not able to PM or email you through this forum. I have been struggling with this as well with VB and I do not write in C#.

nickf77 wrote:A bit of guidance for anyone else out there struggling with this, as I picked my hair out for about a week straight trying to crack this compression.

A couple specific things I came across while unzipping email bodies:
  1. Use the sample C# code provided in this thread! The code's already been written for you - it's just a matter of changing the "conn" variable and looping through the rows with

    Code: Select all

    while (reader.read()) {     }
    .

    I tried mimicking this code in Java and VB.NET, but it seems that Chilkat's libraries (which have a 30 day free trial, mind you) differ slightly by language. Just use C#!
  2. If you're inserting the uncompressed messages back into a SQL database, watch their lengths. Everyone has different intentions once they break the compression on the messages, but I wanted to insert them back into a commentsUncompressed field because it was easier to insert them into my new CMS from a SQL database. Simply using an INSERT statement with the uncompressed message text in it worked for many emails, but threw an error on the longer ones (somewhere around 4000 characters I believe). If you run into that problem, use a DataSet and TableAdapter to edit the rows locally, then flush them all back to the database at one time.
  3. Depending on how many emails you have, RAM could be an issue. Even with 6GB and all other processes closed, I had to break up my conversions into batches of about 40,000 each. Just add a WHERE clause to the end of the connection string: "SELECT... FROM BH_UserMessage WHERE userMessageID > xxx AND userMessageID < xxx".
  4. Not sure if it was at the time of the original post, but InflateToString2() is not a method in Chilkat's Zip library. In its place, I used Inflate() to produce a byte[], then created a UTF8-encoded string from that byte[]. Small tweak, but took me a little but to figure that out.

I'll admit, I'm usually not one to go out of my way to offer help to others after the fact :oops: but this time I truly empathize with others struggling here, especially since migrating data from Bullhorn is way harder than it should be (sorry BH admins reading this). If anyone else would like some advice or guidance, feel free to PM or email me and I'll try to help.

chris.sargent
User
Posts: 30
Joined: Wed Dec 31, 1969 8:00 pm

Re: Email message bodies - Compression?

Post by chris.sargent » Wed Aug 14, 2013 6:23 pm

Hi All,

Okay, so I am migrating away from Bullhorn at the moment. Bit of a shame that the email messages are in an unusual format but never mind I guess. I have the following code which successfully reads the fields from the database and outputs the uncompressed data to the console.

From there, I wanted to store this uncompressed text as a Variable called UnCompBody (which is fine) and then UPDATE this back in to the database in to a new column called commentsUnCompressed - this is where it falls down because the compiler reads the HTML in the uncompressed message bodies as code and since the characters are not escaped and it's reading the first " as the end of the string.

I'm not quite sure where to go from here or whether this is the right strategy to go down.... any help would be much appreciated.

By the way, although InflateToString2 is no longer used, you can use UnzipToString instead.

Here's my code:

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace UncompressEmailBodiesBH
{
    class Program
    {
        static void Main(string[] args)
        {

            SqlConnection conn = new SqlConnection("Data Source=PENTHOUSEWIN8;Initial Catalog=BULLHORN1755;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("SELECT commentsCompressed from bullhorn1.BH_UserMessage where userMessageID = 1508", conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            reader.Read();
            Byte[] bytes = null;
            bytes = new Byte[(reader.GetBytes(0, 0, null, 0, int.MaxValue))];
            reader.GetBytes(0, 0, bytes, 0, bytes.Length);
            reader.Close();
            conn.Close();
            Console.WriteLine("Read " + bytes.Length + " bytes from database.");

            Chilkat.Zip zip = new Chilkat.Zip();
            Console.WriteLine(zip.UnlockComponent("aaaa"));
            zip.NewZip("test.zip");
            Chilkat.ZipEntry entry = zip.AppendCompressed("test.txt", bytes);

            string unCompBody;
            unCompBody = entry.UnzipToString(0, "UTF-8");
            Console.WriteLine(unCompBody);
            
            SqlCommand cmdcs = new SqlCommand("UPDATE bullhorn1.BH_UserMessage SET commentsUnCompressed = '"+unCompBody+"' WHERE userMessageID = 12" , conn);
            conn.Open();
            cmdcs.ExecuteNonQuery();
            conn.Close();

            Console.WriteLine("I'm finished, press any key");
            Console.ReadLine();


        }
    }
}

nickf77
User
Posts: 5
Joined: Wed Jul 10, 2013 5:40 pm

Re: Email message bodies - Compression?

Post by nickf77 » Wed Aug 14, 2013 11:14 pm

chris.sargent,

You won't be able to do it with just a text-written command - like you said, the text can't be escaped and many characters in an email will break the command. Use a DataSet and TableAdapter. The entire table can be "filled" using 1 line of code, you can easily reference any row and any column in the table at any time, loop through the rows easily, update the data locally in memory, then "flush" the updated data back to the original table, and it handles all the statements behind the scenes. Take some time to read up on the concept of DataSets and Adapters if you've never worked with them before... basically it reads your DB into a local "copy" in memory (watch your RAM!!), performs the operations on the local one, then overwrites the original with the local one when you call Flush().

Using a DataSet to connect to the database and a TableAdapter to connect to the table worked perfectly for me... I was able to pull out 1 row at a time, uncompress, and pull out the sender/recipient for some other manipulation. Post back with results, I'm curious to know how others do with this.

Post Reply