(SQL Server) Azure Service Bus - Peek-Lock Message (Non-Destructive Read)

See more Azure Service Bus Examples

Shows how to atomically retrieve and lock a message from a queue or subscription for processing. The message is guaranteed not to be delivered to other receivers (on the same queue or subscription only) during the lock duration specified in the queue/subscription description.

For more information, see https://docs.microsoft.com/en-us/rest/api/servicebus/peek-lock-message-non-destructive-read

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)
    -- Note: Requires Chilkat v9.5.0.65 or greater.

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

    -- Make the initial connection.
    -- A single REST object, once connected, can be used for many Azure Service Bus REST API calls.
    -- The auto-reconnect indicates that if the already-established HTTPS connection is closed,
    -- then it will be automatically re-established as needed.
    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 @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, '<yournamespace>.servicebus.windows.net', 443, 1, @bAutoReconnect
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- ----------------------------------------------------------------------------------------------
    -- The code above this comment could be placed inside a function/subroutine within the application
    -- because the connection does not need to be made for every request.  Once the connection is made
    -- the app may send many requests..
    -- ----------------------------------------------------------------------------------------------

    -- Let's load a previously computed SAS token and use it.
    -- See Azure Shared Access Signature for an example to genenerate an Azure SAS token.
    DECLARE @sbToken int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbToken OUT

    EXEC sp_OAMethod @sbToken, 'LoadFile', @success OUT, 'qa_data/tokens/serviceBusSas.txt', 'utf-8'

    -- Tell the REST object to use the Azure Shared Access Signature for authorization.
    EXEC sp_OAMethod @sbToken, 'Prepend', @success OUT, 'SharedAccessSignature '
    EXEC sp_OAMethod @sbToken, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Authorization', @sTmp0

    -- ----------------------------------------------------------------------------------------------

    -- Peek-lock a message from the subscription "feedings" in the topic "gilaMonster"
    DECLARE @topicName nvarchar(4000)
    SELECT @topicName = 'gilaMonster'
    DECLARE @subscriptionName nvarchar(4000)
    SELECT @subscriptionName = 'feedings'

    -- Build the path..
    DECLARE @sbPath int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPath OUT

    EXEC sp_OAMethod @sbPath, 'Append', @success OUT, '/'
    EXEC sp_OAMethod @sbPath, 'Append', @success OUT, @topicName
    EXEC sp_OAMethod @sbPath, 'Append', @success OUT, '/subscriptions/'
    EXEC sp_OAMethod @sbPath, 'Append', @success OUT, @subscriptionName
    EXEC sp_OAMethod @sbPath, 'Append', @success OUT, '/messages/head'

    -- If peek-locking from a queue, the path would be built like this:
    DECLARE @sbQueuePath int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbQueuePath OUT

    EXEC sp_OAMethod @sbQueuePath, 'Append', @success OUT, '/'
    EXEC sp_OAMethod @sbQueuePath, 'Append', @success OUT, 'queueName'
    EXEC sp_OAMethod @sbQueuePath, 'Append', @success OUT, '/messages/head'
    -- This example will use the sbPath..

    -- Add a timeout parameter.
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'timeout', '20'

    -- Use the POST HTTP verb to peek-lock the message.
    -- Note that the only difference between the destructive Read-and-Delete and the non-destructive Peek-Lock
    -- is that the HTTP verb.  (POST is for peek-lock, DELETE is fore read-and-delete.)
    DECLARE @sbResponseBody int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @rest, 'FullRequestNoBodySb', @success OUT, 'POST', @sTmp0, @sbResponseBody
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @sbToken
        EXEC @hr = sp_OADestroy @sbPath
        EXEC @hr = sp_OADestroy @sbQueuePath
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END


    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    PRINT 'Response Status Code = ' + @iTmp0

    -- Check for a success response.
    -- CAREFUL: This returns a 201 response instead of a 200 response.  In general, either 200 or 201 should always indicate success.
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 201
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastRequestStartLine', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @sbToken
        EXEC @hr = sp_OADestroy @sbPath
        EXEC @hr = sp_OADestroy @sbQueuePath
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    -- The content of the message is returned in the HTTP response body.
    -- The HTTP response headers contains extra information.  For example:
    DECLARE @jsonBrokerProps int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonBrokerProps OUT

    EXEC sp_OAMethod @rest, 'ResponseHdrByName', @sTmp0 OUT, 'BrokerProperties'
    EXEC sp_OAMethod @jsonBrokerProps, 'Load', @success OUT, @sTmp0
    EXEC sp_OASetProperty @jsonBrokerProps, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonBrokerProps, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- An example of the BrokerProperties JSON that is returned:

    -- 	{
    -- 	  "DeliveryCount": 2,
    -- 	  "EnqueuedSequenceNumber": 3000003,
    -- 	  "EnqueuedTimeUtc": "Wed, 07 Dec 2016 17:42:48 GMT",
    -- 	  "Label": "M2",
    -- 	  "LockToken": "0a13ee8a-be32-4458-89e0-5e1401caa6a0",
    -- 	  "LockedUntilUtc": "Wed, 07 Dec 2016 18:13:12 GMT",
    -- 	  "MessageId": "7bea513a6ddb49089e07bfbee26d85bd",
    -- 	  "SequenceNumber": 3,
    -- 	  "State": "Active",
    -- 	  "TimeToLive": 922337203685.47754
    -- 	}

    -- Also the "Location" response header contains the URI of the locked message.
    -- This can be used to ulock or delete the message.

    EXEC sp_OAMethod @rest, 'ResponseHdrByName', @sTmp0 OUT, 'Location'
    PRINT 'Location: ' + @sTmp0

    -- An example of the Location URI:
    -- https://chilkat.servicebus.windows.net/gilamonster/subscriptions/feedings/messages/3/0a13ee8a-be32-4458-89e0-5e1401caa6a0

    -- If successful, the message is the contents of the response body.
    DECLARE @message nvarchar(4000)
    EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @message OUT

    PRINT 'Received message: ' + @message


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @sbToken
    EXEC @hr = sp_OADestroy @sbPath
    EXEC @hr = sp_OADestroy @sbQueuePath
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jsonBrokerProps


END
GO