SQL Server
SQL Server
MIME Content-Transfer-Encoding Header Field
See more MIME Examples
Explains the Content-Transfer-Encoding header field and how it affects how data is stored in the MIME.
Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- 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 @sbJpgBase64 int
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
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..
ERROR: Cannot use pbytes in SQL Server examples...
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..
ERROR: Cannot use pbytes in SQL Server examples...
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