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

Note: Only ultimate edition support connect external database.

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.  Note: Index and Store procdure is used to improve performance when the database include large data.

1.Sql server  (View full script )

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 TABLE [SMSDlv_SIM](
[IMSI] [nvarchar](50) NOT NULL PRIMARY KEY,
[PhoneNo] [nvarchar](50) NULL,
[COMPort] [nvarchar](10) NULL,
[UpdateTime] [datetime] NULL)

CREATE NONCLUSTERED INDEX [idx_readtag] ON [SMSDlv_OutBox] ( [readTag] ASC)
CREATE NONCLUSTERED INDEX [idx_isread] ON [SMSDlv_OutBox] ( [IsRead] ASC)
CREATE NONCLUSTERED INDEX [idx_sendtime] ON [SMSDlv_OutBox] ( [SendTime] ASC)
CREATE NONCLUSTERED INDEX [idx_isprior] ON [SMSDlv_OutBox] ( [IsPrior] ASC)

--To improve read speed (optional)
go
CREATE PROCEDURE[sp_ReadData] (@clienttag varchar(50),@readtag varchar(50),@rows int) AS
BEGIN< br>update[smsdlv_outbox]setisread=1,readTag=@readtag,clientTag=@clienttag where ID in (
select id from
(select ROW_NUMBER() OVER( order by isPrior,createtime) as px,* from [smsdlv_outbox] where isread <>1) as a
where a.px between 1 and @rows )
select id,tono,subject,message,fileattach,mms,IsPrior,flashsms,route,imsi from [smsdlv_outbox] where readTag=@readtag order by createtime
END

 

2.Mysql (View full script)

          CREATE SCHEMA `smsdlvtest` DEFAULT CHARACTER SET utf8mb4

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=utf8mb4;


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=utf8mb4;

 

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=utf8mb4;

create index idx_readtag ON smsdlv_outbox ( readtag ASC);
create index idx_isread ON smsdlv_outbox ( isread ASC);
create index idx_sendtime ON smsdlv_outbox ( sendtimeASC);
create index idx_isprior ON smsdlv_outbox (isprior ASC);

#improve read speed (optional)
DELIMITER $$
CREATE PROCEDURE `sp_readdata`(
IN pclienttag varchar(50),
IN preadtag varchar(50),
IN prowcount integer)
BEGIN
start transaction;
updatesmsdlv_outboxsetisread=1,readTag=preadtag,clientTag=pclienttag where isread <>1 order by isPrior,createtime limit prowcount ;
select id,tono,subject,message,fileattach,mms,IsPrior,flashsms,route,imsi from smsdlv_outbox where readTag=preadtag order by createtime;
commit;
END$$
DELIMITER ;

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)