SQL Server
SQL Server
Socket Select for Reading
See more Socket/SSL/TLS Examples
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 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 @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)
DECLARE @success int
SELECT @success = 0
-- 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
EXEC @hr = sp_OACreate 'Chilkat.Socket', @socketSet OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Before we begin, create a few StringArray objects holding
-- domain names, URLs, and output filenames to be used
-- in this example.
DECLARE @saDomains int
EXEC @hr = sp_OACreate 'Chilkat.StringArray', @saDomains OUT
DECLARE @saUrls int
EXEC @hr = sp_OACreate 'Chilkat.StringArray', @saUrls OUT
DECLARE @saFilenames int
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
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