(SQL Server) REST OAuth1 with Params

Demonstrates how to use OAuth 1.0a "one legged" authentication with Woo Commerce, with URLs that use query parameters. For example: /orders?status=processing

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)
    -- Demonstrates how to do OAuth1 authentication with query parameters (for a Wordpress site using Woo Commerce).

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Prepare an OAuth 1.0 object for use with the Chilkat REST API.
    DECLARE @oauth1 int
    -- Use "Chilkat_9_5_0.OAuth1" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.OAuth1', @oauth1 OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @oauth1, 'ConsumerKey', 'WOO_COMMERCE_CONSUMER_KEY'
    EXEC sp_OASetProperty @oauth1, 'ConsumerSecret', 'WOO_COMMERCE_CONSUMER_SECRET'

    -- The signature method can be HMAC-SHA1 or HMAC-SHA256
    EXEC sp_OASetProperty @oauth1, 'SignatureMethod', 'HMAC-SHA256'

    -- The OauthUrl property will need to be updated each time a request is sent.
    -- The domain here must match the domain passed to the Connect method (below).
    -- The domain must be exact.  For example, "www.your-wordpress-site.com" vs. "your-wordpress-site.com".
    -- One might work while the other does not..
    EXEC sp_OASetProperty @oauth1, 'OauthUrl', 'http://your-wordpress-site.com/wc-api/v3/orders'

    -- We need to tell OAuth1 about our extra query parameters so they can be used
    -- in generating the OAuth1 signature.
    -- In this example, we want to add the param "status=processing"
    DECLARE @success int
    EXEC sp_OAMethod @oauth1, 'AddParam', @success OUT, 'status', 'processing'

    -- The OAuthMethod property will be set automatically when the REST request is sent.
    -- Setting it here is not actually necessary.
    EXEC sp_OASetProperty @oauth1, 'OauthMethod', 'GET'

    -- Generate an initial nonce so that Chilkat knows the desired size of the nonce.
    EXEC sp_OAMethod @oauth1, 'GenNonce', @success OUT, 32

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

    -- Tell the REST object to use the OAuth1 object for authentication.
    -- Also, indicate that the OAuth authentication parameters should be query parameters
    -- and not located within the Authorization header.
    DECLARE @bUseQueryParams int
    SELECT @bUseQueryParams = 1
    EXEC sp_OAMethod @rest, 'SetAuthOAuth1', @success OUT, @oauth1, @bUseQueryParams

    -- Make the initial connection (without sending a request yet) to the WooCommerce endpoint at your Wordpress blog.
    DECLARE @bTls int
    SELECT @bTls = 0
    DECLARE @port int
    SELECT @port = 80
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'your-wordpress-site.com', @port, @bTls, @bAutoReconnect
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @oauth1
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- Send a GET request to list orders.

    -- The extra query params must be added here.
    -- (Whatever params are added here should've also been added to the OAuth1 object.)
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'status', 'processing'

    -- When the request is sent, the OAuth1 object's Timestamp and Nonce properties are automatically
    -- regenerated.  Also, the OAuth1 object's OauthMethod property is automatically set to the HTTP method
    -- used for the request (in this case it is "GET").
    DECLARE @responseJson nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseJson OUT, 'GET', '/wc-api/v3/orders'
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @oauth1
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- When successful, the response status code will equal 200.
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        -- Examine the request/response to see what happened.

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

        EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
        PRINT 'response status text = ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT 'response header: ' + @sTmp0

        PRINT 'response body (if any): ' + @responseJson

        PRINT '---'

        EXEC sp_OAGetProperty @rest, 'LastRequestStartLine', @sTmp0 OUT
        PRINT 'LastRequestStartLine: ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
        PRINT 'LastRequestHeader: ' + @sTmp0
        EXEC @hr = sp_OADestroy @oauth1
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END


    PRINT @responseJson

    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @oauth1
    EXEC @hr = sp_OADestroy @rest


END
GO