SQL Server
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 SQL Server Downloads
-- 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
-- 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
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
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
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
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
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
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
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