Powered by

# External database connection

Note: Only ultimate edition support connect external database.

# External database connection is used to integrate SMS feature into any third-party application.

# 1. Configure external database connection

# Create SQL Server connection:

SMS Deliverer support Microsoft access,Microsoft SQL server (2005 or higher) and Mysql.

  1. Click button 'Add' to add database connection

  1. Select database type

  1. Input exists database name or create a new database then click button 'Test connection', if connect database successfully then user may forward next step,

  1. If the database and table already exists , please checked 'select database table from specific database ', otherwise select 'create database table automatically' , click the link 'Script' to view the script, click the button 'Create' to run the script.

  1. Finally named this connection, click the button 'finish', the application will scan this table with interval, if any new records are inserted into the table 'SmsDlv_Outbox', app will automatically pick up them from database and put them in outbox. Any incoming messages will also be saved to table 'SMSDlv_Inbox'.
# Create MySQL  connection:
  1. Click button 'Add' to add database connection

Note: Our application is developed via .Net, so please install components 'Mysql connector/Net' via MySQL :: Download Connector/NET

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

  1. Create table for database , user may also run  script via Mysql admin panel to create the tables, finally click button 'Test connection' to verify the connection.

  1. Name the connection.

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

# 2. Create table script

# 1. Sql server  (View fullscript ) 
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](50) 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)
# 2.Mysql (View fullscript) 
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,
PRIMARY KEY (`ID`),
UNIQUE INDEX `ID_UNIQUE` (`ID` ASC) VISIBLE
) 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,
PRIMARY KEY (`ID`),
UNIQUE INDEX `ID_UNIQUE` (`ID` ASC) VISIBLE
) ENGINE=InnoDB DEFAULT charset=utf8mb4;


CREATE TABLE `smsdlv_sim` (
`IMSI` VARCHAR(50) NOT NULL,
`PhoneNo` VARCHAR(50) NULL,
`COMPort` VARCHAR(50) 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 ( sendtime ASC);
create index idx_isprior ON smsdlv_outbox (isprior ASC);
# 3. Description of all fields:

smsdlv_inbox: This table is used to store all incoming messages. The third-party app just need read it to get all incoming messages.

  • ID: unique ID
  • FromNo: the number of incoming message
  • Message: incoming message
  • Route: COM port number of modem
  • IMSI: IMSI of SIM card.
  • PhoneNo: SIM card phone number.

smsdlv_outbox: This table is used to store all outgoing messages, the third-party app need insert the message into this table then SMS Deliverer will automatically pick them up and put them in outbox. Third application just need write the highlight fields, other fields just keep them with default value.

  • ID: unique ID
  • ToNo: destination number
  • Subject: subject of message (normally it use for MMS) 
  • Message: SMS text.
  • FileAttach: image file path, only fit for MMS,  multiple files separate with comma.
  • CreateTime: Create time of new messages.
  • SendTime: SMS Deliverer will write it after a SMS is sent.
  • IsPrior: Prior message will be inserted into the top of outbox, modem will process them with prior. 
  • IsSent: if the message is sent successfully, SMS Deliverer will set this filed with true(1), otherwise set it with false(0), third-party just need read this field to make sure whether the message is sent.
  • IsRead:  after the message is loaded to outbox,SMS Deliverer will set the filed with true (1) to avoid repeat load same message.
  • MMS: mark the message as a MMS.
  • FlashSMS: mark the message as Flash SMS.
  • Route: Specify the prior COM/SIM for the messages.
  • ReadTag: an unique ID , when the message is loaded to outbox this filed will be set.
  • IMSI: Once message be sent, software will write the SIM card number (IMSI) to this field, Third-party application is able to calculate the sending amount of each SIM card. Third-party application may also write a IMSI number in this field when insert new message, SMS Deliverer will process the message by specified SIM card.
  • ClientTag: Unique ID of each client.
  • Sentindex: Once a message is sent, modem will return an index number. Delivery report need use this index number to locate the message.
  • Dlv_Status: Delivery report, True means deliver successfully. False means failure.
  • Dlv_time: Deliver time.
  • smpp_refid: the ID of sent message which assigned by SMPP server.
  • custMemo:  Customize filed from user http get/post.

smsdlv_sim: This talbe is used to store all SIM card phone numbers, when user send SMS Or USSD command to query phone numbers, the software will automatically analyse incoming message and pick up the phone number then update this table. Any third-party application is able to get all SIM card phone numberS by read this table.

4.Example :

  • Create a new SMS

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

  • Create a new MMS

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)