(SQL Server) StringBuilder to Bytes (in BinData)

Demonstrates converting a string to bytes of a particular character encoding.

Note: This example requires Chilkat v9.5.0.64 or later.

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)
    -- This initial step gets string data to be used in this example from a live URL.
    -- It assumes Chilkat HTTP API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    EXEC sp_OASetProperty @http, 'VerboseLogging', 1
    DECLARE @sbJson int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbJson OUT

    DECLARE @success int
    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://www.chilkatsoft.com/exampleData/greekAlphabet.json', @sbJson
    IF Not @success
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbJson
        RETURN
      END

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

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

    EXEC sp_OAMethod @json, 'LoadSb', @success OUT, @sbJson

    -- The JSON contains this:

    -- 	{ 
    -- 	  "greekAlphabet": "Α α Β β Γ γ Δ δ Ε ε Ζ ζ Η η Θ θ Ι ι Κ κ Λ λ Μ μ Ν ν Ξ ξ Ο ο Π π Ρ ρ Σ σ ς Τ τ Υ υ Φ φ Χ χ Ψ ψ Ω ω"
    -- 	} 

    DECLARE @greekAlphabet nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @greekAlphabet OUT, 'greekAlphabet'
    EXEC sp_OAMethod @sbGreek, 'Append', @success OUT, @greekAlphabet

    -- Append the Windows-1253 represention of the string..
    -- In this encoding, each Greek char is one byte per char.
    DECLARE @binWindows1253 int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @binWindows1253 OUT

    EXEC sp_OAMethod @binWindows1253, 'AppendSb', @success OUT, @sbGreek, 'windows-1253'
    -- Save to a Windows-1253 text file:
    EXEC sp_OAMethod @binWindows1253, 'WriteFile', @success OUT, 'qa_output/greek_win1253.txt'

    -- Show the bytes in the quoted-printable format so we can easily see the Windows-1253 byte values.
    EXEC sp_OAMethod @binWindows1253, 'GetEncoded', @sTmp0 OUT, 'qp'
    PRINT @sTmp0

    PRINT '--------------------------------'

    -- The result is shown here:
    -- 	=C1 =E1 =C2 =E2 =C3 =E3 =C4 =E4 =C5 =E5 =C6 =E6 =C7 =E7 =C8 =E8 =C9 =E9 =CA=
    -- 	 =EA =CB =EB =CC =EC =CD =ED =CE =EE =CF =EF =D0 =F0 =D1 =F1 =D3 =F3 =F2 =
    -- 	=D4 =F4 =D5 =F5 =D6 =F6 =D7 =F7 =D8 =F8 =D9 =F9

    -- Append the utf-8 represention of the string..
    DECLARE @binUtf8 int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @binUtf8 OUT

    EXEC sp_OAMethod @binUtf8, 'AppendBom', @success OUT, 'utf-8'
    EXEC sp_OAMethod @binUtf8, 'AppendSb', @success OUT, @sbGreek, 'utf-8'
    -- Save to a utf-8 text file:
    EXEC sp_OAMethod @binUtf8, 'WriteFile', @success OUT, 'qa_output/greek_utf8.txt'
    -- Show the bytes in the quoted-printable format so we can easily see the utf-8 char representations.
    EXEC sp_OAMethod @binUtf8, 'GetEncoded', @sTmp0 OUT, 'qp'
    PRINT @sTmp0


    PRINT '--------------------------------'

    -- These are the utf-8 bytes shown in quoted-printable encoding:
    -- 	=EF=BB=BF=CE=91 =CE=B1 =CE=92 =CE=B2 =CE=93 =CE=B3 =CE=94 =CE=B4 =CE=95 =CE=
    -- 	=B5 =CE=96 =CE=B6 =CE=97 =CE=B7 =CE=98 =CE=B8 =CE=99 =CE=B9 =CE=9A =CE=BA =
    -- 	=CE=9B =CE=BB =CE=9C =CE=BC =CE=9D =CE=BD =CE=9E =CE=BE =CE=9F =CE=BF =CE=
    -- 	=A0 =CF=80 =CE=A1 =CF=81 =CE=A3 =CF=83 =CF=82 =CE=A4 =CF=84 =CE=A5 =CF=85 =
    -- 	=CE=A6 =CF=86 =CE=A7 =CF=87 =CE=A8 =CF=88 =CE=A9 =CF=89

    -- Append the utf-16 (little endian) represention of the string..
    DECLARE @binUtf16 int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @binUtf16 OUT

    EXEC sp_OAMethod @binUtf16, 'AppendBom', @success OUT, 'utf-16'
    EXEC sp_OAMethod @binUtf16, 'AppendSb', @success OUT, @sbGreek, 'utf-16'
    -- Save to a utf-16 text file:
    EXEC sp_OAMethod @binUtf16, 'WriteFile', @success OUT, 'qa_output/greek_utf16.txt'
    EXEC sp_OAMethod @binUtf16, 'GetEncoded', @sTmp0 OUT, 'qp'
    PRINT @sTmp0

    -- These are the utf-16 bytes shown in quoted-printable encoding:
    -- 	=FF=FE=91=03 =00=B1=03 =00=92=03 =00=B2=03 =00=93=03 =00=B3=03 =00=94=03 =
    -- 	=00=B4=03 =00=95=03 =00=B5=03 =00=96=03 =00=B6=03 =00=97=03 =00=B7=03 =00=
    -- 	=98=03 =00=B8=03 =00=99=03 =00=B9=03 =00=9A=03 =00=BA=03 =00=9B=03 =00=BB=
    -- 	=03 =00=9C=03 =00=BC=03 =00=9D=03 =00=BD=03 =00=9E=03 =00=BE=03 =00=9F=03 =
    -- 	=00=BF=03 =00=A0=03 =00=C0=03 =00=A1=03 =00=C1=03 =00=A3=03 =00=C3=03 =00=
    -- 	=C2=03 =00=A4=03 =00=C4=03 =00=A5=03 =00=C5=03 =00=A6=03 =00=C6=03 =00=A7=
    -- 	=03 =00=C7=03 =00=A8=03 =00=C8=03 =00=A9=03 =00=C9=03

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbJson
    EXEC @hr = sp_OADestroy @sbGreek
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @binWindows1253
    EXEC @hr = sp_OADestroy @binUtf8
    EXEC @hr = sp_OADestroy @binUtf16


END
GO