(SQL Server) Send UTF-16 on TCP or TLS Socket

See more Socket/SSL/TLS Examples

Demonstrates how to send a string on a connected TCP or SSL/TLS socket such that it is the utf-16 byte representation that is sent.

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 example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    DECLARE @tls int
    SELECT @tls = 0
    DECLARE @maxWaitMs int
    SELECT @maxWaitMs = 20000
    DECLARE @domainOrIpAddr nvarchar(4000)
    SELECT @domainOrIpAddr = 'example.com'
    DECLARE @port int
    SELECT @port = 1234

    -- Connect to the whatever is expecting our connection...
    DECLARE @success int
    EXEC sp_OAMethod @socket, 'Connect', @success OUT, @domainOrIpAddr, @port, @tls, @maxWaitMs
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    -- Let's say we have an XML file saved in the utf-16 byte representation,
    -- and we want to send it over the connection using the utf-16 byte representation...

    -- First the file into a Chilkat StringBuilder.
    -- Tell the LoadFile method that we are reading utf-16 bytes..
    DECLARE @sbXml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT

    EXEC sp_OAMethod @sbXml, 'LoadFile', @success OUT, 'qa_data/xml/myUtf16.xml', 'utf-16'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load utf-16 XML file..'
        EXEC @hr = sp_OADestroy @socket
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- Tell the socket object that we wish to send the utf-16 byte representation of the string.
    EXEC sp_OASetProperty @socket, 'StringCharset', 'utf-16'

    -- Send the string as utf-16 bytes.
    EXEC sp_OAMethod @socket, 'SendSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- Let's say we wanted to instead send the string as utf-8 bytes.
    -- Just tell the socket object...
    EXEC sp_OASetProperty @socket, 'StringCharset', 'utf-8'

    -- Send the string as utf-8 bytes.
    EXEC sp_OAMethod @socket, 'SendSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- If our XML file was utf-8, then just tell the StringBuilder that we're reading utf-8.
    -- Once the text is properly contained in the StringBuilder, we can send the contents in any other charset (windows-1252, utf-16, utf-8, etc.) 
    -- as long as it make sense (i.e. we obviously can't send Japanese text using a single-byte charset such as Windows-1252).
    EXEC sp_OAMethod @sbXml, 'LoadFile', @success OUT, 'qa_data/xml/myUtf8.xml', 'utf-8'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load utf-8 XML file..'
        EXEC @hr = sp_OADestroy @socket
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- Send as utf-16
    EXEC sp_OASetProperty @socket, 'StringCharset', 'utf-16'

    -- Send the string as utf-16 bytes.
    EXEC sp_OAMethod @socket, 'SendSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- ...
    -- ...

    EXEC @hr = sp_OADestroy @socket
    EXEC @hr = sp_OADestroy @sbXml


END
GO