SQL Server Requires Chilkat v11.0.0+
SQL Server
Clickatell One API: Send an approved WhatsApp shipment template
See more Clickatell Examples
Send a business-initiated shipment update, including outside an open customer service window. First approve a template on the WhatsApp integration with two positional body placeholders for the order number and delivery date. Supply its exact name and approved language. The keys 1 and 2 refer to those placeholders in order.
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'
-- Example approved wording: Order {{1}} is due for delivery on {{2}}.
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].template.templateName', 'shipment_update'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].template.language', 'en_US'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].template.body.parameters.1', 'ORD-1042'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].template.body.parameters.2', 'March 19'
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