Consuming WhatsApp Business API from SQL Server: Enhancing Communication Efficiencies

4 days ago

4 min read

Integrating WhatsApp Business API with SQL Server for Automated Messaging

Integrating the WhatsApp Business API with SQL Server can enhance communication strategies, enabling businesses to send automated messages, notifications, and updates directly from their databases. This blog outlines the steps to consume the WhatsApp Business API from within SQL Server, providing a seamless integration for efficient communication.

Prerequisites

WhatsApp Business API Access: Ensure you have access to the WhatsApp Business API. You will need the API key and endpoint details.
SQL Server: A running instance of SQL Server 2016 or later.
Internet Access: The SQL Server must have internet access to connect to the WhatsApp API.

Obtaining the WhatsApp Business API Access Token

Before integrating the WhatsApp Business API with SQL Server, you need to obtain an access token. Follow these steps to get your token:

Step 1: Set Up Your WhatsApp Business API Account

Create a Facebook Business Manager Account:

Go to Facebook Business Manager and create an account if you don't already have one.

Create a WhatsApp Business Account:

In your Facebook Business Manager account, navigate to Business Settings.
Under Accounts, select WhatsApp Accounts.
Click Add to create a new WhatsApp Business Account.

Verify Your Business:

Ensure your business is verified to use the WhatsApp Business API. Verification can be done via Facebook Business Manager.

Step 2: Set Up a WhatsApp Business API Client

Choose a Hosting Option:

You can choose to host the WhatsApp Business API client on your own servers or use a third-party provider.

Get Your API Client Key and Secret:

Once you set up your API client, you'll receive a client key and secret. These credentials are used to generate an access token.

Step 3: Generate an Access Token

Authenticate with Facebook Graph API:

Use your client key and secret to authenticate with the Facebook Graph API to generate an access token. You can use tools like Postman or curl for this purpose.

Use the Following cURL Command to Generate a Token: curl -X POST \ 'https://graph.facebook.com/v14.0/oauth/access_token' \ -d 'client_id=YOUR_APP_ID' \ -d 'client_secret=YOUR_APP_SECRET' \ -d 'grant_type=client_credentials' Replace YOUR_APP_ID and YOUR_APP_SECRET with your actual app credentials. This command will return an access token.
Store the Access Token:

Save the generated access token securely. You will use this token to authenticate API requests.

Step 4: Test the Access Token

Send a Test Request: curl -X POST \ 'https://graph.facebook.com/v14.0/YOUR_PHONE_NUMBER_ID/messages' \ -H 'Authorization: Bearer YOUR_ACCESS_TOKEN' \ -H 'Content-Type: application/json' \ -d '{ "messaging_product": "whatsapp", "to": "RECIPIENT_PHONE_NUMBER", "type": "text", "text": { "body": "Hello, this is a test message from WhatsApp API!" } }' Replace YOUR_PHONE_NUMBER_ID, YOUR_ACCESS_TOKEN, and RECIPIENT_PHONE_NUMBER with the respective values.

Use the access token to send a test message to ensure it's working correctly. Here’s an example using cURL:

Step 5: Refresh the Access Token

Access tokens typically have an expiration period. Make sure to implement a mechanism to refresh the token periodically. Use the same method as above to generate a new token when the current one expires.

Consuming WhatsApp Business API from SQL Server

Once you have the access token, follow these steps to consume the WhatsApp Business API from SQL Server:

1. Configure SQL Server to Enable HTTP Requests

SQL Server doesn't natively support HTTP requests, so we need to enable sp_OACreate procedures to make HTTP calls.

Enable OLE Automation Procedures:

sp_configure 'show advanced options', 1; 
RECONFIGURE; sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;

Create a Stored Procedure for HTTP Post Requests:

CREATE PROCEDURE [dbo].[usp_HTTPPost]

    @URL NVARCHAR(MAX),

    @Headers NVARCHAR(MAX),

    @Body NVARCHAR(MAX),

    @ResponseText NVARCHAR(MAX) OUTPUT

AS

BEGIN

    DECLARE @Object AS INT;

    DECLARE @ResponseText AS VARCHAR(8000);

    DECLARE @Response AS INT;

    EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;

    EXEC sp_OAMethod @Object, 'open', NULL, 'POST', @URL, 'false';

    EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type', 'application/json';

    IF LEN(@Headers) > 0

    BEGIN

        DECLARE @Header NVARCHAR(4000);

        DECLARE @Delim NVARCHAR(1) = CHAR(10);

        DECLARE @Pos INT = 1;

        WHILE LEN(@Headers) > 0

        BEGIN

            SET @Pos = CHARINDEX(@Delim, @Headers);

            IF @Pos = 0 SET @Pos = LEN(@Headers) + 1;

            SET @Header = LEFT(@Headers, @Pos - 1);

            EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, SUBSTRING(@Header, 1, CHARINDEX(':', @Header) - 1), SUBSTRING(@Header, CHARINDEX(':', @Header) + 2, LEN(@Header));

            SET @Headers = SUBSTRING(@Headers, @Pos + 1, LEN(@Headers));

END

END

    EXEC sp_OAMethod @Object, 'send', NULL, @Body;

    EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUT;

    SET @ResponseText = @ResponseText;

    EXEC sp_OADestroy @Object;

END;

2. Sending a Message via WhatsApp Business API

To send a message, we need to make a POST request to the WhatsApp Business API endpoint with the appropriate payload.

Define the Endpoint and Payload:

DECLARE @URL NVARCHAR(MAX) = 'https://api.whatsapp.com/v1/messages';

DECLARE @Headers NVARCHAR(MAX) = 'Authorization: Bearer YOUR_ACCESS_TOKEN';

DECLARE @Body NVARCHAR(MAX) = '{

    "to": "recipient_phone_number",

    "type": "text",

    "text": {

        "body": "Hello, this is a test message from SQL Server!"

    }}';DECLARE @ResponseText NVARCHAR(MAX);

Execute the Stored Procedure: EXEC [dbo].[usp_HTTPPost] @URL, @Headers, @Body, @ResponseText OUTPUT; SELECT @ResponseText AS Response;

Error Handling and Logging

It is crucial to implement error handling and logging mechanisms to track and debug issues.

Modify the Stored Procedure for Error Handling: CREATE PROCEDURE [dbo].[usp_HTTPPost] ( @URL NVARCHAR(MAX), @Headers NVARCHAR(MAX), @Body NVARCHAR(MAX), @ResponseText NVARCHAR(MAX) OUTPUT ) AS BEGIN BEGIN TRY DECLARE @Object AS INT; DECLARE @ResponseText AS VARCHAR(8000); DECLARE @Response AS INT; EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT; EXEC sp_OAMethod @Object, 'open', NULL, 'POST', @URL, 'false'; EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type', 'application/json'; IF LEN(@Headers) > 0 BEGIN DECLARE @Header NVARCHAR(4000); DECLARE @Delim NVARCHAR(1) = CHAR(10); DECLARE @Pos INT = 1; WHILE LEN(@Headers) > 0 BEGIN SET @Pos = CHARINDEX(@Delim, @Headers); IF @Pos = 0 SET @Pos = LEN(@Headers) + 1; SET @Header = LEFT(@Headers, @Pos - 1); EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, SUBSTRING(@Header, 1, CHARINDEX(':', @Header) - 1), SUBSTRING(@Header, CHARINDEX(':', @Header) + 2, LEN(@Header)); SET @Headers = SUBSTRING(@Headers, @Pos + 1, LEN(@Headers)); END END EXEC sp_OAMethod @Object, 'send', NULL, @Body; EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUT; SET @ResponseText = @ResponseText; EXEC sp_OADestroy @Object; END TRY BEGIN CATCH SET @ResponseText = ERROR_MESSAGE(); END CATCH END;