SQL Server Requires Chilkat v11.0.0+
SQL Server
Clickatell One API: Look up SMS delivery status
See more Clickatell Examples
Use the apiMessageId returned by the One API send example and the same integration key. The ID goes in the URL path. The SMS result contains sms.status and the timestamp of the last status change. For example, QUEUED means processing is pending and DEVICE_ACK indicates a device acknowledgment.
The response can also contain a WhatsApp result or have no identifiable channel for an unknown message. For ongoing delivery updates, configure delivery notifications in the integration. This example performs one lookup.
Chilkat SQL Server Downloads
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Assumes Chilkat is unlocked. See Global Unlock Sample.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @http, 'RequireSslCertVerify', 1
-- Supply the key for this One API integration from application configuration.
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Authorization', 'ONE_API_KEY'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Accept', 'application/json'
-- Use the provider-generated ID from an accepted One API send.
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'id', 'ONE_API_MESSAGE_ID'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://platform.clickatell.com/v1/message/{$id}', @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'No HTTP response. Check the connection and retry the lookup.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
DECLARE @reply int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @reply OUT
DECLARE @parsed int
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @parsed OUT, @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF (@success <> 1) or ((@iTmp0 <> 200))
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Clickatell returned HTTP ' + @iTmp0
IF @parsed = 1
BEGIN
EXEC sp_OAMethod @reply, 'HasMember', @iTmp0 OUT, 'error.description'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'error.description'
PRINT @sTmp0
END
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
IF @parsed <> 1
BEGIN
PRINT 'Expected a JSON response.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
-- A known SMS has a string status under the sms member.
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'sms.status'
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'whatsapp.status'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'whatsapp.status'
PRINT 'This message has WhatsApp status: ' + @sTmp0
END
ELSE
BEGIN
PRINT 'No SMS status was returned. Check the message ID and integration.'
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'status'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'status'
PRINT 'Reported status: ' + @sTmp0
END
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'sms.status'
PRINT 'SMS status: ' + @sTmp0
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'sms.timestamp'
IF @iTmp0 = 2
BEGIN
-- Read the provider timestamp as text to preserve every digit.
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'sms.timestamp'
PRINT 'Last status change (provider timestamp): ' + @sTmp0
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
END
GO