SQL Server

Use Socket Object for the Connection

See more REST Examples

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.

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.

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

    DECLARE @socket int
    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
    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