SQL query to retrieve the values I want, stored into a single row

Question

I'm trying to migrate from Goldmine 5.5 to MCRM 2011 using Starfish ETL.

The problem is that there is a table called Mailbox in the Goldmine database where all the information I need to migrate incoming e-mails are stored in a single row called rfc822 (full headers and body message). There is no row for the sender/recipient/etc even in the other tables.

I use this query as Origin in my ETL software to get the information I need from the origin database (Goldmine) but the default map provided with Starfish don't work in my case.

SELECT  ch.*, c1.PHONE1, mb.RFC822 
FROM    CONTHIST ch 
        INNER JOIN CONTACT1 c1 
            ON ch.ACCOUNTNO=c1.ACCOUNTNO 
        INNER JOIN MAILBOX mb 
            ON ch.recid=mb.LINKRECID 
WHERE   ch.RECTYPE='MI'

After that I can map the destination MCRM email table and add function fields (vbscript, C#).

Most of the time, once the job is completed, the sender/recipients are reverted to the wrong values (e.g. a user in the BCC field).

What I would like to know is:

• How do I extract the CC and BCC fields, which are stored in the rfc822 row
• There are e-mails formatted in both HTML and plain/text and the only way to make the distinction is to look for it in this row. Also, Dynamics does not store it in the same way.
• How can I use SQL to retrieve the sender and recipient(s)?

I figure there must be a way to do the job with SQL queries.

Answer 1

GoldMine 5.5 (! wow) - my condolences (bot in fact, you are lucky because in future versions of GoldMine rfc822 field is encrypted as well)

I see two options to solve your problem (all requires SQL Server 2005 or above):

1. integration with SQLCLR to be able to use regular expressions to parse rfc822 field
2. or use GoldMineAPI (and integration with SQLCLR if you want to use GoldMine API from SQL) to get emails throug business logic functions

---

I have some examples of both options:

For instance,

an example of using SQLCLR to parse HTML

I wrote it for once to parse ContHist notes for GoldMine 9.0

SQLCLR Function (C#)

[SqlFunction()]
public static SqlString RemoveHTML(SqlString html)
{
    if (!html.IsNull)
    {
        string remove_style = @"\<STYLE((\S|\ |\s)+?)\<\/STYLE\>";
        string remove_html = @"(?></?\w+)(?>(?:[^>'""]+|'[^']*'|""[^""]*"")*)>";

        string result = Regex.Replace(html.Value, remove_style, string.Empty, RegexOptions.IgnoreCase);
        result = Regex.Replace(result, remove_html, string.Empty, RegexOptions.IgnoreCase);

        return result;
    }
    else return new SqlString();
}

Installation to DB

USE YourDB
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

ALTER DATABASE YourDB SET TRUSTWORTHY ON
----
CREATE ASSEMBLY GMSQLCLRAPI
FROM 'DRIVE:\Path\To\DLL\dll_name.dll'
with permission_set = UNSAFE;
--
CREATE FUNCTION RemoveHTML(@html NVARCHAR(MAX))
RETURNS NVARCHAR(MAX) WITH EXECUTE AS CALLER
AS EXTERNAL NAME GMSQLCLRAPI.[CLSQLCLR.GMSQLCLRAPI].RemoveHTML

Usage

select dbo.RemoveHTML(rfc822) from Mailbox

Using this approach, you can replace a regular expression in SQLCLR Function to your own, which will parse the recipients for you.

and example of using GoldMine API

There are actually quite a lot of code, I will provide a few pieces

С# wrapper function to call ReadMail GoldMine API function

public Dictionary<string, string> ReadMail(Dictionary<string, string> NVDictionary)
{
    const string failure = "Failure to read email\n";
    Int32 res;
    Dictionary<string, string> result = null;

    IntPtr nvc = import.GMW_NV_Create();
    if (nvc == null) throw (new CLGMAPIException(failure + "Unable to create name/value container"));
    try
    {
        foreach (KeyValuePair<string, string> kvp in NVDictionary)
        {
            import.GMW_NV_SetValue(nvc, kvp.Key, kvp.Value);
        }

        res = import.GMW_Execute("ReadMail", nvc);

        if (res > 0)
        {
            int count = import.GMW_NV_Count(nvc);
            result = new Dictionary<string, string>(count);
            IntPtr ptr;
            string name;

            for (int i = 0; i < count; i++)
            {
                ptr = import.GMW_NV_GetNameFromIndex(nvc, i);
                name = Marshal.PtrToStringAnsi(ptr);

                ptr = import.GMW_NV_GetValue(nvc, name, string.Empty);
                result[name] = Marshal.PtrToStringAnsi(ptr);
            }
        }
    }
    finally
    {
        import.GMW_NV_Delete(nvc);
    }

    switch (res)
    {
        case 0: throw (new CLGMAPIException(failure));
        case -1: throw (new CLGMAPIException(failure + "Message is private"));
        case -2: throw (new CLGMAPIException(failure + "Message not found, or cannot be loaded"));
        case -3: throw (new CLGMAPIException(failure + "Exception"));
        default:
            break;
    }

    return result;
}

Wrapper usage

public GoldMineEmails(GoldMineAPI api, DataTable mailbox)
{
    foreach (DataRow emailRow in mailbox.Rows)
    {
        var dict = new Dictionary<string, string>(3);
        dict["MboxRecID"] = emailRow["recid"].ToString();
        dict["History"] = "1";

        try
        {
            var res = api.ReadMail(dict);

            var email = new GoldMineEmail(res["To"], res["Cc"], res["Bcc"], res["Outgoing"])
            {
                body = res["Body"],
                From = res["From"],
                subject = res["Subject"],
                user = res["User"]
            };

            this.Add(email);
        }
        catch
        {
            throw;
            // error logging
        }
    }
}

I hope this helps. Do not hesitate to contact me - I can send you examples of source codes