MySQL addressbook type in Ozeki NG SMS Gateway

This page demonstrates the MySQL addressbook type available in Ozeki NG SMS Gateway. Please read this description page and follow the screen shots to learn the various types and their functions.

In this example I login as user admin to Ozeki NG SMS Gateway. To be able to configure the addressbook, click on the respective user (Figure 1).

Figure 1 - Click on the user

Click on Configure and select Advanced tab on the configuration pane (Figure 2).

Figure 2 - Select Advanced tab

In the section called Addressbook, specify the Type of Addressbook.

SQL Addressbook

You can select SQL Addressbook type on Advanced tab (Figure 3). In this case contacts will be stored and uploaded from an SQL database.

Figure 3 - SQL addressbook

To configure the addressbook, click on Configure and on Addressbook configuration. On Connection information tab you can specify the Connection string with which the system connects to the database (Figure 4).

Figure 4 - Connection string

MySQL also requires Odbc as Connection string type, which can also be selected on Connection information tab (Figure 5).

Figure 5 - Connection string type

First you need to create database tables:

MySQL

You can connect to MySQL with this connection string:
Connection string

Driver={MySQL ODBC 5.1 Driver};Server=IPaddress;Database=DBname;User=UserName;Password=PassWord;Option=4;

Create table script for MySQL

CREATE TABLE  `contact` (
       `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
       `name` VARCHAR( 50 ) NOT NULL ,
       `mobile` VARCHAR( 255 ) NOT NULL ,
       `telephone` VARCHAR( 255 ) NOT NULL ,
       `fax` VARCHAR( 255 ) NOT NULL ,
       `email` VARCHAR( 255 ) NOT NULL ,
       `im` VARCHAR( 255 ) NOT NULL ,
       `other` VARCHAR( 255 ) NOT NULL ,
       `comment` VARCHAR( 255 ) NOT NULL ,
       `createTime` DATE NOT NULL
);

CREATE TABLE  `contactGroup` (
       `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
       `name` VARCHAR( 50 ) NOT NULL ,
       `useraccount` VARCHAR( 50 ) NOT NULL ,
       `subscribekeyword` VARCHAR( 50 ) NOT NULL ,
       `greetingmessage` VARCHAR( 50 ) NOT NULL ,
       `unsubscribekeyword` VARCHAR( 50 ) NOT NULL ,
       `byemessage` VARCHAR( 50 ) NOT NULL ,
       `allowsubscription` VARCHAR( 5 ) NOT NULL
);

CREATE TABLE  `membership` (
 `groupId` INT NOT NULL ,
 `contactId` INT NOT NULL
);

On Contacts tab you can specify the SQL statements of the contacts: for Load, Create, Delete, and Update fields (Figure 6).

Figure 6 - Contacts tab

Replace all statements in Contacts tab to the follows:

Load

SELECT `id`,`name`,`mobile`,`telephone`,`fax`,`email`,`im`,`other`,`comm`,`createTime` FROM `contact`;

Insert

INSERT INTO `contact`(`name`,`mobile`,`telephone`,`fax`,`email`,`im`,`other`,`comm`,`createTime`)VALUES ('$name','$mobile','$telephone','$fax','$email','$im','$other','$comment','$createTime');SELECT LAST_INSERT_ID( );

Delete

DELETE FROM `contact` WHERE `id`='$id';

Update

UPDATE `contact` SET `id` = '$id',`name`='$name',`mobile`='$mobile',`telephone`='$telephone',`fax`='$fax', `email`='$email',`im`='$im',`other`='$other',`comm`='$comment' WHERE `id` = '$id';

On Groups tab you can define the SQL statements of groups for Load, Create, Delete, and Update fields (Figure 7).

Figure 7 - Groups tab

Replace all statements in Groups tab to the follows:

Load

SELECT `id`,`name`,`useraccount`,`subscribekeyword`,`greetingmessage`,`unsubscribekeyword`, `byemessage`,`allowsubscription` FROM `contactgroup`;

Create

INSERT INTO `contactgroup`(`name`,`useraccount`,`subscribekeyword`,`greetingmessage`, `unsubscribekeyword`,`byemessage`,`allowsubscription`) VALUES ('$name', '$useraccount', '$subscribekeyword', '$greetingmessage', '$unsubscribekeyword', '$byemessage', '$allowsubscription');SELECT LAST_INSERT_ID();

Delete

DELETE FROM `contactgroup` WHERE id = '$id';

Update

UPDATE `contactgroup` SET `name`='$name',`useraccount`='$useraccount', `subscribekeyword`='$subscribekeyword',`greetingmessage`='$greetingmessage', `unsubscribekeyword` ='$unsubscribekeyword', `byemessage` = '$byemessage', `allowsubscription` = '$allowsubscription' WHERE `id` = '$id';

SQL statements can also be specified for members on Membership tab: Load, Add to group, and Remove from group (Figure 8).

Figure 8 - Membership tab

Replace all statements in Membership tab to the follows:

Load

SELECT `groupId`,`contactId` FROM `membership`;

Add to group

INSERT INTO `membership`(`groupId`,`contactId`) VALUES ('$groupId','$contactId');

Remove from group

DELETE FROM `membership` WHERE (`groupId` = '$groupId' AND `contactId` = '$contactId');

Dig deeper!
People who read this also read...

Shared database
Addressbook
Import/Export Addressbook
Separate addressbook for each user in Ozeki NG SMS Gateway
Graphical user interface

More information

Next page: Shared database