(SQL Server) Socket Select for Reading

Demonstrates how the Chilkat socket object can become a "socket set" that contains other connected socket objects, and this can be used to "select" on multiple sockets for reading. The SelectForReading method waits until one or more sockets in the set have incoming data ready and available to read.

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
    DECLARE @iTmp0 int
    DECLARE @iTmp1 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This will be the socket object that will contain other 
    -- connected sockets.
    DECLARE @socketSet int
    -- Use "Chilkat_9_5_0.Socket" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Socket', @socketSet OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Before we begin, create a few StringArray objects holding
    -- domain names, URLs, and output filenames to be used
    -- in this example.
    DECLARE @saDomains int
    -- Use "Chilkat_9_5_0.StringArray" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringArray', @saDomains OUT

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

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

    EXEC sp_OAMethod @saDomains, 'Append', @success OUT, 'www.chilkatsoft.com'
    EXEC sp_OAMethod @saDomains, 'Append', @success OUT, 'www.cknotes.com'
    EXEC sp_OAMethod @saDomains, 'Append', @success OUT, 'www.google.com'
    EXEC sp_OAMethod @saDomains, 'Append', @success OUT, 'www.example-code.com'
    EXEC sp_OAMethod @saDomains, 'Append', @success OUT, 'www.yahoo.com'

    EXEC sp_OAMethod @saUrls, 'Append', @success OUT, 'http://www.chilkatsoft.com/'
    EXEC sp_OAMethod @saUrls, 'Append', @success OUT, 'http://www.cknotes.com/'
    EXEC sp_OAMethod @saUrls, 'Append', @success OUT, 'http://www.google.com/'
    EXEC sp_OAMethod @saUrls, 'Append', @success OUT, 'http://www.example-code.com/'
    EXEC sp_OAMethod @saUrls, 'Append', @success OUT, 'http://www.yahoo.com/'

    EXEC sp_OAMethod @saFilenames, 'Append', @success OUT, 'chilkatsoft.out'
    EXEC sp_OAMethod @saFilenames, 'Append', @success OUT, 'cknotes.out'
    EXEC sp_OAMethod @saFilenames, 'Append', @success OUT, 'google.out'
    EXEC sp_OAMethod @saFilenames, 'Append', @success OUT, 'example-code.out'
    EXEC sp_OAMethod @saFilenames, 'Append', @success OUT, 'yahoo.out'

    DECLARE @numConnections int
    SELECT @numConnections = 5

    -- This example will connect to 5 different web servers,
    -- send an HTTP request to each, and read the HTTP response
    -- form each.  It will call the SelectForReading method to wait
    -- until data arrives from any of the connected sockets.

    -- NOTE: As you'll see later, communicating with HTTP servers
    -- is better left to the Chilkat HTTP component/library because
    -- of many HTTP protocol issues such as redirects, GZIP responses,
    -- chunked responses, etc.  We're only communicating with
    -- HTTP servers here as a convenient way of demonstrating
    -- the technique of waiting for data to arrive from a set of 
    -- connected sockets.

    -- First, connect to each of the web servers:
    DECLARE @i int

    DECLARE @useSsl int
    SELECT @useSsl = 0
    DECLARE @maxWaitMs int
    SELECT @maxWaitMs = 5000
    DECLARE @port int
    SELECT @port = 80
    DECLARE @domain nvarchar(4000)

    DECLARE @getReq nvarchar(4000)

    SELECT @i = 0
    WHILE @i <= @numConnections - 1
      BEGIN
        DECLARE @sock int
        -- Use "Chilkat_9_5_0.Socket" for versions of Chilkat < 10.0.0
        EXEC @hr = sp_OACreate 'Chilkat.Socket', @sock OUT

        EXEC sp_OAMethod @saDomains, 'GetString', @domain OUT, @i

        EXEC sp_OAMethod @sock, 'Connect', @success OUT, @domain, @port, @useSsl, @maxWaitMs
        IF @success <> 1
          BEGIN
            EXEC sp_OAGetProperty @sock, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @socketSet
            EXEC @hr = sp_OADestroy @saDomains
            EXEC @hr = sp_OADestroy @saUrls
            EXEC @hr = sp_OADestroy @saFilenames
            EXEC @hr = sp_OADestroy @sock
            RETURN
          END

        -- Save a filename in the socket object's UserData.  We'll use this
        -- later when saving the data read in the HTTP response.
        EXEC sp_OAMethod @saFilenames, 'GetString', @sTmp0 OUT, @i
        EXEC sp_OASetProperty @sock, 'UserData', @sTmp0

        -- Display the remote IP address of the connected socket:

        EXEC sp_OAGetProperty @sock, 'RemoteIpAddress', @sTmp0 OUT
        PRINT 'connected to ' + @sTmp0

        -- Build the HTTP GET request to be sent to the web server.
        EXEC sp_OAMethod @saUrls, 'GetString', @sTmp0 OUT, @i
        EXEC sp_OAMethod @sock, 'BuildHttpGetRequest', @getReq OUT, @sTmp0

        -- Send the HTTP GET request to the web server.
        EXEC sp_OAMethod @sock, 'SendString', @success OUT, @getReq
        IF @success <> 1
          BEGIN
            EXEC sp_OAGetProperty @sock, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @socketSet
            EXEC @hr = sp_OADestroy @saDomains
            EXEC @hr = sp_OADestroy @saUrls
            EXEC @hr = sp_OADestroy @saFilenames
            EXEC @hr = sp_OADestroy @sock
            RETURN
          END

        -- Tell the socketSet object to take ownership of the connected socket.
        -- This adds the connection to the internal set of sockets contained
        -- within socketSet.   
        EXEC sp_OAMethod @socketSet, 'TakeSocket', @success OUT, @sock
        IF @success <> 1
          BEGIN
            -- The TakeSocket method shouldn't fail.  The only reason it might
            -- is if we try to give it an unconnected socket -- but we already 
            -- know it's connected at this point..
            EXEC sp_OAGetProperty @socketSet, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @socketSet
            EXEC @hr = sp_OADestroy @saDomains
            EXEC @hr = sp_OADestroy @saUrls
            EXEC @hr = sp_OADestroy @saFilenames
            EXEC @hr = sp_OADestroy @sock
            RETURN
          END

        SELECT @i = @i + 1
      END

    -- At this point we have 5 TCP/IP connections contained within the socketSet object.
    -- Each of the web servers have received a GET request and will be
    -- sending a response.  We now want to write a loop that waits for data
    -- to arrive from any of the 5 connections, reads incoming data, and
    -- saves the data to files.

    -- We'll use the StartTiming method and ElapsedSeconds property
    -- to wait a maximum of 10 seconds.  The reason we do this is because
    -- different web servers will behave differently w.r.t. idle connections.
    -- Some web servers close the connections quickly while others do not.
    -- The socket component is not "HTTP aware", meaning that it is not parsing
    -- the HTTP response and therefore does not know when it has received
    -- the full response.  Because of this, we might receive the full response
    -- and then wait a long time before the server decides to close its side of
    -- the connection.  
    -- The ElapsedSeconds property will return the number of seconds that
    -- have elapsed since the last call to StartTiming (on the same object instance).
    EXEC sp_OAMethod @socketSet, 'StartTiming', NULL

    EXEC sp_OAGetProperty @socketSet, 'NumSocketsInSet', @iTmp0 OUT
    EXEC sp_OAGetProperty @socketSet, 'ElapsedSeconds', @iTmp1 OUT
    WHILE ((@iTmp0 > 0) and (@iTmp1 < 10))
      BEGIN

        SELECT @maxWaitMs = 300

        DECLARE @numReady int

        -- Wait for data to arrive on any of the sockets.  Waits a maximum
        -- of 300 milliseconds.  If maxWaitMs = 0, then it is effectively a poll.
        -- If no sockets have data available for reading, then a value of 0 is 
        -- returned.  A value of -1 indicates an error condition.
        -- Note: when the remote peer (in this case the web server) disconnects,
        -- the socket will appear as if it has data available.  A "ready" socket
        -- is one where either data is available for reading or the socket has
        -- become disconnected.
        EXEC sp_OAMethod @socketSet, 'SelectForReading', @numReady OUT, @maxWaitMs
        IF @numReady < 0
          BEGIN
            EXEC sp_OAGetProperty @socketSet, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @socketSet
            EXEC @hr = sp_OADestroy @saDomains
            EXEC @hr = sp_OADestroy @saUrls
            EXEC @hr = sp_OADestroy @saFilenames
            EXEC @hr = sp_OADestroy @sock
            RETURN
          END

        -- Consume data from each of the ready sockets, and show those
        -- that become disconnected.   
        SELECT @i = 0
        WHILE @i <= @numReady - 1
          BEGIN

            -- To make method calls on the "ready" socket, or to get/set property
            -- values, set the SelectorReadIndex.  
            EXEC sp_OASetProperty @socketSet, 'SelectorReadIndex', @i

            -- Print information that identifies the socket that is ready for reading:

            EXEC sp_OAGetProperty @socketSet, 'RemoteIpAddress', @sTmp0 OUT

            EXEC sp_OAGetProperty @socketSet, 'UserData', @sTmp1 OUT
            PRINT 'ready: IP address = ' + @sTmp0 + ' filename = ' + @sTmp1

            -- Read the selected socket and send the data directly to a file (appending
            -- to the file).
            EXEC sp_OAGetProperty @socketSet, 'UserData', @sTmp0 OUT
            EXEC sp_OAMethod @socketSet, 'ReceiveBytesToFile', @success OUT, @sTmp0

            -- The success/failure of a socket read should normally be checked.
            -- In this case it is not necessary.  If the socket became ready for
            -- reading because the remote peer disconnected and there is no
            -- data available, then nothing will be read.  The socketSet object
            -- will automatically remove a disconnected socket from its internal set of sockets
            -- the next time SelectForReading or SelectForWriting is called.
            -- We can check to see if the socket is still connected by examining
            -- the IsConnected property:
            EXEC sp_OAGetProperty @socketSet, 'IsConnected', @iTmp0 OUT
            IF @iTmp0 <> 1
              BEGIN

                EXEC sp_OAGetProperty @socketSet, 'UserData', @sTmp0 OUT
                PRINT 'disconnected: ' + @sTmp0
              END

            SELECT @i = @i + 1
          END

        -- Set the SelectorReadIndex = -1 so that method calls
        -- and property gets/sets are not routed to one of the socketSet's
        -- contained sockets.  This is necessary because we want to
        -- check the number of elapsed seconds from when we called
        -- StartTiming.  
        EXEC sp_OASetProperty @socketSet, 'SelectorReadIndex', -1

      END


    PRINT 'Finished.'

    -- Some final notes:
    -- If you examine the output files, you'll see that the HTTP protocol is a little
    -- more complex than you may have originally thought.
    -- 1) HTTP responses are MIME -- there is a MIME header followed by a MIME body.
    --     In most cases, the MIME body is the HTML document itself.
    -- 2) Some HTTP responses return gzip compressed data (which is binary).  It must
    --      be decompressed to get the HTML.
    -- 3) Some HTTP responses arrive in "chunked" form.  You'll see lines containing nothing but
    --      a hexidecimal number indicating the size of the chunk to follow.  It ends with
    --      a 0-sized chunk.
    -- 4) You'll find that some HTTP responses might be redirects to other URL's (such as a 302 redirect).

    -- For the above reasons, and many more, you would typically use the Chilkat HTTP component
    -- to handle the complexities of HTTP.  

    -- The reason HTTP was chosen for this example is because:
    -- 1) It is easy to establish multiple connections with existing servers that are not typically not blocked by firewalls.
    -- 2) It is easy to send a request and receive a response.
    -- 3) There is variability  in the amount of time before a server decides to disconnect from an idle client

    EXEC @hr = sp_OADestroy @socketSet
    EXEC @hr = sp_OADestroy @saDomains
    EXEC @hr = sp_OADestroy @saUrls
    EXEC @hr = sp_OADestroy @saFilenames
    EXEC @hr = sp_OADestroy @sock


END
GO