Sample code for 30+ languages & platforms
SQL Server Requires Chilkat v11.0.0+

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.

Clickatell API reference

Chilkat SQL Server Downloads

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