Ozeki NG SMS Gateway logo OZEKI NG SMS Gateway
"The World's most reliable SMS gateway software!"
info@ozekisms.com
Tel.: 00 36 52 532 731
SMS Gateway to send SMS via GSM modem or IP SMS connection Overview SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Quick start SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Download SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Manual SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection How to buy SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection FAQ SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Contact Us SMS Gateway to send SMS via GSM modem or IP SMS connection
OZEKI NG SMS Gateway - Product Guide

MySQL addressbook Contents | SearchImport/Export addressbook

Home > Product Manual > User Guide > User Interface > Users and applications > Addressbook > Addressbook types > Shared database

SMS Gateway SMS Gateway Home

  Product Manual
  Introduction
  SMS technology
  Installation Guide
  User Guide
  Quick Start Guide
  User Interface
  Menu
  Toolbar
  Service Provider Connectivity
  Users and applications
  Export messages to Excel
  Addressbook
  Addressbook types
  MySQL addressbook
  Shared database
  Import/Export addressbook
  Separate addressbook for each user
  Ozeki SMS Client
  Sound and Alerts
  Schedule SMS sending
  Send message to multiple recipients
  Send message to contact groups
  Configuration
  Client software
  Administrator's guide
  Developers Guide
  Examples and Solutions
  Appendix
  FAQ
  Feature list
  Commercial Information
  Search
  Cookie policy
 


Automated phone calls?
Ozeki Phone System XE VoIP PBX software is an advanced PBX built for automated voice calls and 2 way SMS messaging. It has outstanding APIs for software developers. It can be used for:

Phone calls from SQL
Phone calls from HTTP
Voice and SMS applications

Callcenter developers
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.

Contact Us!
If you wish to get further information, do not hesitate to contact us!

E-mail: info@ozekisms.com

If you have a technical question, please submit a support request on-line.

Shared database

If you decide to store the contact details in database, the database will provide you faster access in case of large number of contacts. By default, every user in Ozeki NG - SMS Gateway is set to use separate databases. This page demonstrates how you can configure Ozeki NG - SMS Gateway to use the same database for each user.

After you configure the users in Ozeki NG - SMS Gateway to use a shared database, the users will use the same database to store their contact details, as demonstrated on Figure 1:


Figure 1 - Overview

Conditions
The following operations are needed to start the configuration:
1. Install Ozeki NG SMS Gateway (for more information, click on: Installation Steps)
2. Create Service Provider connection

Please note, that this solution is configured with MySQL database. To configure Ozeki NG - SMS Gateway's addressbook for MySQL, please follow the instructions of MySQL addressbook. Also, please note that each user has separate addressbook (for more information, click on Separate addressbook for each user) and this solution provides a solution to store each user's addressbook in the same database.

Also please note that a second user,(John Smith in this example) is installed for demonstrating purposes to provide an example for this solution. The second user can be replaced with any user in Ozeki NG - SMS Gateway.

Overview

To build up the shared database the following steps are taken in this example:
First, Install a second user (John Smith) in Ozeki NG SMS Gateway(for more information, click on: Standard user). After that, set the addressbook type to SQL addressbook for the each user (admin and John Smith). Finally, customize the SQL statements.


1. General

Storing contacts in database minimizes the computer load and enables a more effective work with large amount of contacts. To set up this solution, first you need to configure each user's addressbook to store their contacts in database. For this you need to do the following:

Step 1
Click on Configure and select Advanced tab on the configuration pane. In the section called Addressbook, specify the Type of Addressbook as SQL Addressbook.

Step 2
To configure the addressbook, click on Configure and on Addressbook configuration. Select the Connection Information tab and select ODBC as connection string type.

2. Customized

As the first step of the customization, you need to contact to a database with a connection string. Please note that to connect to a database, you need to customize this connection string.

General connection string
Driver={MySQL ODBC 5.1 Driver};Server=serveraddress;Database=databasename;
User=username;Password=passwd;Option=4;

Customized connection string
Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=addressbook;
User=root;Password=qwe123;Option=4;

The database in this example is stored on the local computer (Server=localhost), I use the database named 'addressbook' (Database=addressbook), I connect with the built-in 'root' user (User=root) and its password(Password=qwe123).


User 1 - admin (user)


The first user whose addresses are stored on the database is the admin(user).

MySQL create table script for admin user:
CREATE TABLE `admin_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 ,
       `comm` VARCHAR( 255 ) NOT NULL ,
       `createTime` DATE NOT NULL
);

CREATE TABLE `admin_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 `admin_membership` (
 `groupId` INT NOT NULL ,
 `contactId` INT NOT NULL
);

Click on Configure then on Addressbook configuration and select Contacts tab to customize the SQL statements as in Figure 2.


Figure 2 - Admin contacts

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

Insert
INSERT INTO `admin_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 `admin_contact` WHERE `id`='$id';

Update
UPDATE `admin_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, perform the same as on Contacts tab.(Figure 3)


Figure 3 - Admin groups

Load
SELECT `id`,`name`,`useraccount`,`subscribekeyword`,`greetingmessage`,`unsubscribekeyword`,
`byemessage`,`allowsubscription` FROM `admin_contactgroup`

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

Delete
DELETE FROM `admin_contactgroup` WHERE id = '$id';

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


On Membership tab, perform the same as on Contacts and Groups tabs. (Figure 4)


Figure 4 - Admin membership

Load
SELECT `groupId`,`contactId` FROM `admin_membership`;

Add to group
INSERT INTO `admin_membership`(`groupId`,`contactId`) VALUES ('$groupId','$contactId');

Remove from group
DELETE FROM `admin_membership` WHERE (`groupId` = '$groupId' AND `contactId` = '$contactId');



User 2 - John Smith

Other users can also store addressbooks in the same database as the admin. Take for exapmle John Smith as a user who uses the same database but different addressbook.

MySQL create table script for John Smith
CREATE TABLE `john_smith_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 ,
       `comm` VARCHAR( 255 ) NOT NULL ,
       `createTime` DATE NOT NULL
);

CREATE TABLE `john_smith_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 `john_smith_membership` (
 `groupId` INT NOT NULL ,
 `contactId` INT NOT NULL
);

Click on Configure then on Addressbook configuration and select Contacts tab to customize the SQL statements.(Figure 5)


Figure 5 - John Smith contacts

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

Insert
INSERT INTO `john_smith_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 `john_smith_contact` WHERE `id`='$id';

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


Perform the same as on Contacts tab. (Figure 6)


Figure 6 - John Smith groups

Load
SELECT `id`,`name`,`useraccount`,`subscribekeyword`,`greetingmessage`,
`unsubscribekeyword`,`byemessage`,`allowsubscription` FROM `john_smith_contactgroup`

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

Delete
DELETE FROM `john_smith_contactgroup` WHERE id = '$id';

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


Perform the same as on Contacts and Groups tab. (Figure 7)


Figure 7 - John Smith membership

Load
SELECT `groupId`,`contactId` FROM `john_smith_membership`;

Add group
INSERT INTO `john_smith_membership`(`groupId`,`contactId`) VALUES
('$groupId','$contactId');

Remove from group
DELETE FROM `john_smith_membership` WHERE (`groupId` = '$groupId' AND
`contactId` = '$contactId');


Conclusion
If you had followed the settings, you created a database, which is able to store user contact data. This way the Ozeki NG SMS Gateway handles a large number of contacts more efficiently, while the users use the same database but separate addressbooks.

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


Addressbook
Import/Export Addressbook
Separate addressbook for each user in Ozeki NG SMS Gateway
Graphical user interface
MySQL addressbook
Installation Steps
Standard user





Next page: Import/Export addressbook
Copyright © 2000 - 2017 Ozeki Informatics Ltd.
All rights reserved

Software PBX for Windows | VoIP SDK   |   Legal information   |   Privacy policy   |   Terms of use
Please, address your inquiries to info@ozekisms.com