SQL Server

Receiving a String

See more Socket/SSL/TLS Examples

Demonstrates how to receive character data on a socket connection. The code for this example immediately follows the session logs (below). You should first read throught the code w/ comments and then refer back to these session logs...

SessionLog for 1st ReceiveString method call

SPACE chars are shown as \x20. It is easy to see that the accented characters (á, é, í, ó, ú, ý) are each represented by a single byte in the received data: \xE1, \xE9, 0xED, \xF3, \xFA, and \xFD.

ReceiveString1: HTTP/1.1\x20200\x20OK\r\n
	Content-Length:\x20140\r\n
	Content-Type:\x20text/html\r\n
	Last-Modified:\x20Thu,\x2001\x20Oct\x202009\x2012:29:48\x20GMT\r\n
	Accept-Ranges:\x20bytes\r\n
	ETag:\x20\"2e66a6dd9242ca1:28b\"\r\n
	Server:\x20Microsoft-IIS/6.0\r\n
	X-Powered-By:\x20ASP.NET\r\n
	Date:\x20Thu,\x2001\x20Oct\x202009\x2013:56:53\x20GMT\r\n
	\r\n
	\r\n
	\r\n
	\r\n
	\r\n
	\r\n
	\xE1,\x20\xE9,\x20\xED,\x20\xF3,\x20\xFA,\x20\xFD\r\n
	\r\n
	

SessionLog for 2nd ReceiveString method call

It is easy to see that the accented characters (á, é, í, ó, ú, ý) are each represented by two bytes in the received data: \xC3\xA1, \xC3\xA9, etc.

ReceiveString1: HTTP/1.1\x20200\x20OK\r\n
	Content-Length:\x20141\r\n
	Content-Type:\x20text/html\r\n
	Last-Modified:\x20Thu,\x2001\x20Oct\x202009\x2012:29:48\x20GMT\r\n
	Accept-Ranges:\x20bytes\r\n
	ETag:\x20\"a6968dd9242ca1:28b\"\r\n
	Server:\x20Microsoft-IIS/6.0\r\n
	X-Powered-By:\x20ASP.NET\r\n
	Date:\x20Thu,\x2001\x20Oct\x202009\x2013:56:53\x20GMT\r\n
	\r\n
	\r\n
	\r\n
	\r\n
	\r\n
	\r\n
	\xC3\xA1,\x20\xC3\xA9,\x20\xC3\xAD,\x20\xC3\xB3,\x20\xC3\xBA,\x20\xC3\xBD\r\n
	\r\n
	

SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @socket int
    EXEC @hr = sp_OACreate 'Chilkat.Socket', @socket OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- To demonstrate receiving character (i.e. not binary) data,
    -- this example will connect to the chilkatsoft.com web server
    -- and download a very short and simple HTML page.
    -- (Note: You would typically use the Chilkat HTTP component
    -- to communicate with a web server.  We only do it here to
    -- provide an example that is runnable such that socket peer
    -- (i.e. the other end of the socket connection) is already
    -- available.

    -- So.. let's first connect the socket to the remote peer.
    DECLARE @ssl int
    SELECT @ssl = 0
    DECLARE @maxWaitMillisec int
    SELECT @maxWaitMillisec = 20000
    DECLARE @port int
    SELECT @port = 80
    EXEC sp_OAMethod @socket, 'Connect', @success OUT, 'www.chilkatsoft.com', @port, @ssl, @maxWaitMillisec
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    -- Set maximum timeouts for reading an writing (in millisec)
    EXEC sp_OASetProperty @socket, 'MaxReadIdleMs', 10000
    EXEC sp_OASetProperty @socket, 'MaxSendIdleMs', 10000

    -- Send a simple HTTP GET request to download 
    -- an HTML file on the server named "iso_chars.html"
    -- This web page contains the following characters
    -- using the iso-8859-1 encoding:
    -- á, é, í, ó, ú, ý

    -- The Chilkat Socket API has a convenience method named
    -- BuildHttpGetRequest to make it easy to send an HTTP GET
    -- for a URL:
    DECLARE @getRequest nvarchar(4000)

    EXEC sp_OAMethod @socket, 'BuildHttpGetRequest', @getRequest OUT, 'http://www.chilkatsoft.com/iso_chars.html'

    -- Send the GET request to the HTTP server:
    EXEC sp_OAMethod @socket, 'SendString', @success OUT, @getRequest
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    -- Before calling ReceiveString to get the HTTP response,
    -- let's turn on session logging so we can examine the 
    -- actual bytes received:
    EXEC sp_OASetProperty @socket, 'KeepSessionLog', 1

    -- IMPORTANT:
    -- Tell the component the character encoding of the string
    -- data to be received by setting the StringCharset property.
    -- This is extremely important.  The socket component/library must know how
    -- to interpret the received bytes.  For example,
    -- the character "á" will be represented as a single byte
    -- having the hex value 0xE1 when encoded using iso-8859-1,
    -- but will arrive as a sequence of 2 bytes having the values
    -- 0xC3 0xA1 when encoded using utf-8.
    -- Given that strings are returned as Unicode or utf-8 in most
    -- programming languages, the socket component must know how
    -- to interpret the received bytes to properly construct the 
    -- string to be returned to the caller.
    EXEC sp_OASetProperty @socket, 'StringCharset', 'iso-8859-1'

    -- OK, get the response:
    DECLARE @resp nvarchar(4000)
    EXEC sp_OAMethod @socket, 'ReceiveString', @resp OUT
    EXEC sp_OAGetProperty @socket, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    -- Examine the received string:

    PRINT @resp

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

    -- Important: ReceiveString may not receive the entire response.
    -- It simply receives whatever data is already available from 
    -- the connected peer.  To simplify the example, we're assuming
    -- the complete response is received -- and it is likely
    -- because the response is short.

    -- OK, let's examine the session log to see the actual bytes
    -- received.  (The session logging was turned on just
    -- before the call to ReceiveBytes, so we won't see the activity
    -- prior to it.)
    -- The SessionLogEncoding property controls the output format
    -- of the session log.  It defaults to "esc" for escaped ASCII, which
    -- is a good way of viewing mostly US-ASCII character data.
    -- We'll be able to see the binary bytes in between the printable
    -- ASCII characters as hex values escaped with "\x".
    EXEC sp_OAGetProperty @socket, 'SessionLog', @sTmp0 OUT
    PRINT @sTmp0

    -- (Refer to the SessionLog for the 1st ReceiveString above for the expected results.)

    -- Close the connection with the server
    -- Wait a max of 20 seconds (20000 millsec)
    EXEC sp_OAMethod @socket, 'Close', @success OUT, 20000

    -- -----------------------------------------------------------------
    -- Now we'll download a utf-8 encoded HTML page
    -- to see how the bytes received for the same characters
    -- are different.  The Socket component must know how
    -- to interpret these bytes as characters, and (as discussed
    -- before) this is the purpose of the StringCharset property.

    -- Connect to the chilkatsoft.com HTTP server again.
    EXEC sp_OAMethod @socket, 'Connect', @success OUT, 'www.chilkatsoft.com', @port, @ssl, @maxWaitMillisec
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    -- This time, get an HTML page where the characters
    -- are encoded using utf-8.
    EXEC sp_OAMethod @socket, 'BuildHttpGetRequest', @getRequest OUT, 'http://www.chilkatsoft.com/utf8_chars.html'

    -- Send the GET request to the HTTP server:
    EXEC sp_OAMethod @socket, 'SendString', @success OUT, @getRequest
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    -- Tell the component to interpret the received bytes as
    -- utf-8.  (This only applies to methods that receive strings,
    -- such as ReceiveString, ReceiveUntilMatch, etc.  
    -- Methods that receive bytes, such as ReceiveBytes, do
    -- not try to interpret the bytes received from the connected
    -- peer -- they simply return the bytes received.)
    EXEC sp_OASetProperty @socket, 'StringCharset', 'utf-8'

    -- Clear the session log so we can examine it again for the next
    -- call to ReceiveString:
    EXEC sp_OAMethod @socket, 'ClearSessionLog', NULL

    EXEC sp_OAMethod @socket, 'ReceiveString', @resp OUT
    EXEC sp_OAGetProperty @socket, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    -- The characters should display correctly:

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

    PRINT @resp

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

    -- Examine the session log.  You'll see that the actual bytes
    -- received for the characters were 2-bytes/char.  Because
    -- the StringCharset was correctly set, the socket component
    -- knew how to interpret the bytes to arrive at the appropriate
    -- characters.
    EXEC sp_OAGetProperty @socket, 'SessionLog', @sTmp0 OUT
    PRINT @sTmp0

    -- (Refer to the SessionLog for the 2nd ReceiveString above for the expected results.)

    EXEC sp_OAMethod @socket, 'Close', @success OUT, 20000

    EXEC @hr = sp_OADestroy @socket


END
GO