How to send WhatsApp message from MS SQL Server

This article as a detailed tutorial on how to send message from MS SQL Server. Messages go out and come in via Whatsapp. Ozeki SMS Gateway allows you te create an MS SQL Server connection and send text messages from a database using SQL code. You will also learn how to create a database and tables for storing incoming and outgoing messages. We hope you find this article informative and let's get started!

What is Ozeki SMS Gateway?

Ozeki SMS Gateway is a professional SMS gateway software that allows individuals and software applications to send and receive SMS messages, while providing full control over the SMS infrastructure, guaranteeing security.

What is WhatsApp messaging?

WhatsApp is a popular, free, and cross-platform messaging service for exchanging text, voice, and video messages, as well as documents and photos.

What is WhatsApp webhook?

WhatsApp webhook is a programming interface that allows third parties to automatically notify and reply to new messages on the WhatsApp platform.

whatsapp connection
Figure 1 - WhatsApp connection

Prerequisites

How to send WhatsApp message from SQL database (Quick steps)

To send message from Ozeki SMS Gateway into the WhatsApp:

  1. Download the Ozeki SMS Gateway
  2. Create a WhatsApp business account on Facebook developer page
  3. Launch the Ozeki SMS Gateway app
  4. Install a new WhatsApp client connection
  5. Configure Connection settings
  6. Enable the WhatsApp connection
  7. Create an SMS Test
  8. Check the message on your phone

How to send message to WhatsApp through Ozeki SMS Gateway (video tutorial)

This video shows you how to add a Whatsapp connection to the Ozeki SMS Gateway. You can view the basic settings required to send a WhatsApp message directly. You can learn how to create the Default template. Database-driven WhatsApp messaging allows you to automate the WhatsApp communication system. After the connection is established, the video shows how to compose and send the first test message, then we examine the log file to verify successful delivery.

First, log in to your Ozeki SMS Gateway, then choose the SMS Gateway Desktop icon. In the Ozeki Toolbar, choose the Add new connections (Figure 1) link in the left side of the Connections panel to choose to install WhatsApp.

add new connection
Figure 1 - Add new connection at the left side

From the IP SMS connections, service provider specific (SMS) install section, choose the WhatsApp client, and click on the Install link (Figure 2). After installation, you will see the configuration interface.

whatsapp client installation
Figure 2 - Install the WhatsApp Client

Next to the General tab, click on Advanced, and then check these two checkbox under the Log level (Figure 3) to get more information, about routing decision and message delivery events.

whatsapp event log
Figure 3 - Check these logs to get more information

Now you have to configure the connection. Copy the WhatsApp Business Account ID (Figure 4) from your Meta for Developers page. You will have to paste this into the WhatsApp general tab at Ozeki SMS Gateway.

copy account id
Figure 4 - Copy the WhatsApp Business Account ID

Go back to the General tab. Paste the WhatsApp Business Account ID into the WABA-ID field (Figure 5). This ID distinguishes all WhatsApp Business accounts.

paste account id
Figure 5 - Paste the WhatsApp Business Account ID

Copy the Temporary access token (Figure 6) from your Meta for Developers page. The temporary access token that the service can use to perform a specific task.

copy access token
Figure 6 - Copy the Temporary access token

Paste the Temporary access token from your Meta for Developers page into the Access token field (Figure 7).

paste access token
Figure 7 - Paste the Temporary access token

The Default template (Figure 8) is used to, when no conversation is open with the recipient of the message, the template is sent instead of the message. Then, when the conversation is opened, the original message is automatically sent. You must first enter the recipient's telephone number in international format. Secondly, you must specify the message type, which in this case is a template. Thirdly, this tag specifies the name of the template to be used. Finally, this tag specifies the language code of the template.
Fill in these fields correctly, as follows:
Tag 1: wa.to : $recipient
Tag 2: wa.type : template
Tag 3: wa.template.name : hello_world
Tag 4: wa.template.language.code : en_US

whatsapp default template
Figure 8 - Configure the default template

We also need to specify a telephone number on the connection. Copy the Test number (Figure 9) from the Meta for Developers page. You can send free messages with the provided test phone number. You can use your own phone number which is subject to limits and pricing.

copy test number
Figure 9 - Copy the Test number

Paste into the Telephone number field, then click on the Ok button, highlighted in blue (Figure 10). After pressing the button and then you have to enable the connection.

paste test number
Figure 10 - Paste the Test number for testing purposes

To establish a connection, you have to switch the switch button on the left control table, and it should be green (Figure 11). If the connection is successful, you can see it in the logs at the Events tab, and you can use the service.

whatsapp connected
Figure 11 - Successful connection

In the right panel of the Ozeki SMS Gateway, select the Test tab and create an SMS message by entering your details. Enter the phone number of the recipients and click Send, to send the automatically generated test message. If this is successful, a green tick will appear below the tab, indicating that the connection has been established (Figure 12). Look at the message on your phone.

test send
Figure 12 - Send a successful test message to the phone number

How to send message from MS SQL (Simple guidelines)

To send message from MS SQL:

  1. Create database and tables
  2. Launch the Ozeki SMS Gateway app
  3. Add new SQL messaging application
  4. Install MS SQL Server
  5. Configure Connection settings
  6. Enable MS SQL Server connection
  7. Insert a message into the database
  8. Check the message in the Sent folder

How to send message from MS SQL Server (video tutorial)

This video shows you how to create databases. How to create MS SQL Server connection on Ozeki SMS Gateway. The video will help you how to configure the connection. It shows how to send a message directly and view sent and received messages. Once the connection is established, the video will show you how to write and send the first test message and then examine the log file to verify successful delivery.

Create a table definition for messages

Before you can start sending SMS using SQL, you must first create a database and tables. You can do this by entering certain lines of code on the command line. The CREATE DATABASE command will add the new database itself. The two CREATE TABLE commands add two tables. One to store incoming messages and one to store outgoing messages. Finally, you can use the sp_addLogin command to create a username and password to authenticate the database.

CREATE DATABASE ozekidb
GO
 
USE ozekidb
GO
 
CREATE TABLE ozekimessagein (
 id int IDENTITY (1,1),
 sender varchar(255),
 receiver varchar(255),
 msg nvarchar(160),
 senttime varchar(100),
 receivedtime varchar(100),
 operator varchar(30),
 msgtype varchar(30),
 reference varchar(30),
);
 
CREATE TABLE ozekimessageout (
 id int IDENTITY (1,1),
 sender varchar(255),
 receiver varchar(255),
 msg nvarchar(160),
 senttime varchar(100),
 receivedtime varchar(100),
 operator varchar(100),
 msgtype varchar(30),
 reference varchar(30),
 status varchar(30),
 errormsg varchar(250)
);
 
GO
 
sp_addLogin 'ozekiuser', 'ozekipass'
GO
 
sp_addsrvrolemember 'ozekiuser', 'sysadmin'
GO

Create database and tables

To start sending SMS using MS SQL, you first need to create a database and tables. This is done via the command line. Open the command line and enter the above code sequence. The CREATE DATABASE command creates the database itself. Next, the tables ozekimessagein and ozekimessageout are created using the CREATE TABLE command. Finally, we add the username and password with sp_addLogin (Figure 13). The database is now ready for SMS sending in MS SQL.

database and tables create
Figure 13 - Create the database and tables

First, log in to your Ozeki SMS Gateway, then choose the SMS Gateway Desktop icon. In the Ozeki Toolbar, choose the Add new user/application (Figure 14) link in the right side of the User and applications panel to choose SQL messaging.

new sql connection
Figure 14 - Add new connection at the right side

From the Application interfaces install section, choose the SQL messaging, and click on the Install (Figure 15) link. After this, you can then see the SQL services available for installation.

sql messaging
Figure 15 - Select SQL messaging

Find MS SQL Server under SQL Messaging and click install (Figure 16). After installation, you will see the SQL configuration interface.

sql server
Figure 16 - Choose MS SQL Server to install this service

Next to the Send tab, click on Advanced, and then check the Attach log to every message checkbox under the Log level (Figure 17). Enable this checkbox to log delivery events into the event log of each message.

sql log
Figure 17 - Check this log to get more information

Go back to the General tab and configure the Connection settings (Figure 18). With these settings you can connect to your own sql database. The tables in the database will be used later.
Server: local IP address
Port: port number
Database: database name
UserID: username
Password: password
If everything is filled in correctly, press the ok button.

sql configure
Figure 18 - Configure SQL for successful connection

To establish a connection, you have to switch the switch button on the left control table, and it should be green (Figure 19). If the connection is successful, you can see it in the logs at the Events tab, and you can use the service.

sql server connected
Figure 19 - Successful connection

Lets insert a message into the database table. First, go to the SQL tab of your connection. Select the INSERT INTO from the dropdown menu. Change the phone number and click on the Execute button to run the command (Figure 20). This will insert the message into the ozekimessageout table and send a message to the WhatsApp application.

insert message
Figure 20 - Select INSERT INTO option to send a test

From the ozekimessageout table (Figure 21) you can query the messages sent by selecting the SELECT * option. If you run this query, you can see the message sent in the table.

message out
Figure 21 - View sent messages in the database

On the left side in the control panel, under the Folders select the Sent link and you can see the messages are sent (Figure 22).

message sent
Figure 22 - View sent messages in the folders

Click on the Events tab, and you can see every log line (Figure 23).

sql log
Figure 23 - You can see the sent messages in the log files

How to receive WhatsApp message into SQL database (Simple steps)

To receive WhatsApp message into SQL database:

  1. Launch the Ozeki SMS Gateway app
  2. Configure the WhatsApp webhook
  3. Check the message in the SQL database

How to receive WhatsApp message into MS SQL (Video tutorial)

This video shows you how to receive messages from WhatsApp. How to connect your WhatsApp webhook to the Ozeki SMS Gateway. How to configure the webhook, what parameters to set. After successful connection you can see the incoming messages in the log file and also in the database.

First log in to the Ozeki SMS Gateway, then select the SMS Gateway Desktop icon. In the Ozeki toolbar, select the existing WhatsApp client (Figure 24) link on the left side of the Connections panel.

whatsapp client
Figure 24 - Click WhatsApp client at the left panel

Open WhatsApp configuration menu at the Meta for Developers page to configure the webhook (Figure 25). Click on Edit.
The webhook need to be set, because it allows the Ozeki SMS Gateway to send an alert message to Whatsapp when it receives a message from a mobile phone, or when the status of a Whatsapp message sent to a mobile phone changes.

webhook
Figure 25 - Webhook configuration settings

On the WhatsApp client page, we need the Configure tab. Scroll down to the Published callback URL section and copy it (Figure 26), it's a template. This will help you to just copy and paste.

webhook template
Figure 26 - Copy the published callback URL template

Paste the template into the Callback URL field (Figure 27).

callback url
Figure 27 - Paste the template into the webhook Callback URL

Copy the Connection name and the Verify token (Figure 28). The connection name specifies the name of the connection, the verfiy token is responsible for security.

connection name and token
Figure 28 - Copy the Connection name and Verify Token

Paste the Connection name at the end of the template. Replace the [public-ip]:[port] into your public IP with your forwarded port.
Paste the Verify token into the Verify token field on the Meta for Developers page. If you've filled everything in, click on Verify and save button.
Then press the Manage button (Figure 29).

webhook configuration
Figure 29 - All fields filled in at the Webhook configuration

You have to select the Webhook field, so you will need to select the messages on the page. Check this box and click Done (Figure 30). Selecting the message type in the WhatsApp webhook setting indicates that the webhook will respond to messages. It means that the webhook will work when messages are received and sent.

webhook fields message
Figure 30 - Select the message type in the WhatsApp webhook settings

It should look like this (Figure 31), if we did everything right. And you can test the successful connection in the Ozeki SMS Gateway.

success webhook configuration
Figure 31 - Successfully webhook configuration

Lets go back to the Ozeki SMS Gateway. In the WhatsApp client, click on Events to see the log. Send a message in the WhatsApp application, the message need to appear in the log (Figure 32).

message received in log
Figure 32 - Send a test message and look at the log file

On the left side in the control panel, under the Folders click on the Forwarded link, and choose the Forward accepted tab. Where you can see the messages (Figure 33).

forwarded message
Figure 33 - You can see the message in the forwarded folder

Go back to the Main page and select your existing SQL Server link at the right side of the User and applications (Figure 34) panel to see the incoming message here too.

ms sql server
Figure 34 - Select SQL Server to see the messages

Lets query a message from the database. Go to the SQL tab of your connection.Select the SELECT * from the dropdown menu and you can see every received message (Figure 35).

sql message in
Figure 35 - You can query the inbox with SQL

Summary

If you follow the steps described in this article, you will be able to set up a WhatsApp SMS connection without any problems. This will allow you to send and receive WhatsApp messages through the Ozeki SMS gateway, which can be a great way to communicate with clients or customers.

In this article we have shown you how to set up a WhatsApp SMS connection. We explained the process in detail, from creating a WhatsApp application in Facebook Developer Portal to setting up a connection in Ozeki SMS Gateway.

We have provided screenshots and instructions for each step so you can easily follow the steps.

Here are some more useful tips for setting up your WhatsApp SMS connection:

  • Make sure you have a valid WhatsApp Business account ID and a temporary access token.
  • Make sure the phone number you add to your WhatsApp Business App is the same phone number you use in Ozeki SMS Gateway.
  • Test the connection by sending a test message to a recipient's phone number.

If you have further questions, please email info@ozeki.com or register on our website: https://myozeki.com for help.

More information