Views: 8.448 views
Speak guys!
In this article, I'll share with you a solution I've been looking for a long time ago, which is how to send messages to Whatsapp contacts, groups and broadcast lists using WebRequests. I had done something similar using other tools, but never Whatsapp, which does not have an official API and is completely closed on that.
Although there are even better communication tools than Whatsapp, like Telegram itself, 100 from 100 people I know have a Whatsapp account and most don't have a Telegram, so I think this article may end up being a public utility for who wants to create any automation that involves Whatsapp.
Check out my articles on other communication tools:
- SQL Server - How to integrate database with Slack and send messages using Python and sp_execute_external_script
- SQL Server - How to Integrate Database with Slack and Send Messages Using CLR (C #)
- SQL Server - How to Integrate Database with Telegram and Send Messages Using CLR (C #)
- SQL Server - How to Integrate Database with Ryver and Send Messages Using CLR (C #)
- SQL Server - How to Send SMS Messages Using the CLR (C #) and the More Result API (PG Solutions)
- Using the Pushbullet API to send SMS messages in C #, PHP, Java, or SQL Server (with CLR)
Whatsapp Communication API
The main character of this article, the NETiZap API is what makes easy integration between the SQL Server database and the Whatsapp communicator possible, since it does not have an official API and is completely closed about it. I have tried to do some direct integrations with Whatsapp and after some testing I was banned by 24h and notified that next time my number would be banned platform forever. After that, I left these tests aside .. lol
That's why using an API is so practical and safe: The number that triggers is the API number and not your personal / corporate number.
For testing this API, you can use the free demo, but for real routines deployment, you must purchase a paid plan:
| Plano | modality | Quantity of Shipments | Value |
|---|---|---|---|
| Free Plan | Free for Demonstration, Testing or Periods. | Personalised | Free |
| Prepaid Plan | Message Pack + Support | 100 | $ 0,20 for message + $ 50,00 monthly |
| Post Paid Plan | Monthly + Support | Unlimited | R$ 200,00 |
| Postpaid Reseller Plan | Monthly + Support | Unlimited | $ 130,00 (5 line minimum) |
Rules:
- Use for spam purposes is prohibited
- Rules supervised and enforced by whatsapp
Within the API package, you can use the ConsumersAPI.exe application, which allows you to test all API methods with default parameters:
You can also test using Postman:
Documentation for this API is available. this link here. To download the API developer package, click here on this link.
If you are interested in hiring this API for your company, please contact Thiago, developer of this API, at +55 (27) 99802-2075. The number used for sending the messages is not fixed and can be changed / chosen by the contracting company.
Sending Messages to Whatsapp with OLE Automation
Click here to view content One way to do this communication from SQL Server to the API is by using the OLE Automation, which instantiates Windows objects into SQL Server. This method is already very old in SQL Server, known for some memory leaks and should be used ultimately, as SQLCLR is more secure and flexible than this method (and accentuation works), but a bit more complicated to implement. too.
Sending private messages through Whatsapp
To send a message to a specific Whatsapp contact, use the code below:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | -- ALTERAR CONFORME NECESSIDADE DECLARE @Destinatario VARCHAR(30) = 'telefone_destino', -- FORMATO: ddnumero Ex: 11988543306 @Mensagem VARCHAR(MAX) = 'Teste da API', @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2', @Usuario VARCHAR(100) = 'user', @Senha VARCHAR(100) = 'api' -- NÃO ALTERAR DAQUI PARA BAIXO DECLARE @obj INT, @Url VARCHAR(255), @resposta VARCHAR(8000), @Parametros VARCHAR(4000), @Autorizacao VARCHAR(100), @source VARBINARY(MAX) -------------------------------------------------------------------------------- -- Habilitando o OLE Automation (Se não estiver ativado) -------------------------------------------------------------------------------- DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures') IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE; END -------------------------------------------------------------------------------- -- Realizando a requisição -------------------------------------------------------------------------------- SET @Url = 'http://api.meuaplicativo.vip:13005/services/message_send?line=5527981049976&destiny=55' + @Destinatario + '&reference&text=' + @Mensagem SET @Parametros = 'App=NetiZap Consumers 1.0&AccessKey=' + @Chave SET @source = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha) SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)') EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT EXEC sys.sp_OAMethod @obj, 'open', NULL, 'POST', @Url, 'FALSE' EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded' EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization', @Autorizacao EXEC sys.sp_OAMethod @obj, 'send', NULL, @Parametros EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT EXEC sys.sp_OADestroy @obj SELECT @resposta -------------------------------------------------------------------------------- -- Desabilitando o OLE Automation (Se não estiver ativado) -------------------------------------------------------------------------------- IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE WITH OVERRIDE; END |
Result:
How it arrives on mobile:
How to retrieve the list of available groups
To list the available groups in your account, you can use the script below. To send a message to the group, simply use the same private message sending script and replace the recipient's phone number with the group id returned below.
JSON handling was done with the OPENJSON function, available from SQL Server 2016. If your version is earlier than this, you can replace it with a simple SELECT in the variable and manually identify the desired group id. If you would like to know more about JSON string handling, take a look at my article. SQL Server 2016 - Using Native JSON Support (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY).
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | -- ALTERAR CONFORME NECESSIDADE DECLARE @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2', @Usuario VARCHAR(100) = 'user', @Senha VARCHAR(100) = 'api' -- NÃO ALTERAR DAQUI PARA BAIXO DECLARE @obj INT, @Url VARCHAR(255), @resposta VARCHAR(8000), @Parametros VARCHAR(4000), @Autorizacao VARCHAR(100), @source VARBINARY(MAX) -------------------------------------------------------------------------------- -- Habilitando o OLE Automation (Se não estiver ativado) -------------------------------------------------------------------------------- DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures') IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE; END -------------------------------------------------------------------------------- -- Realizando a requisição -------------------------------------------------------------------------------- SET @Url = 'http://api.meuaplicativo.vip:13005/services/group_search?line=5527981049976&AccessKey=' + @Chave SET @source = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha) SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)') EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, 'FALSE' EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded' EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization', @Autorizacao EXEC sys.sp_OAMethod @obj, 'send', NULL, @Parametros EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT EXEC sys.sp_OADestroy @obj SELECT conteudo.id, conteudo.descricao FROM OPENJSON(@resposta, '$') WITH ( [root] NVARCHAR(MAX) AS JSON ) AS elemento CROSS APPLY OPENJSON(elemento.[root]) WITH( id VARCHAR(200), descricao VARCHAR(500) ) AS conteudo -------------------------------------------------------------------------------- -- Desabilitando o OLE Automation (Se não estiver ativado) -------------------------------------------------------------------------------- IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE WITH OVERRIDE; END |
Result:
How to send messages to a Whatsapp group
Now that I've demonstrated how to send a private message and how to list the groups, let's join the two together and send messages to one group!
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 | -- ALTERAR CONFORME NECESSIDADE DECLARE @Destino VARCHAR(50), @Mensagem VARCHAR(8000) = 'Ola! \nEsta e uma mensagem de *teste* para o ~grupo~.', @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2', @Usuario VARCHAR(100) = 'user', @Senha VARCHAR(100) = 'api' -- NÃO ALTERAR DAQUI PARA BAIXO DECLARE @obj INT, @Url_Mensagem NVARCHAR(4000), @Url_Grupo VARCHAR(4000) = 'http://api.meuaplicativo.vip:13005/services/group_search?line=5527981049976&AccessKey=' + @Chave, @resposta VARCHAR(8000), @Parametros VARCHAR(4000) = 'App=NetiZap Consumers 1.0&AccessKey=' + @Chave, @Autorizacao VARCHAR(100), @source VARBINARY(MAX) -------------------------------------------------------------------------------- -- Habilitando o OLE Automation (Se não estiver ativado) -------------------------------------------------------------------------------- DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures') IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE; END -------------------------------------------------------------------------------- -- Realizando a requisição -------------------------------------------------------------------------------- SET @source = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha) SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)') EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url_Grupo, 'FALSE' EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded' EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization', @Autorizacao EXEC sys.sp_OAMethod @obj, 'send', NULL, NULL EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT EXEC sys.sp_OADestroy @obj -- Gravo a lista dos grupos numa tabela temporária IF (OBJECT_ID('tempdb..#Grupos') IS NOT NULL) DROP TABLE #Grupos SELECT conteudo.id, conteudo.descricao INTO #Grupos FROM OPENJSON(@resposta, '$') WITH ( [root] NVARCHAR(MAX) AS JSON ) AS elemento CROSS APPLY OPENJSON(elemento.[root]) WITH( id VARCHAR(200), descricao VARCHAR(500) ) AS conteudo SET @obj = NULL SET @resposta = NULL SET @Destino = (SELECT TOP(1) id FROM #Grupos WHERE descricao = 'Teste API') SET @Url_Mensagem = 'http://api.meuaplicativo.vip:13005/services/message_send?line=5527981049976&destiny=' + @Destino + '&text=' + @Mensagem EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT EXEC sys.sp_OAMethod @obj, 'open', NULL, 'POST', @Url_Mensagem, 'FALSE' EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded; charset=utf-8' EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Charset', 'UTF-8' EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization', @Autorizacao EXEC sys.sp_OAMethod @obj, 'send', NULL, @Parametros EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT EXEC sys.sp_OADestroy @obj SELECT @resposta -------------------------------------------------------------------------------- -- Desabilitando o OLE Automation (Se não estiver ativado) -------------------------------------------------------------------------------- IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE WITH OVERRIDE; END |
How it arrives on mobile:
How to retrieve the list of available broadcasts for your account
Almost identical to the account group listing, the script below lets you list the lists of broadcasts available for submission via the API:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | -- ALTERAR CONFORME NECESSIDADE DECLARE @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2', @Usuario VARCHAR(100) = 'user', @Senha VARCHAR(100) = 'api' -- NÃO ALTERAR DAQUI PARA BAIXO DECLARE @obj INT, @Url VARCHAR(255), @resposta VARCHAR(8000), @Parametros VARCHAR(4000), @Autorizacao VARCHAR(100), @source VARBINARY(MAX) -------------------------------------------------------------------------------- -- Habilitando o OLE Automation (Se não estiver ativado) -------------------------------------------------------------------------------- DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures') IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE; END -------------------------------------------------------------------------------- -- Realizando a requisição -------------------------------------------------------------------------------- SET @Url = 'http://api.meuaplicativo.vip:13005/services/broadcast_search?line=5527981049976&AccessKey=' + @Chave SET @source = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha) SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)') EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, 'FALSE' EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded' EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization', @Autorizacao EXEC sys.sp_OAMethod @obj, 'send', NULL, @Parametros EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT EXEC sys.sp_OADestroy @obj SELECT conteudo.id, conteudo.descricao FROM OPENJSON(@resposta, '$') WITH ( [root] NVARCHAR(MAX) AS JSON ) AS elemento CROSS APPLY OPENJSON(elemento.[root]) WITH( id VARCHAR(200), descricao VARCHAR(500) ) AS conteudo -------------------------------------------------------------------------------- -- Desabilitando o OLE Automation (Se não estiver ativado) -------------------------------------------------------------------------------- IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE WITH OVERRIDE; END |
Result:
How to create SQLCLR libraries (Only if using SQLCLR)
Click here to view contentSending Messages to Whatsapp with SQLCLR
Click here to view content Now that I have demonstrated how to connect to the API, making it possible to send messages to Whatsapp with any programming language, I will demonstrate how we can do this through SQL Server. For those who already follow my blog, know that there are at least two easy ways to do this: Using SQLCLR and OLE Automation.
Sending private messages through Whatsapp
The first test to perform is by sending private messages to a contact. Its use is very simple:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | -- ALTERAR CONFORME NECESSIDADE DECLARE @Destino VARCHAR(20) = 'telefone_destino', -- FORMATO: ddnumero Ex: 11988543306 @Mensagem VARCHAR(MAX) = 'Olá! \nEsta é uma mensagem de teste.', @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2', @Usuario VARCHAR(100) = 'user', @Senha VARCHAR(100) = 'api' -- PARÂMETROS FIXOS DECLARE @Ds_Retorno_OUTPUT NVARCHAR(MAX), @Url VARCHAR(MAX) = 'http://api.meuaplicativo.vip:13005/services/message_send?line=5527981049976&destiny=' + @Destino + '&text=' + @Mensagem, @source VARBINARY(MAX) = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha), @Paramatros VARCHAR(MAX) = 'App=NetiZap Consumers 1.0&AccessKey=' + @Chave, @Autorizacao VARCHAR(500), @Cabecalhos VARCHAR(MAX) -- Gero a string base64 com o usuário e senha da API SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)') SET @Cabecalhos = 'Authorization:: ' + @Autorizacao -- Faço a chamada à API EXEC dbo.stpWs_Requisicao @Ds_Url = @Url, -- nvarchar(max) @Ds_Metodo = N'POST', -- nvarchar(max) @Ds_Parametros = @Paramatros, -- nvarchar(max) @Ds_Codificacao = N'utf-8', -- nvarchar(max) @Ds_Accept = N'', -- nvarchar(max) @Ds_ContentType = N'application/x-www-form-urlencoded', -- nvarchar(max) @Fl_Autentica_Proxy = 0, -- bit @Ds_Headers = @Cabecalhos, -- nvarchar(max) @Qt_Segundos_Timeout = 30, -- int @Ds_Retorno_OUTPUT = @Ds_Retorno_OUTPUT OUTPUT -- nvarchar(max) -- Mostro o retorno da requisição na tela SELECT @Ds_Retorno_OUTPUT |
Result:
How the message arrives on mobile:
How to retrieve the list of available groups
To list the available groups in your account, you can use the script below. To send a message to the group, simply use the same private message sending script and replace the recipient's phone number with the group id returned below.
JSON handling was done with the OPENJSON function, available from SQL Server 2016. If your version is earlier than this, you can replace it with a simple SELECT in the variable and manually identify the desired group id. If you would like to know more about JSON string handling, take a look at my article. SQL Server 2016 - Using Native JSON Support (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY).
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | -- ALTERAR CONFORME NECESSIDADE DECLARE @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2', @Usuario VARCHAR(100) = 'user', @Senha VARCHAR(100) = 'api' -- PARÂMETROS FIXOS DECLARE @Ds_Retorno_OUTPUT NVARCHAR(MAX), @Url VARCHAR(MAX) = 'http://api.meuaplicativo.vip:13005/services/group_search?line=5527981049976&AccessKey=' + @Chave, |