Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment   Page 2 of 2      Prev   1   2
jfrancis

New Member
Registered:
Posts: 6
Reply with quote  #16 
Quote:
Originally Posted by BMellert
If you are looking to dump attachments, you could create a process to loop through them, use Mstm.GetAttachment(args) which includes the patch/name to write them to.  We do this for daily notices for multiple folders, when conditions warrant for that particular email.

We also use a byte array to process files we get from a web service, then use System.IO.File.WriteAllBytes(args) to write it to disk so we can display it.

I've never tried the SQL Execute reader, but it's something I may keep in the mind for the future.


Yeah I basically need everything in the eattachment table converted to whichever file it was when it originally got uploaded. 

I've seen 'Mstm.GetAttachment' metnioned in several places when searching for help with this. Where would I get the method though? Is it only for V9? I can't find any libraries by that name, I'm guessing there's a dll somewhere in the Metastorm install directory that i need to reference, or something similar? 

I got my SQL script working, except the files exported from the database are all corrupt. I suspect this is to do with the base64 encoding so need to work around that. I'd prefer a C# solution though as it handles files better than SQL server does.
0
BMellert

Guru
Registered:
Posts: 688
Reply with quote  #17 
I don't have access to a V7 system any longer, so I don't recall if it is there or not.  Mstm.GetAttachment() is indeed a V9 built-in procedure at least.  I was responding presuming you had access to the MBPM engine and could create a solution.

I do see a few attachment related stored procedures in the database that I suspect MBPM uses (such as esp_pull_attachment and esp_get_attachment) but I never thought about how it was implemented from there.
0
jfrancis

New Member
Registered:
Posts: 6
Reply with quote  #18 
Incase anyone comes across this in the future, I managed to get this working by using a CLR function combined with some SQL.


[SqlFunction(IsDeterministic = true,
                         IsPrecise = true,
                         DataAccess = DataAccessKind.None,
                         SystemDataAccess = SystemDataAccessKind.None)]
        public static SqlString WriteToFile(SqlBytes binary, SqlString path, SqlBoolean append)
            {
                try
                {
                    if (!binary.IsNull && !path.IsNull && !append.IsNull)
                    {
                        var dir = Path.GetDirectoryName(path.Value);
                        if (!Directory.Exists(dir))
                            Directory.CreateDirectory(dir);
                        using (var fs = new FileStream(path.Value, append ? FileMode.Append : FileMode.OpenOrCreate))
                        {
                            byte[] byteArr = binary.Value;
                            string s2 = System.Text.Encoding.ASCII.GetString(byteArr);
                            // remove GUID ({501F235F-75F0-4964-9A79-AE6350BB918C})
                            byteArr = Convert.FromBase64String(s2.Substring(38));
                        for (int i = 0; i < byteArr.Length; i++)
                            {
                                fs.WriteByte(byteArr[i]);
                            };
                        }
                        return "SUCCESS";
                    }
                    else
                        return "NULL INPUT";
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }



Import the CLR with 'External access' and define the function as follows;


EXEC sp_changedbowner 'sa' --needed for 'external access' to work

--Once the assembly is imported, run this;
Create Function WriteToFile(@Input varbinary(max), @path nvarchar(max), @append bit )
   returns nvarchar(max)        
   AS
   External name <assembly name>.[<assembly name>.<namespace>].WriteToFile



Usage:


declare @econtents varbinary(max), @fpath nvarchar(255)
set @econtents = SELECT TOP 1 econtents FROM eattachment where ...
set @fpath = 'c:\temp'
dbo.WriteToFile(@econtents, @fPath, 0)


Seems to be ok for the few I've tried so far!
0
jfrancis

New Member
Registered:
Posts: 6
Reply with quote  #19 
At least the first 30 or so characters from eContents are always the same. Does anyone know the difference between files that start with this:

0x7B00350030003100460032003300350046002D0037003500

and files that start with this: 

0x7B35303146323335462D373546302D343936342D394137392

It seems that the first type become corrupt or throw an error (System.FormatException: Invalid character in a Base-64 string.) when saved by my script above, while the other ones save without any problems, every time.


0
jfrancis

New Member
Registered:
Posts: 6
Reply with quote  #20 
Ok I've found the answer. It's to do with encoding. Hopefully this helps someone one day.

Some attachments are unicode, some are ascii. The difference between the two formats above is that one of them has 00 bytes in between each of the data bytes. This is UTF-16, where all symbols are 16 bits (2 bytes). The compact data without those 00 bytes should be plain ASCII.

Changed my code as follows and I can pull all attachments out now


public partial class UserDefinedFunctions
    {
        [SqlFunction(IsDeterministic = true,
                         IsPrecise = true,
                         DataAccess = DataAccessKind.Read,
                         SystemDataAccess = SystemDataAccessKind.Read)]
        public static SqlString WriteToFile(SqlString path, SqlString efolderid, SqlString fileName)
        {
            try
            {
                if (!path.IsNull && !efolderid.IsNull && !fileName.IsNull)
                {
                    var dir = Path.GetDirectoryName(path.Value);
                    if (!Directory.Exists(dir))
                        Directory.CreateDirectory(dir);
                    string filename = Convert.ToString(fileName);
                    string folderid = Convert.ToString(efolderid);
                    string filepath = Convert.ToString(path);
                    SaveAttachmentToFile(filename, folderid, filepath);
                    return "Wrote file";
                }
                else
                    return "No data passed to method!";
            }
            catch (IOException e)
            {
                return "Make sure the assembly has external access!\n" + e.ToString();
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }
        private const int guidLength = 38 * 2;
        public static byte[] GetAttachment(string file, string efolderid)
        {
            string queryString = string.Format("SELECT eContents FROM eAttachment WHERE eKey = '0\t{0}\t{1}'",
                      efolderid, file);
            using (SqlConnection connection = new SqlConnection("context connection=true"))
            {
                connection.Open();
                using (SqlCommand selectAttachment = new SqlCommand(
                    queryString,
                    connection))
                {
                    using (SqlDataReader reader = selectAttachment.ExecuteReader())
                    {
                        if (!reader.Read())
                            return new byte[0];
                        if (reader[0] == System.DBNull.Value)
                            return new byte[0];
                        byte[] data = (byte[])reader[0];
                        if (data.Length == 0)
                            return new byte[0];
                        String base64String;
                        if (data.Length > 1 && data[1] == 00)
                            base64String = Encoding.Unicode.GetString(data);
                        else
                            base64String = Encoding.ASCII.GetString(data);
                        // Cuts off the GUID, and takes care of any trailing 00 bytes.
                        String truncatedString = base64String.Substring(38).TrimEnd('\0');
                        return Convert.FromBase64String(truncatedString);
                    }
                }
            }
        }
        /// <summary>
        /// Saves the specified attachment to a file on disk.
        /// </summary>
        /// <param name="type">The type of attachment.</param>
        /// <param name="file">The attachment filename.</param>
        /// <param name="folderid">The owner of the attachment (folder ID for folder attachments, map name for map attachments, procedure name for procedure attachments).</param>
        /// <param name="fileName">Name of the file to save to.</param>
        public static void SaveAttachmentToFile(string file, string folderid, string fileName)
        {
            byte[] data = GetAttachment(file, folderid);
            if (data == null)
                throw new ArgumentNullException("Attachment has no data, it may have been deleted");
            using (FileStream writer = new FileStream(fileName, FileMode.Create))
            {
                writer.Write(data, 0, data.Length);
            }
        }
    }



0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!