(SQL Server) REST Follow Redirects

Demonstrates how to follow a 302/303 redirect response.

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
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'chilkatsoft.com', @port, @bTls, @bAutoReconnect
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- Send a POST to a URL that will respond with a 302 redirect..
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'firstName', 'John'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'lastName', 'Doe'
    DECLARE @responseText nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestFormUrlEncoded', @responseText OUT, 'POST', '/echoPost302.asp'
    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
        RETURN
      END

    DECLARE @statusCode int
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @statusCode OUT

    -- Examine the response status code
    IF @statusCode < 300
      BEGIN

        PRINT 'Not a redirect.'

        PRINT @responseText
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    IF @statusCode > 399
      BEGIN

        PRINT 'Error response: Status code = ' + @statusCode

        PRINT @responseText
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END


    PRINT 'Redirect status code = ' + @statusCode

    -- The response header will contain a Location field with the redirect URL, such as this:
    -- Location: http://www.chilkatsoft.com/echoPostFinal.asp

    -- The response status code determines how the client should behave.
    -- Here are some common possibilities:

    -- 301: Moved Permanently
    -- This and all future requests should be directed to the given URI.  (Keep the original HTTP method for the redirect.  In this case, the 
    -- original request was a POST, so we POST to the redirect URL.)

    -- 302: Found (aka Object Moved aka Moved Temporarily)
    -- This is the most popular redirect code, but also an example of industrial practice contradicting the standard. HTTP/1.0 specification (RFC 1945 ) required the client
    -- to perform a temporary redirect (the original describing phrase was Moved Temporarily), but popular browsers implemented it as a 303 See Other. Therefore, HTTP/1.1
    -- added status codes 303 and 307 to disambiguate between the two behaviors. However, the majority of Web applications and frameworks still use the 302 status code
    -- as if it were the 303.

    -- 303: See Other
    -- The response to the request can be found under another URI using a GET method. When received in response to a PUT, it should be assumed that the server has
    -- received the data and the redirect should be issued with a separate GET message.

    -- 307: Temporary Redirect
    -- In this occasion, the request should be repeated with another URI, but future requests can still use the original URI. In contrast to 303, the request method
    -- should not be changed when reissuing the original request. For instance, a POST request must be repeated using another POST request.

    EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @redirectUrl int
    EXEC sp_OAMethod @rest, 'RedirectUrl', @redirectUrl OUT
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'No Location header found for redirect.'
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- Prep for the redirect..
    EXEC sp_OAMethod @rest, 'ClearAllParts', @success OUT

    -- Disconnect and re-connect.  
    -- (This can be skipped if both the host and SSL/TLS conditions are the same.)
    EXEC sp_OAMethod @rest, 'Disconnect', @success OUT, 100
    EXEC sp_OAGetProperty @redirectUrl, 'Host', @sTmp0 OUT
    EXEC sp_OAGetProperty @redirectUrl, 'Port', @iTmp0 OUT
    EXEC sp_OAGetProperty @redirectUrl, 'Ssl', @iTmp1 OUT
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, @sTmp0, @iTmp0, @iTmp1, @bAutoReconnect
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    IF (@statusCode = 301) or (@statusCode = 307)
      BEGIN
        -- Redirect using a POST, sending the same params to the new destination
        EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'firstName', 'John'
        EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'lastName', 'Doe'
        EXEC sp_OAGetProperty @redirectUrl, 'Path', @sTmp0 OUT
        EXEC sp_OAMethod @rest, 'FullRequestFormUrlEncoded', @responseText OUT, 'POST', @sTmp0
        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
            RETURN
          END
      END

    IF (@statusCode = 302) or (@statusCode = 303)
      BEGIN
        -- Redirect using a GET, sending the query params found in the redirect URL.
        EXEC sp_OAGetProperty @redirectUrl, 'PathWithQueryParams', @sTmp0 OUT
        EXEC sp_OAMethod @rest, 'FullRequestFormUrlEncoded', @responseText OUT, 'GET', @sTmp0
        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
            RETURN
          END
      END

    -- Show the final status code and the response text.

    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    PRINT 'Final status code = ' + @iTmp0


    PRINT 'Final response text (HTML, XML, JSON, or whatever..)'

    PRINT @responseText

    EXEC @hr = sp_OADestroy @rest


END
GO