SQL Server Requires Chilkat v11.0.0+
SQL Server
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.
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
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