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 to 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.

Prerequisites

• Download Ozeki SMS Gateway
• Download Sqlcmd Utility (It's free)
• Download SQL Server (It's free)
• Download WhatsApp (It's free)
• Registration Facebook developer account (It's free)

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 2) link in the left side of the Connections panel to choose to install WhatsApp.

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

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

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

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

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

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

The Default template (Figure 9) 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

We also need to specify a telephone number on the connection. Copy the Test number (Figure 10) 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.

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

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

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 13). Look at the message on your phone.

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 14). The database is now ready for SMS sending in MS SQL.

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 15) link in the right side of the User and applications panel to choose SQL messaging.

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

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

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

Go back to the General tab and configure the Connection settings (Figure 19). 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.

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

Let's 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 21). This will insert the message into the ozekimessageout table and send a message to the WhatsApp application.

From the ozekimessageout table (Figure 22) 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.

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

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

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 25) link on the left side of the Connections panel.

Open WhatsApp configuration menu at the Meta for Developers page to configure the webhook (Figure 26). 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.

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

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

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

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 30).

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 31). 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.

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

Let's 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 needs to appear in the log (Figure 33).

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 34).

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

Let's 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 36).

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.hu or register on our website: https://myozeki.com for help.