(SQL Server) Use Socket Object for the Connection

The Chilkat Socket API provides many features for establishing connections, which includes the following:

• Connect through an HTTP proxy
• Connect through a SOCKS proxy
• Connect through an SSH tunnel
• Binding to a local IP address
• Client-side Certificates (two-way SSL/TLS)
• Public Key Pinning for TLS connections
• Controlling TLS ciphers

Rather than reproducing all of the properties and methods required for these features, the REST object can use a Socket object's connection by calling the UseConnection method. This allows for an application to use the features of the Socket API to establish the connection, and then use it from the REST API.

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

    -- This example shows how to use the Chilkat socket object's connection.
    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    -- Establish the connection using the socket object:
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @maxWaitMs int
    SELECT @maxWaitMs = 5000
    DECLARE @success int
    EXEC sp_OAMethod @socket, 'Connect', @success OUT, 'www.alphavantage.co', @port, @bTls, @maxWaitMs
    IF @success <> 1
      BEGIN

        EXEC sp_OAGetProperty @socket, 'ConnectFailReason', @iTmp0 OUT
        PRINT 'Connect Failure Error Code: ' + @iTmp0
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1

    -- Use the connection:
    EXEC sp_OAMethod @rest, 'UseConnection', @success OUT, @socket, @bAutoReconnect
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    -- Get a stock quote:
    -- Sending GET request to https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=MSFT&apikey=my_api_key
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'function', 'TIME_SERIES_DAILY'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'symbol', 'AAPL'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'apikey', 'my_api_key'
    DECLARE @responseJson nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseJson OUT, 'GET', '/query'
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END


    PRINT @responseJson

    -- Sample response:
    -- {
    --     "Meta Data": {
    --         "1. Information": "Daily Prices (open, high, low, close) and Volumes",
    --         "2. Symbol": "AAPL",
    --         "3. Last Refreshed": "2019-12-06",
    --         "4. Output Size": "Compact",
    --         "5. Time Zone": "US/Eastern"
    --     },
    --     "Time Series (Daily)": {
    --         "2019-12-06": {
    --             "1. open": "267.4800",
    --             "2. high": "271.0000",
    --             "3. low": "267.3000",
    --             "4. close": "270.7100",
    --             "5. volume": "25447644"
    --         },
    --         "2019-12-05": {
    --             "1. open": "263.7900",
    --             "2. high": "265.8900",
    --             "3. low": "262.7300",
    --             "4. close": "265.5800",
    --             "5. volume": "18661343"
    --         },
    --         "2019-12-04": {
    --             "1. open": "261.0700",
    --             "2. high": "263.3100",
    --             "3. low": "260.6800",
    --             "4. close": "261.7400",
    --             "5. volume": "16810388"
    --         },
    -- ...
    --         "2019-07-18": {
    --             "1. open": "204.0000",
    --             "2. high": "205.8800",
    --             "3. low": "203.7000",
    --             "4. close": "205.6600",
    --             "5. volume": "18582161"
    --         }
    --     }
    -- }

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @socket


END
GO