Previous topicNext topic
Help > How to... >
External database connection

External database connection be used to integrate SMS feature to third-party application.

1.Configure external database connection

Create SQL Server connnection:

App support Microsoft access,Microsoft sql server (2005 or hinger) and Mysql.

1.click button 'Add' to add database connection

2.Select database type

3.Input exists database name or create a new database then click button 'Test connection', if connect database success, you may forward next step,

4.If the database and table already exists , please checked 'select database table from specific database ', otherwise select 'create database table automatically' , you may click 'Script' to view the script,  when you click button  'Create' app will use this script to create database and table.

5. Finally named this connection, click button 'finish', app will scan this table with interval, any new records be inserted into table 'SmsDlv_Outbox', app will get it and put it to outbox. any incoming message will be save to table 'SMSDlv_Inbox'.

Create MySQL  connnection:

1.click button 'Add' to add database connection

Note: Our app is develop via .Net, so please install components 'Mysql connector/Net' via https://dev.mysql.com/downloads/connector/net/  

2.Input correct Mysql username and password and input an exists database name which you want put data,  if you have not database on Mysql, you need create one by Mysql admin panel.

3.Create table for database

you also may run  script via mysql admin panel to create table, if the table already exists just need click button 'Test connection' to verify connection.

4. Name the connecction.

5.Click button 'Finish', you may get the Mysql connection on db connection list.

2.Create table script

You may create database and table with admin user then just configure app to connect it. 

1.Sql server

CREATE TABLE [SMSDlv_Inbox](
[ID] [nvarchar](50) NOT NULL  PRIMARY KEY,
[FromNo] [nvarchar](50) NULL,
[Message] [nvarchar](max) NULL,
[RecTime] [datetime] NULL,
[Route] [nvarchar](255) NULL,
[IMSI] ] [nvarchar](50) NULL),
[PhoneNo] [nvarchar](50) ;

CREATE TABLE [SMSDlv_OutBox](
 [ID] [nvarchar](50) NOT NULL  PRIMARY KEY,
 [ToNo] [nvarchar](50) NULL,
 [Subject] [nvarchar](255) NULL,
 [Message] [nvarchar](max) NULL,
 [FileAttach] [nvarchar](max) NULL,
 [CreateTime] [datetime] NULL,
 [SendTime] [datetime] NULL,
 [IsPrior] bit NULL DEFAULT 0,
 [IsSent] bit NULL DEFAULT 0,
 [IsRead] bit  NULL DEFAULT 0,
 [Route] [nvarchar](255)  NULL,
 [MMS] [bit]  NULL DEFAULT 0),
 [FlashSMS] bit NULL DEFAULT 0,
 [IMSI] [varchar](50) NULL,
 [readTag] [varchar](50) NULL,
 [clientTag] [varchar](50) NULL,
 [dlv_Status] [bit] NULL,
 [dlv_Time] [datetime] NULL,
 [sentIndex] [int] NULL,
 [smpp_refid] [varchar](50) NULL);

 CREATE NONCLUSTERED INDEX [IX_SMSDlv_OutBox] ON [dbo].[SMSDlv_OutBox] ([IsPrior], [CreateTime])

CREATE TABLE [SMSDlv_SIM](
 [IMSI] [nvarchar](50) NOT NULL PRIMARY KEY,
 [PhoneNo] [nvarchar](50) NULL,
 [COMPort] [nvarchar](10) NULL,
 [UpdateTime] [datetime] NULL
) ;

2.Mysql

CREATE TABLE `smsdlv_inbox` (
`ID` varchar(50) NOT NULL,
`FromNo` varchar(50) DEFAULT NULL,
`Message` text,
`RecTime` datetime DEFAULT NULL,
`Route` varchar(255) DEFAULT NULL,
`IMSI` varchar(50) DEFAULT NULL,
`PhoneNo` varchar(50) NULL,
 UNIQUE KEY `ID_UNIQUE` (`ID`)
) ENGINE=InnoDB DEFAULT charset=utf8;


CREATE TABLE `smsdlv_outbox` (
`ID` varchar(50) NOT NULL,
`ToNo` varchar(50) DEFAULT NULL,
`Subject` varchar(255) DEFAULT NULL,
`Message` text,
`FileAttach` text,
`CreateTime` datetime DEFAULT NULL,
`SendTime` datetime DEFAULT NULL,
`IsPrior` bit(1) DEFAULT b'0',
`IsSent` bit(1) DEFAULT b'0',
`IsRead` bit(1) DEFAULT b'0',
`Route` varchar(255) DEFAULT NULL,
`MMS` bit(1) DEFAULT b'0',
`FlashSMS` bit(1) DEFAULT b'0',
`ReadTag` varchar(50) DEFAULT NULL,
`IMSI` varchar(50) DEFAULT NULL,
`clientTag` varchar(50) DEFAULT NULL,
`sentIndex` int(11) DEFAULT NULL,
`dlv_Status` bit(1) DEFAULT NULL,
`dlv_Time` datetime DEFAULT NULL,
 `smpp_refid` varchar(50) DEFAULT NULL,
 UNIQUE KEY `ID_UNIQUE` (`ID`)
) ENGINE=InnoDB DEFAULT charset=utf8;

 

CREATE TABLE `smsdlv_sim` (
  `IMSI` VARCHAR(50) NOT NULL,
  `PhoneNo` VARCHAR(50) NULL,
  `COMPort` VARCHAR(10) NULL,
  `UpdateTime` DATETIME NULL,
  PRIMARY KEY (`IMSI`)) ENGINE=InnoDB DEFAULT charset=utf8;

3. Explain of each fileds:

 smsdlv_inbox: This table be used stored all incoming message. so third-party app should not to write any data , just need read this table to get get all incoming messages.

smsdlv_outbox: This table be used to store all outgoing message, third-party app should insert record into this table, SMSDeliverer will scan this table with interval, once new record be found, it will get it and put it to app's outbox to sending. Third applciation just need write the fields which mark with bolder. other fileds need keep as default value.

smsdlv_sim: This talbe be used to stored all sim card phone number, when user send sms or USSD command to query phone number, software will automatic analyse incoming message and pick up the phone number then software will update this table. 3rd appliation is able to get all sim card phone number via read this table.

 

 

4.Example :

SQL Server:

INSERT INTO [dbo].[SMSDlv_OutBox]
           ([ID]
           ,[ToNo]
           ,[Subject]
           ,[Message]
           ,[FileAttach]
           ,[CreateTime]
           ,[SendTime]
           ,[IsPrior]
           ,[IsSent]
           ,[IsRead]
           ,[Route]
           ,[MMS]
           ,[FlashSMS])
     VALUES
           (cast(newid() as varchar(50))
           ,'+1234777885'
           ,null
           ,This is test!'
           ,null
           ,getdate()
           ,null
           ,null
           ,null
           ,null
           ,null
           ,null
           ,null)

MySQL:

INSERT INTO `test`.`smsdlv_outbox` (`ID`, `ToNo`, `Message`,`createtime`) VALUES (uuid(), '99999', 'this is test',localtime());

SQL Server:

INSERT INTO [dbo].[SMSDlv_OutBox]
           ([ID]
           ,[ToNo]
           ,[Subject]
           ,[Message]
           ,[FileAttach]
           ,[CreateTime]
           ,[SendTime]
           ,[IsPrior]
           ,[IsSent]
           ,[IsRead]
           ,[Route]
           ,[MMS])
     VALUES
           (cast(newid() as varchar(50))
           ,'+5562992377885'
           ,'This is subject'
           ,'MMS body'
           ,'c:\abc\sample.jpg'
           ,getdate()
           ,null
           ,null
           ,null
           ,null
           ,null
           ,1)