SMSgee PC SMS Gateway Server Database Software Development Kit (SDK)
Table Of Content
2. Preparing SQL Database For PC SMS Gateway Server
3. Integrating PC SMS Gateway Server by Accessing SQL Database
3.3.3 Sending SMS Using Default Parameter
3.3.4 Sending SMS Using Custom Parameter
1. Introduction
This Software Development Kit (SDK) purpose is to explain about one of very useful feature from the SMSgee PC SMS Gateway Server which is capable to integrated with other application.
Since the SMSgee PC SMS Gateway Server store SMS data on SQL Database, to integrate with another application, so they can use the SMSgee PC SMS Gateway Server SMS capabilities, is by accessing the SMSgee PC SMS Gateway Server database.
Picture 1. SMSgee PC SMS Gateway Server System
Chapter 2 Explain about preparing SQL Database for SMSgee PC SMS Gateway Server.
Chapter 3 Explanation about integrating SMSgee PC SMS Gateway Server with other application.
2. Preparing SQL Database For PC SMS Gateway Server
The default SQL database included on the SMSgee PC SMS Gateway Server installer are MS Access, for any other purpose it is possible using another SQL database.
The SQL Database supported for SMSgee PC SMS Gateway Server are :
- Microsoft Access (version 2000, 2003)
- Microsoft SQL Server (version 9 / SQL Server 2005 or above) including Microsoft SQL Express
- MySQL (version 5 or above)
- Oracle (version 9 or above).
The SQL Table script for creating tables and indexes is available on the installer, after installation the file located on the installation directory on folder DB Table Script
.
Picture 2. Table Script Shortcut
The available Database table Script are :
- Microsoft SQL Server (Version 8 compatible script)
- Oracle (Version 9 compatible script)
- MySQL (Version 5 compatible script)
3. Integrating PC SMS Gateway Server by Accessing SQL Database
3.1 Introduction
The SMSgee PC SMS Gateway Server store SMS data on SQL Database, integrated by another application is by accessing the SMSgee PC SMS Gateway Server database.
For ensuring Date Time compability across database, the SMSgee PC SMS Gateway Server store the Date Time in Double data type(e.g: INBOX.SMSTIMESTAMP, INBOX.RECEIVEDDATE, OUTBOXMASTER.CREATEDDATE, OUTBOXMASTER.LASTSENTDATE, OUTBOXLOG.CREATEDDATE, OUTBOXLOG.MODIFIEDDATE ). The SMSgee PC SMS Gateway Server Date Time begin from December, 30 1899 00.00 , means the Date Time value is the day passed since 30 Desember 1899.For example, 1 day is valued 1, and time is the fraction part. For example, the value of 1.5 means December, 31 1899 12:00 (mid day)
3.2 Receiving SMS
3.2.1 Introduction
The SMSgee PC SMS Gateway Server store received SMS in INBOX table. When other application is wants to reead the received SMS could be done by accessing the INBOX table.
3.2.2 INBOX table fields
Each records on INBOX table holds one PDU. A non long SMS only consist of one part or one PDU. A long SMS consist of multiple part or multiple PDU, the SMS property that linked the part of long SMS are UDHI and MESSAGEREF.
The INBOX table fields are,
3.2.3 Read non Long SMS from INBOX Table
Non long SMS are SMS which message length are less than 160 character and sent using single PDU.
The example of SQL Script for reading received Inbox non long SMS are
SELECT * FROM INBOX WHERE SMSTYPE=0
Notes: SMSTYPE of Received Inbox SMS are 0
3.2.4 Read Long SMS from INBOX Table
Long SMS are SMS which message length are more than 160 character and sent using multiple PDU part. The SMS property that linked long SMS are UDHI and MESSAGEREF.
The example of SQL Script for reading received Inbox long SMS are,
Step 1. Select a record (a part) of long SMS from the table.
SELECT * FROM INBOX WHERE SMSTYPE=0 AND LONGSMS=1
Step 2. Using MESSAGEREF and UDHI value from a part of long SMS, then read all part of the SMS
SELECT * FROM INBOX WHERE SMSTYPE=0 AND LONGSMS=1 AND MESSAGEREF='[MESSAGEREF_OF_SMS]' AND UDHI LIKE '[UDHI_SUBSTRING_LENGTH_WITHOUT_LAST_TWO_CHARACTER]%' AND ANUMBER='[ANUMBER]'
For example,
if UDHI='050003370201', then UDHI_SUBSTRING_LENGTH_WITHOUT_LAST_TWO_CHARACTER='0500033702',
and Messageref ='OD',
and ANUMBER='+6281320573350'
then the SQL script will be ,
SELECT * FROM INBOX WHERE SMSTYPE=0 AND LONGSMS=1 AND MESSAGEREF='OD' AND UDHI LIKE '0500033702%' AND ANUMBER='+6281320573350'
3.3 Sending SMS
3.3.1 Introduction
The SMSgee PC SMS Gateway Server store Outbox SMS in OUTBOXMASTER, OUTBOXRECIPIENT and OUTBOXLOG table. To send SMS from other application could be done by inserting the Outbox SMS record in OUTBOXMASTER and OUTBOXRECIPIENT table, and read the log whether the SMS has been successfully sent (to SMS centre) could be done by acessing OUTBOXLOG table.
3.3.2 OUTBOX Tables Fields
OUTBOXMASTER store the SMS parameter value, the OUTBOXRECIPIENT store the recipient list, and OUTBOXLOG store the SMS log whether has been sent (to SMS centre).
OUTBOXMASTER table field description are
| Field Name | Field Type | Field Length | Not Null | Description |
| OUTBOXID | VARCHAR | 60 | yes | Table Primary Key |
| SUBJECT | SUBJECT | no | SMS Subject, for archiving purpose only and will not displayed on SMS message. | |
| SMSMESSAGE | TEXT/MEMO or VARCHAR(2000) | no | SMS Message | |
| FLASHSMS | INTEGER | no | SMS flag whether the SMS is flash SMS. 0=Non Flash SMS(default), 1=Flash SMS | |
| LONGSMS | INTEGER | no | SMS flag whether the SMS is Force non Long SMS or auto detect. 0=Force non Long SMS , 1=Auto detect(default) | |
| CREATEDDATE | DOUBLE | yes | TimeStamp when the record is inserted | |
| SMSTYPE | INTEGER | yes | SMS Type flag. 1=Outbox, 2=Archive, 3=Template, 5=DeletedOutbox, 6=Draft | |
| CHARENCODING | INTEGER | no | SMS character encoding type. 0=GSM Alphabet-7Bit, 1=Octet-8bit, 2=Ucs2-16 bit, 3=Auto Detect(default) | |
| PRIORITY | INTEGER | yes | SMS Outbox Send Priority -2=Lowest, -1=Lower, 0=Normal, 1=Higher, 2=Highest | |
| MAXRETRY | INTEGER | yes | Maximum Retry attemp to send SMS to SMS centre. Default value is 3 | |
| DEVICECODE | VARCHAR | 60 | yes | DeviceCode used for Sending SMS |
| SMSSTATUS | INTEGER | yes | Application flag wheter SMS has been read. 0=unread, 1=read. | |
| USEVARIABLE | INTEGER | no | SMS flag whether the SMS Using Variable Tag (Mail Merge like feature). 0=Disable, 1=Enabled(default) | |
| LASTSENTDATE | DOUBLE | no | N/A(Not applicable) for compability only | |
| USECUSTOMSMSC | INTEGER | no | SMS flag whether the SMS using custom SMSC(SMS Centre) Number. 0=Disable(default) , 1=Enabled | |
| CUSTOMSMSCNUMBER | VARCHAR | 160 | no | SMSC(SMS Centre) Number used if USECUSTOMSMSC is set with 1 |
| STATUSREQUEST | INTEGER | no | SMS flag whether the SMS is request Status Report. 0=Disable(default), 1=Enabled | |
| REQUESTREPLY | INTEGER | no | SMS flag whether the SMS is request Repl. =Disable(default), 1=Enabled | |
| VALIDITYPERIOD | INTEGER | no | SMS validity period. The default value 169 (3 day) | |
| SCHEDULEDSENT | INTEGER | yes | Flag whether SMS Send on specified date and time . 0=Disable, 1=Enabled | |
| SCHEDULE | DOUBLE | no | Date and Time if SCHEDULEDSENT set with 1 |
OUTBOXRECIPIENT table field description are,
| Field Name | Field Type | Field Length | Description |
| OUTBOXRECIPIENTID | VARCHAR | 60 | Table Primary Key |
| OUTBOXID | VARCHAR | 60 | Foreign Key (OUTBOXMASTER primary key) |
| COPY | INTEGER | SMS copy. | |
| RECIPIENT | VARCHAR | 160 | SMS Recipient. |
| RECIPIENTTYPE | INTEGER | SMS Recipient type. 0=Mobile Number, 1= Contact ID from PHONEBOOK table, 2=Contact ID from CONTACTGROUP table |
OUTBOXLOG table field description are,
| Field Name | Field Type | Field Length | Description |
| OUTBOXRECIPIENTID | VARCHAR | 60 | Foreign Key (OUTBOXRECIPIENT Table Primary Key) |
| CONTACTID | VARCHAR | 160 | SMS Recipient. |
| SENT | INTEGER | Number of SMS that has been sent. | |
| RETRY | INTEGER | Retry attemp to send SMS | |
| BNUMBER | VARCHAR | 160 | SMS Recipient mobile number |
| SMSMESSAGE | TEXT/MEMO or VARCHAR(2000) | SMS Message | |
| CREATEDDATE | DOUBLE | Timestamp when the record is inserted | |
| MODIFIEDDATE | DOUBLE | Last timestamp when the record is inserted or updated | |
| DELIVERYREPORT | INTEGER | N/A(not applicable) | |
| MESSAGEREF | INTEGER | N/A(not applicable) |
3.3.3 Sending SMS Using Default Parameter
This example demonstrate how to Send SMS using PC SMS Gateway Server by inserting SMS Outbox Record into Database using SMS default parameter, which means leave nullable table fields with NULL value, then later when the application read the record will be intepreted as default value.
Prequisities
- Make sure you have done the Device Settings, and have Device Code named "GSMDEVICE1" as described on Picture 3.
Picture 3. Device
STEP 1 Insert SMS Outbox record into database. (follow step accoding to the Database used)
3.3.3.1 using MS SQL Server Database
Notes:
- In the example below the PC SMS Gateway Server SQL Database is using the Microsoft SQL Server 2000
- The table field Date datatype of the PC SMS Gateway Server is stored in database as Double datatype. The application Date datatype start from 0 as December, 30 1899.
- The Microsoft SQL Server 2000 database Date datatype start from 0 as January, 1 1900. The value converting is needing, this is could be done by after the GETDATE() is casted to float, it should be add with 2(two) to compensate the difference date start value.
- The Microsoft Access database Date datatype start from 0 as December, 30 1899 so no compensating value is necessary when using Microsoft Acces database.
3.3.3.2 Using MySQL Database
- In the example below the PC SMS Gateway Server SQL Database is using the MySQL 5.x
- The table field Date datatype of the PC SMS Gateway Server is stored in database as Double datatype. The application Date datatype start from 0 as December, 30 1899.
/* Example D.1 Create Outbox Recipient */;
/* Create Recipient by Mobile Number */;
INSERT INTO OUTBOXRECIPIENT(OUTBOXRECIPIENTID,OUTBOXID,RECIPIENT,RECIPIENTTYPE,COPY) VALUES('R1','OUTBOX1','+628388744713',0,1);
INSERT INTO OUTBOXRECIPIENT(OUTBOXRECIPIENTID,OUTBOXID,RECIPIENT,RECIPIENTTYPE,COPY) VALUES('R2','OUTBOX1','+628996028540',0,1);
/* Example D.2 Create Outbox Master */;
INSERT INTO OUTBOXMASTER(OUTBOXID,SMSMESSAGE,CREATEDDATE,SMSTYPE,PRIORITY,MAXRETRY,DEVICECODE,SMSSTATUS,SCHEDULEDSENT,LONGSMS)
VALUES('OUTBOX1','Sending SMS using SMS Gateway Server',DATEDIFF(CURRENT_TIMESTAMP,FROM_DAYS(693959))+ ( TIME_TO_SEC( TIMEDIFF(TIME(SYSDATE()), MAKETIME(0,0,0))) / 86400 ),1,0,3,'GSMDEVICE1',0,0,1);
3.3.3.3 Using Microsoft Access Database
- In the example below the PC SMS Gateway Server SQL Database is using Microsoft Access
- The table field Date datatype of the PC SMS Gateway Server is stored in database as Double datatype. The application Date datatype start from 0 as December, 30 1899.
/* Example D.1 Create Outbox Recipient */;
/* Create Recipient by Mobile Number */;
INSERT INTO OUTBOXRECIPIENT(OUTBOXRECIPIENTID,OUTBOXID,RECIPIENT,RECIPIENTTYPE,COPY) VALUES('R1','OUTBOX1','+628388744713',0,1);
INSERT INTO OUTBOXRECIPIENT(OUTBOXRECIPIENTID,OUTBOXID,RECIPIENT,RECIPIENTTYPE,COPY) VALUES('R2','OUTBOX1','+628996028540',0,1);
/* Example D.2 Create Outbox Master */;
INSERT INTO OUTBOXMASTER(OUTBOXID,SMSMESSAGE,CREATEDDATE,SMSTYPE,PRIORITY,MAXRETRY,DEVICECODE,SMSSTATUS,SCHEDULEDSENT,LONGSMS)
VALUES('OUTBOX1','Sending SMS using SMS Gateway Server',now(),1,0,3,'GSMDEVICE1',0,0,1);
STEP.2
. Check whether the SMS has been successfully sent (to SMS centre), by accessing
table.
The SQL script for reading OUTBOXLOG for checking whether the SMS has been sent are,
SELECT * FROM OUTBOXLOG INNER JOIN OUTBOXRECIPIENT ON OUTBOXRECIPIENT.OUTBOXRECIPIENTID=OUTBOXLOG.OUTBOXRECIPIENTID WHERE OUTBOXRECIPIENT.OUTBOXID='OUTBOX1'
The OUTBOXLOG also could be accessed from the application,
Picture 4. Outboxlog: Check whether SMS has been sent.
3.3.4 Sending SMS Using Custom Parameter
Sending SMS with custom value according to the purpose, the custom value description could be found on OUTBOXMASTER table field description.
- Login to post comments