If you are working on telephone solutions, please check
out the Ozeki VoIP SIP SDK.
It can be used to create VoIP client and server software. |
|
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
Next page:
Shared database
|