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

Clickatell One API: Send personalized SMS reminders in a batch

See more Clickatell Examples

Send two appointments in one request with a different text and application ID for each. Use two distinct recipient numbers in international digits. For a US two-way integration, add its provisioned from number to each message.

Read each recipient result even when HTTP 207 reports a mixed outcome. Save the accepted provider ID against the appointment. A missing result calls for checking the message report before retrying. A client message ID helps correlate records and does not guarantee deduplication.

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 @iTmp1 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 @firstPhone nvarchar(4000)
    SELECT @firstPhone = 'FIRST_PHONE_NUMBER'
    DECLARE @secondPhone nvarchar(4000)
    SELECT @secondPhone = 'SECOND_PHONE_NUMBER'
    DECLARE @body int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @body OUT

    DECLARE @success int
    EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].channel', 'sms'
    EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].to', @firstPhone
    EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].content', 'Your appointment with Lee is at 10:00.'
    EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].clientMessageId', 'appt-1042'
    EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[1].channel', 'sms'
    EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[1].to', @secondPhone
    EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[1].content', 'Your appointment with Sam is at 14:30.'
    EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[1].clientMessageId', 'appt-1043'

    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 @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
    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp1 OUT
    IF (@sent <> 1) or ((@iTmp0 <> 202) and (@iTmp1 <> 207))
      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 @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 @body
            EXEC @hr = sp_OADestroy @resp
            EXEC @hr = sp_OADestroy @reply
            RETURN
          END
      END

    --  Look up each submitted appointment in the reply using its unique recipient.
    --  This works even if the result order changes or clientMessageId is absent.
    DECLARE @results int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @results OUT

    DECLARE @i int
    SELECT @i = 0
    WHILE (@i < 2)
      BEGIN
        EXEC sp_OASetProperty @body, 'I', @i
        DECLARE @appointment nvarchar(4000)
        EXEC sp_OAMethod @body, 'StringOf', @appointment OUT, 'messages[i].clientMessageId'
        DECLARE @phone nvarchar(4000)
        EXEC sp_OAMethod @body, 'StringOf', @phone OUT, 'messages[i].to'
        DECLARE @result int
        EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @result OUT

        DECLARE @found int
        EXEC sp_OAMethod @reply, 'FindRecord2', @found OUT, 'messages', 'to', @phone, 1, @result
        EXEC sp_OASetProperty @results, 'I', @i
        EXEC sp_OAMethod @results, 'UpdateString', @success OUT, 'appointments[i].clientMessageId', @appointment
        EXEC sp_OAMethod @result, 'JsonTypeOf', @iTmp0 OUT, 'accepted'
        IF (@found <> 1) or (@iTmp0 <> 5)
          BEGIN
            EXEC sp_OAMethod @results, 'UpdateString', @success OUT, 'appointments[i].outcome', 'unknown'

            PRINT @appointment + ' has no confirmed result.'
          END
        ELSE
          BEGIN
            EXEC sp_OAMethod @result, 'BoolOf', @iTmp0 OUT, 'accepted'
            IF @iTmp0 = 1
              BEGIN
                EXEC sp_OAMethod @results, 'UpdateString', @success OUT, 'appointments[i].outcome', 'accepted'
                EXEC sp_OAMethod @result, 'JsonTypeOf', @iTmp0 OUT, 'apiMessageId'
                IF @iTmp0 = 1
                  BEGIN
                    EXEC sp_OAMethod @result, 'StringOf', @sTmp0 OUT, 'apiMessageId'
                    EXEC sp_OAMethod @results, 'UpdateString', @success OUT, 'appointments[i].apiMessageId', @sTmp0
                  END

                PRINT @appointment + ' accepted.'
              END
            ELSE
              BEGIN
                EXEC sp_OAMethod @results, 'UpdateString', @success OUT, 'appointments[i].outcome', 'rejected'
                EXEC sp_OAMethod @result, 'StringOf', @sTmp0 OUT, 'error.description'
                EXEC sp_OAMethod @results, 'UpdateString', @success OUT, 'appointments[i].reason', @sTmp0

                EXEC sp_OAMethod @result, 'StringOf', @sTmp0 OUT, 'error.description'
                PRINT @appointment + ' rejected: ' + @sTmp0
              END
          END
        SELECT @i = @i + 1
      END
    --  This demo writes a snapshot. In an application, update the two appointment records.
    DECLARE @saved int
    EXEC sp_OAMethod @results, 'WriteFile', @saved OUT, 'appointment-send-results.json'
    IF @saved <> 1
      BEGIN

        PRINT 'Could not save the results. Reconcile the submitted messages before retrying.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @body
        EXEC @hr = sp_OADestroy @resp
        EXEC @hr = sp_OADestroy @reply
        EXEC @hr = sp_OADestroy @results
        EXEC @hr = sp_OADestroy @result
        RETURN
      END

    PRINT 'Saved appointment-send-results.json'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @body
    EXEC @hr = sp_OADestroy @resp
    EXEC @hr = sp_OADestroy @reply
    EXEC @hr = sp_OADestroy @results
    EXEC @hr = sp_OADestroy @result


END
GO