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

Clickatell One API: Schedule a time-limited SMS reminder

See more Clickatell Examples

Schedule a reminder 15 minutes from now in UTC. The scheduledDeliveryTime includes its time zone and validityPeriod is expressed in minutes. Set both from the appointment and the period for which the reminder will still be useful. Use the returned message ID for a later status lookup.

Supply an international number as digits. Set the sender to the provisioned number when the integration requires it.

Clickatell API reference

Chilkat SQL Server Downloads

SQL Server
-- 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
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 nvarchar(4000)
    --  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
    --  Load the key for this One API integration from your application configuration.
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Authorization', 'ONE_API_KEY'
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Accept', 'application/json'

    DECLARE @schedule int
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @schedule OUT

    DECLARE @success int
    EXEC sp_OAMethod @schedule, 'SetFromCurrentSystemTime', @success OUT
    EXEC sp_OAMethod @schedule, 'AddSeconds', @success OUT, 900
    DECLARE @sendAt nvarchar(4000)
    EXEC sp_OAMethod @schedule, 'GetAsTimestamp', @sendAt OUT, 0
    DECLARE @body int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @body OUT

    EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].channel', 'sms'
    EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].to', 'PHONE_NUMBER'
    EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].content', 'Appointment reminder: please check in at reception.'
    EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].scheduledDeliveryTime', @sendAt
    EXEC sp_OAMethod @body, 'UpdateInt', @success OUT, 'messages[0].validityPeriod', 60
    DECLARE @sender nvarchar(4000)
    SELECT @sender = ''
    IF @sender <> ''
      BEGIN
        EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].from', @sender
      END

    PRINT 'Requested send time (UTC): ' + @sendAt

    DECLARE @resp int
    EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT

    DECLARE @sent int
    EXEC sp_OAMethod @http, 'HttpJson', @sent OUT, 'POST', 'https://platform.clickatell.com/v1/message', @body, 'application/json', @resp
    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'No HTTP response. Check the message report before resending.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @schedule
        EXEC @hr = sp_OADestroy @body
        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 (@sent <> 1) or (@iTmp0 <> 202)
      BEGIN

        EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
        PRINT 'Send returned HTTP ' + @iTmp0
        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
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @schedule
        EXEC @hr = sp_OADestroy @body
        EXEC @hr = sp_OADestroy @resp
        EXEC @hr = sp_OADestroy @reply
        RETURN
      END
    EXEC sp_OAMethod @reply, 'HasMember', @iTmp0 OUT, 'error.code'
    IF @iTmp0 = 1
      BEGIN
        EXEC sp_OAMethod @reply, 'IsNullOf', @iTmp0 OUT, 'error.code'
        IF @iTmp0 <> 1
          BEGIN

            EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'error.code'

            EXEC sp_OAMethod @reply, 'StringOf', @sTmp1 OUT, 'error.description'
            PRINT 'Submission error ' + @sTmp0 + '  ' + @sTmp1
            EXEC @hr = sp_OADestroy @http
            EXEC @hr = sp_OADestroy @schedule
            EXEC @hr = sp_OADestroy @body
            EXEC @hr = sp_OADestroy @resp
            EXEC @hr = sp_OADestroy @reply
            RETURN
          END
      END
    --  HTTP acceptance and the recipient result are separate checks.
    EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'messages[0].accepted'
    IF (@parsed <> 1) or (@iTmp0 <> 5)
      BEGIN

        PRINT 'Missing recipient result. Check the message report.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @schedule
        EXEC @hr = sp_OADestroy @body
        EXEC @hr = sp_OADestroy @resp
        EXEC @hr = sp_OADestroy @reply
        RETURN
      END
    EXEC sp_OAMethod @reply, 'BoolOf', @iTmp0 OUT, 'messages[0].accepted'
    IF @iTmp0 <> 1
      BEGIN

        EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'messages[0].error.code'

        EXEC sp_OAMethod @reply, 'StringOf', @sTmp1 OUT, 'messages[0].error.description'
        PRINT 'Message rejected: ' + @sTmp0 + '  ' + @sTmp1
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @schedule
        EXEC @hr = sp_OADestroy @body
        EXEC @hr = sp_OADestroy @resp
        EXEC @hr = sp_OADestroy @reply
        RETURN
      END
    EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'messages[0].apiMessageId'
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Accepted without a message ID. Check the message report.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @schedule
        EXEC @hr = sp_OADestroy @body
        EXEC @hr = sp_OADestroy @resp
        EXEC @hr = sp_OADestroy @reply
        RETURN
      END

    EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'messages[0].apiMessageId'
    PRINT 'Accepted. Message ID: ' + @sTmp0

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @schedule
    EXEC @hr = sp_OADestroy @body
    EXEC @hr = sp_OADestroy @resp
    EXEC @hr = sp_OADestroy @reply


END
GO