SQL Server
SQL Server
SQS Receive Message
See more Amazon SQS Examples
Retrieves one or more messages from an Amazon SQS queue, with a maximum limit of 10 messages, from the specified queue.See SQS ReceiveMessage or detailed information.
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
-- 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.Rest', @rest OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Connect to the Amazon AWS REST server.
-- such as https://sqs.us-west-2.amazonaws.com/
DECLARE @bTls int
SELECT @bTls = 1
DECLARE @port int
SELECT @port = 443
DECLARE @bAutoReconnect int
SELECT @bAutoReconnect = 1
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'sqs.us-west-2.amazonaws.com', @port, @bTls, @bAutoReconnect
-- Provide AWS credentials for the REST call.
DECLARE @authAws int
EXEC @hr = sp_OACreate 'Chilkat.AuthAws', @authAws OUT
EXEC sp_OASetProperty @authAws, 'AccessKey', 'AWS_ACCESS_KEY'
EXEC sp_OASetProperty @authAws, 'SecretKey', 'AWS_SECRET_KEY'
-- the region should match our URL above..
EXEC sp_OASetProperty @authAws, 'Region', 'us-west-2'
EXEC sp_OASetProperty @authAws, 'ServiceName', 'sqs'
EXEC sp_OAMethod @rest, 'SetAuthAws', @success OUT, @authAws
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'Action', 'ReceiveMessage'
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'MaxNumberOfMessages', '5'
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'VisibilityTimeout', '15'
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'AttributeName', 'All'
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'MessageAttributeName', 'All'
-- Use the actual path part of your SQS queue URL here:
DECLARE @responseXml nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseXml OUT, 'GET', '/123456789123/chilkatTest'
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 @authAws
RETURN
END
-- A successful response will have a status code equal to 200.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
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: ' + @responseXml
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @authAws
RETURN
END
-- Examine the successful XML response.
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @responseXml
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
PRINT '----'
-- A successful response looks like this:
-- <?xml version="1.0" encoding="utf-8" ?>
-- <ReceiveMessageResponse xmlns="http://queue.amazonaws.com/doc/2012-11-05/">
-- <ReceiveMessageResult>
-- <Message>
-- <Body>this is a test</Body>
-- <MD5OfBody>54b0c58c7ce9f2a8b551351102ee0938</MD5OfBody>
-- <ReceiptHandle>AQEBMyLof...UbKBTvHtEg==</ReceiptHandle>
-- <Attribute>
-- <Name>SenderId</Name>
-- <Value>957491831129</Value>
-- </Attribute>
-- <Attribute>
-- <Name>ApproximateFirstReceiveTimestamp</Name>
-- <Value>1475013283557</Value>
-- </Attribute>
-- <Attribute>
-- <Name>ApproximateReceiveCount</Name>
-- <Value>4</Value>
-- </Attribute>
-- <Attribute>
-- <Name>SentTimestamp</Name>
-- <Value>1475013283557</Value>
-- </Attribute>
-- <MessageId>52882c65-5e21-4450-9024-93a3b01e61d3</MessageId>
-- <MD5OfMessageAttributes>2ff68603f4239272bd03f543254ed040</MD5OfMessageAttributes>
-- </Message>
-- </ReceiveMessageResult>
-- <ResponseMetadata>
-- <RequestId>05ba5c92-80ee-5f87-be76-0cf67b7475d6</RequestId>
-- </ResponseMetadata>
-- </ReceiveMessageResponse>
-- Get some values from the XML:
EXEC sp_OAMethod @xml, 'ChilkatPath', @sTmp0 OUT, 'ReceiveMessageResult|Message|Body|*'
PRINT 'Body: ' + @sTmp0
EXEC sp_OAMethod @xml, 'ChilkatPath', @sTmp0 OUT, 'ReceiveMessageResult|Message|MessageId|*'
PRINT 'MessageId: ' + @sTmp0
DECLARE @unused nvarchar(4000)
EXEC sp_OAMethod @xml, 'ChilkatPath', @unused OUT, 'ReceiveMessageResult|Message[0]|Attribute|$'
DECLARE @rec int
EXEC sp_OAMethod @xml, 'FindNextRecord', @rec OUT, 'Name', 'SenderId'
EXEC sp_OAMethod @rec, 'GetChildContent', @sTmp0 OUT, 'Value'
PRINT 'SenderId: ' + @sTmp0
EXEC @hr = sp_OADestroy @rec
EXEC sp_OAMethod @xml, 'FindNextRecord', @rec OUT, 'Name', 'ApproximateFirstReceiveTimestamp'
EXEC sp_OAMethod @rec, 'GetChildContent', @sTmp0 OUT, 'Value'
PRINT 'ApproximateFirstReceiveTimestamp: ' + @sTmp0
EXEC @hr = sp_OADestroy @rec
EXEC sp_OAMethod @xml, 'FindNextRecord', @rec OUT, 'Name', 'ApproximateReceiveCount'
EXEC sp_OAMethod @rec, 'GetChildContent', @sTmp0 OUT, 'Value'
PRINT 'ApproximateReceiveCount: ' + @sTmp0
EXEC @hr = sp_OADestroy @rec
EXEC sp_OAMethod @xml, 'FindNextRecord', @rec OUT, 'Name', 'SentTimestamp'
EXEC sp_OAMethod @rec, 'GetChildContent', @sTmp0 OUT, 'Value'
PRINT 'SentTimestamp: ' + @sTmp0
EXEC @hr = sp_OADestroy @rec
EXEC sp_OAMethod @xml, 'GetRoot2', NULL
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @authAws
EXEC @hr = sp_OADestroy @xml
END
GO