Previous topicNext topic
Help > How to... >
How to use 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) ;

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);

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

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,
 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',
 UNIQUE KEY `ID_UNIQUE` (`ID`)
) 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.

ID: unique ID,third-party should write it.

ToNo: destination number,third-party should write it.

Subject: subject of message,third-party should write it. (normally it use for MMS) 

Message: outgoing message,third-party should write it.

FileAttach: image file path, only  fit for mms, for sms it should be null,third-party should write it.

CreateTime:new message create time,third-party should write it.

SendTime: message send time, SMSDeliverer will write it. third-party just need read it.

IsPrior: Prior message will insert to top of outbox, modem will send it as prior.  if yes, third-party should wirte it as true, if no, just keep it as null.

IsSent:If message be sent success, SMSDeliverer will set this filed is true(1), otherwise set to false(0), third-party just need read this field to make sure whether the message be sent success.

IsRead:  Normally if third-party add many new records  to smsdlv_outbox, SMSDeliverer will load them one by one, one message be sent success or failed then load next. once message be load to outbox, this filed will be set to true(1) . third-party just need  read it.

Route: when message be sent success or failed,  SMSDeliverer will set this field to modem's COM port, third-party is able to know which modem sent the message by read this field.

MMS: if the message is SMS, just need keep it as null, if the message is MMS,third-party shoud set this field to True(1)

 

 

 

4.Example :

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))
           ,'+1234777885'
           ,null
           ,This is test!'
           ,null
           ,getdate()
           ,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)