SQL Server Requires Chilkat v11.0.0+
SQL Server
Clickatell One API: Send WhatsApp reply buttons
See more Clickatell Examples
Send an appointment question with Confirm and Reschedule buttons within 24 hours of the customer’s last WhatsApp message. This standalone example ends when Clickatell returns the send result. The response gives the outbound message ID. A customer’s eventual selection is a separate inbound event.
Chilkat SQL Server Downloads
--
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 @body int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @body OUT
DECLARE @success int
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].channel', 'whatsapp'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].to', 'PHONE_NUMBER'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].button.body', 'Can you attend your appointment tomorrow at 10:00?'
-- The visible titles can be translated. Each button has a distinct application ID.
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].button.items[0].type', 'reply'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].button.items[0].title', 'Confirm'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].button.items[0].postbackData', 'appt-1042-confirm'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].button.items[1].type', 'reply'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].button.items[1].title', 'Reschedule'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].button.items[1].postbackData', 'appt-1042-reschedule'
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
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 @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
-- 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 @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 @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 @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 @body
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
END
GO