(SQL Server) MIME Content-Transfer-Encoding Header Field

Explains the Content-Transfer-Encoding header field and how it affects how data is stored in the MIME.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- The Content-Transfer-Encoding header field is typically set to one of these values:
    -- 
    --   base64
    --   quoted-printable
    --   binary
    --   8bit
    --   7bit

    -- If the encoding is base64 or quoted-printable, then the bytes of the content are encoded as such.
    -- The values "8bit", "7bit", and "binary" all imply that NO encoding has been performed.

    -- Here are some guidelines and hints:
    -- 
    -- 1) "8bit", "7bit", and "binary" encodings (i.e. NO encoding) results in the smallest MIME.

    -- 2) A "binary" encoding implies non-text data.  Binary MIME CANNOT be retrieved as a string.  It can only be retrieved as a byte array.

    -- 3) "8bit" and "7bit" encodings imply text.  
    --    7bit implies that the text is us-ascii (all byte values less than or equal to 0x7F).
    --    8bit implies that the text contains non-us-ascii chars.

    -- 4) Base64 is the best choice for encoding non-text data, such as PDF's, images, etc.

    -- 5) quoted-printable is the best choice for encoding text data where most chars are going to be us-ascii. This would typically 
    --    include all Western European languages.  For Asian, Arabic, Hebrew, etc. where most chars in the text are non-us-ascii,
    --    the most efficient encoding would be base64.  

    -- Let's demonstrate with this small JPG image.
    DECLARE @success int

    DECLARE @sbJpgBase64 int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbJpgBase64 OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, '/9j/4AAQSkZJRgABAQEASABIAAD//gAmRmlsZSB3cml0dGVuIGJ5IEFkb2JlIFBob3Rvc2hvcD8g' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'NC4w/9sAQwAQCwwODAoQDg0OEhEQExgoGhgWFhgxIyUdKDozPTw5Mzg3QEhcTkBEV0U3OFBtUVdf' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'YmdoZz5NcXlwZHhcZWdj/9sAQwEREhIYFRgvGhovY0I4QmNjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'Y2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj/8IAEQgAFAAUAwERAAIRAQMRAf/EABcAAAMBAAAA' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'AAAAAAAAAAAAAAIDBAX/xAAYAQADAQEAAAAAAAAAAAAAAAABAgMEAP/aAAwDAQACEAMQAAAB2kZY' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'NNEijWKddfTmLgALWH//xAAbEAACAgMBAAAAAAAAAAAAAAABAgMRAAQSE//aAAgBAQABBQL0XqN+' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'pM2aqJGMiqFFCyg7z//EABwRAAICAgMAAAAAAAAAAAAAAAERAAIQIQMSUf/aAAgBAwEBPwHqU5aq' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'Axx+y1tMQl4elj//xAAcEQEAAQUBAQAAAAAAAAAAAAABEQACEBIhA1H/2gAIAQIBAT8B3Bhqy7Zc' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'enyiwmGgDhiOzj//xAAdEAABAwUBAAAAAAAAAAAAAAABAAIREBIhIkFR/9oACAEBAAY/ArZyn+Cg' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'xtxWuJaoCnqDuin/xAAcEAABBAMBAAAAAAAAAAAAAAABABEhYRAxQVH/2gAIAQEAAT8hkEwPUUR9' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'DYfE4nxtRpIkBTsayuALIiuY/9oADAMBAAIAAwAAABDWPTsf/8QAGhEAAwADAQAAAAAAAAAAAAAA' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'AAEREDFBIf/aAAgBAwEBPxC0DVPcWm+Ce4OesrkE6bjH/8QAGBEBAQEBAQAAAAAAAAAAAAAAAREA' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'QRD/2gAIAQIBAT8QahMiOc8YgSrnTY3ELclHXn//xAAcEAEBAAIDAQEAAAAAAAAAAAABEQAhMUFx' + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @sbJpgBase64, 'Append', @success OUT, 'EFH/2gAIAQEAAT8Qn3igmSZSj+c4N4zapMy9IjFV98wncN2iuLFsCEbDGxQkI6RO/n//2Q==' + CHAR(13) + CHAR(10)

    DECLARE @mime int
    -- Use "Chilkat_9_5_0.Mime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Mime', @mime OUT

    EXEC sp_OASetProperty @mime, 'ContentType', 'image/jpeg'
    EXEC sp_OAMethod @sbJpgBase64, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @mime, 'SetBodyFromEncoded', @success OUT, 'base64', @sTmp0

    EXEC sp_OAMethod @mime, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    PRINT '-'

    -- The result:
    -- 
    -- 	Content-Type: image/jpeg
    -- 	Content-Transfer-Encoding: base64
    -- 
    -- 	/9j/4AAQSkZJRgABAQEASABIAAD//gAmRmlsZSB3cml0dGVuIGJ5IEFkb2JlIFBob3Rvc2hvcD8g
    -- 	NC4w/9sAQwAQCwwODAoQDg0OEhEQExgoGhgWFhgxIyUdKDozPTw5Mzg3QEhcTkBEV0U3OFBtUVdf
    -- 	YmdoZz5NcXlwZHhcZWdj/9sAQwEREhIYFRgvGhovY0I4QmNjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj
    -- 	Y2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj/8IAEQgAFAAUAwERAAIRAQMRAf/EABcAAAMBAAAA
    -- 	AAAAAAAAAAAAAAIDBAX/xAAYAQADAQEAAAAAAAAAAAAAAAABAgMEAP/aAAwDAQACEAMQAAAB2kZY
    -- 	NNEijWKddfTmLgALWH//xAAbEAACAgMBAAAAAAAAAAAAAAABAgMRAAQSE//aAAgBAQABBQL0XqN+
    -- 	pM2aqJGMiqFFCyg7z//EABwRAAICAgMAAAAAAAAAAAAAAAERAAIQIQMSUf/aAAgBAwEBPwHqU5aq
    -- 	Axx+y1tMQl4elj//xAAcEQEAAQUBAQAAAAAAAAAAAAABEQACEBIhA1H/2gAIAQIBAT8B3Bhqy7Zc
    -- 	enyiwmGgDhiOzj//xAAdEAABAwUBAAAAAAAAAAAAAAABAAIREBIhIkFR/9oACAEBAAY/ArZyn+Cg
    -- 	xtxWuJaoCnqDuin/xAAcEAABBAMBAAAAAAAAAAAAAAABABEhYRAxQVH/2gAIAQEAAT8hkEwPUUR9
    -- 	DYfE4nxtRpIkBTsayuALIiuY/9oADAMBAAIAAwAAABDWPTsf/8QAGhEAAwADAQAAAAAAAAAAAAAA
    -- 	AAEREDFBIf/aAAgBAwEBPxC0DVPcWm+Ce4OesrkE6bjH/8QAGBEBAQEBAQAAAAAAAAAAAAAAAREA
    -- 	QRD/2gAIAQIBAT8QahMiOc8YgSrnTY3ELclHXn//xAAcEAEBAAIDAQEAAAAAAAAAAAABEQAhMUFx
    -- 	EFH/2gAIAQEAAT8Qn3igmSZSj+c4N4zapMy9IjFV98wncN2iuLFsCEbDGxQkI6RO/n//2Q==
    -- 

    -- Set the Content-Transfer-Encoding to "binary" by setting the Encoding property.
    EXEC sp_OASetProperty @mime, 'Encoding', 'binary'

    -- Try to get the MIME string.  This CANNOT be done.  The bytes of the JPG image do not
    -- represent chars, and to return a string means that bytes must be interpreted according
    -- to some character encoding (such as utf-8).  Non-text binary bytes can only be 
    -- contained in a string IF encoded in some way. Encodings such as Base64, quoted-printable,
    -- URL, etc. exist to make it possible to represent binary data in string format.   
    EXEC sp_OAMethod @mime, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    PRINT '-'

    -- We CAN get the binary MIME as bytes..

    EXEC sp_OAMethod @mime, 'GetMimeBytes', @mimeBytes OUT

    -- Regardless of the Content-Transfer-Encoding, the 
    -- body content can always be retrieved and the body bytes
    -- decoded from whatever encoding is used..

    EXEC sp_OAMethod @mime, 'GetBodyBinary', @jpgBytes OUT

    -- To get the body in base64 format, first make sure
    -- the Content-Transfer-Encoding is base64, then call GetBodyEncoded.
    EXEC sp_OASetProperty @mime, 'Encoding', 'base64'
    DECLARE @jpgBase64 nvarchar(4000)
    EXEC sp_OAMethod @mime, 'GetBodyEncoded', @jpgBase64 OUT

    PRINT @jpgBase64

    PRINT '-'

    -- Let's go back to "binary" MIME..
    EXEC sp_OASetProperty @mime, 'Encoding', 'binary'

    -- Let's say we have MIME, and it was loaded directly from a file, or from
    -- a byte array.  (It was not loaded from the contents of a string variable.)
    -- We don't know whether the MIME contains binary or 8bit encodings, and thus
    -- we dont' know if the MIME is safe to get as a string.
    -- The Convert8Bit method can be called to recursively traverse the MIME and set
    -- all 8bit or binary encodings to "base64".  This makes the MIME safe for storing in
    -- a string.
    EXEC sp_OAMethod @mime, 'Convert8Bit', NULL
    DECLARE @mimeStr nvarchar(4000)
    EXEC sp_OAMethod @mime, 'GetMime', @mimeStr OUT

    PRINT @mimeStr

    PRINT '-'

    EXEC @hr = sp_OADestroy @sbJpgBase64
    EXEC @hr = sp_OADestroy @mime


END
GO