Integrating WAMMU/GAMMU with WEBCON BPS

How to configure and integrate the WEBCON BPS environment to send and receive SMS. Guide to the WAMMU (GAMMU for Windows) and SMSD installation on Windows.

Introduction

Today on a post on this blog I describe how to proceed step by step to be able to send SMS from the WEBCON BPS form. For this purpose, I use a 3G modem and a WAMMU package based on the MySQL database. If you want to prepare a similar solution, you will need the following components:

3G modem with active sim card
WEBCON BPS platform (WEBCON app server and a WEBCON database)
the machine where 3G modem will be physically plugged in – virtual machines may be problematic

In my case, I used a Huawei e173 3G modem. WEBCON Server and WEBCON databases are virtual machines and I plugged modem to host where my VMs are hosted.

XAMPP installation

First, you need to install the XAMPP package. There is no rocket science in it – simple download a file for your Windows version (32 or 64 bit) and install it. After installation, you should open the XAMPP control panel and start Apache and MySQL modules. Note that those modules are not starting automatically, so if you want to start these modules after system reboot you need to check these modules in under the “Config” button.

XAMP Control Panel wit Apache and MySQL modules turned on

ODBC and Linked Server configuration

At your WEBCON database server configure ODBC (Open DataBase Connectivity). To do this go to this article: Create a linked server to MySQL from SQL Server. There is a step by step guide on how to achieve a connection to MySQL database from SQL Server. You may face some trouble just like me, so below I give you some advice:

You need to allow remote connection to MySQL
The linked server was unable to begin a distributed transaction to MySQL, so I set the parameter “Enable promotion of distributed transaction for RCP” in linked server options to false

At this point, you should be able to execute queries at the MySQL server.

Example of selecting MySQL database names

3G modem installation

This should be easy – but not in my case. When I plug into front USB 3.0 port I have struggled with using my 3G modem. That’s because the modem has not been recognized properly by the system. Updating and reinstalling many versions of drivers didn’t work. In my case, I have to go to Device Manager, then expand the Universal Serial Bus Controllers and at the end, uninstall Huawei mobile connect. After that, I switched dongle to another USB 2.0 port and it started working.

WAMMU and SMSD installation

I found a non-English tutorial on YouTube on how to install GAMMU on Windows. These steps I listed below, but I recommend to watch the whole video – it is easy to understand.

Plug 3G modem
Verify COM port number of HUAWEI Mobile Connect in Device Manager
Copy and modify the gammurc file. In my case, this file was at C:\Program Files\Gammu 1.41.0\share\doc\gammu\examples\config folder. You need to copy it to GAMMU localization (C:\Program Files\Gammu 1.41.0\bin)
Copy and modify the smsdrc file. In my case, this file was at C:\Program Files\Gammu 1.41.0\share\doc\gammu\examples\config folder. You need to copy it to GAMMU localization (C:\Program Files\Gammu 1.41.0\bin)
Check that modem is working with gammu –identify command
Install GAMMU service with the command gammu-smsd -c smsdrc -i
Modify entry in Window Register using the Registry Editor
Start Apache and MySQL modules in XAMPP control panel
Create tables in the database to store your incoming and outcoming messages. Ready to use SQL script you will find in example catalog of GAMMU installation (C:\Program Files\Gammu 1.41.0\share\doc\gammu\examples\sql)
Start GAMMU service

It almost works, but after configuring everything just like in video tutorial, I got an error during the GAMMU SMSD service starting. It says “SQL service was not compiled in!”. After another hour of searching for the solution on the web, I found another way to configure WAMMU – using an ODBC driver instead. You must know that in your DSN configuration, you have to pick the database name. Otherwise, the SMSD will not start because it won’t be able to find tables.

More details about the configuration you may find with the WAMMU manual with MySQL backend.

Official WAMMU manual for OBC service using MySQL

GAMMU tables basic usage

There are 3 main tables which you should be interested in:

inbox – received SMS
outbox – SMS to sent
sentitems – SMS that was sent successfully

Below I present some basic usages of GAMMU tables:

--Send SMS
--SMS_MySQL is DSN of MySQL odbc
 EXEC( 
 'insert into outbox (DestinationNumber, TextDecoded) values(''+48666666666'', ''Test SMS'');' 
 ) at sms_mysql 
 --Check all SMS to sent
 SELECT * 
 FROM   Openquery(sms_mysql, 'select * from outbox;') 
 --Check all received SMS
 SELECT * 
 FROM   Openquery(sms_mysql, 'select * from inbox;') 
 --Check all  sent SMS
 SELECT * 
 FROM   Openquery(sms_mysql, 'select * from sentitems;')

Example of selecting all SMS from inbox table

Integration with WEBCON BPS

To send SMS from WEBCON BPS you may use the standard “Run an SQL procedure” action. If you have configured Linked Server you do not need to create dedicated Data Source and you may use the Current BPS database data connection.

Run an SQL procedure configuration to send SMS

Summary

I spend a whole day configuring this to be able to send and receive messages. But this is super-satisfying when you can do your personal SMS gateway to sending and receiving SMS via web page.

But it is worth it to use it in commercial applications? In my opinion, it depends. I am not sure how reliable this solution is I have not done any stress tests, so I would not recommend this to a big company and it’s critical systems. But for the small or medium-size company and non-critical usages using GAMMU should be fine.

Featured Image: Technology photo created by rawpixel.com – www.freepik.com