Support >>PC SMS Gateway Server 3.2>>Database SDK
Database SDK
Integrating PC SMS Gateway Server 3.2 is by accessing directly to its Database. For receive SMS only, the "On Received SMS event" Exec SQL Script or Exec HTTP Submit could be used.
There are two choice using PC SMS Gateway Server Database:
- Using the default database, the default database of PC SMS Gateway Server is using MS Access, the database os located on <INSTALLATION DIRECTORY>\DB
- Creating another database using MS SQL Server, PostreSQL or Oracle, based on the Table properties below, PC SMS Gateway Server Database could be created on MS SQL Server(eg:2000,2005,Express,etc) , Oracle(eg:8,9,10, etc) or PostgreSQL
Content
1.Database
1.1.SMS Database
1.2.Device Database
2. Accessing SMS Directly Into database
2.1 Accessing Inbox
2.2 Create a new SMS
Note: For some database e.g: Oracle, table name and field name should be in uppercase.
1.Database
1.1.SMS Database
There are 6 Table on the SMS Database.
| No |
TableName |
Purpose |
| a |
INBOX |
Store Received SMS |
| b |
OUTBOXMASTER |
Store Outbox/Sent/Failed/Template SMS |
| c |
OUTBOXSLAVE |
Store SMS Outbox/Sent/Failed/Template number list |
| d |
PHONEBOOK |
Store phonebook contact |
| e |
CONTACTGROUP |
Store phonebook contact group |
| f |
CONTACTGROUPMEMBER |
Store phonebook contact group member |
a. INBOX
| No. |
FieldName |
IsPrimaryKey |
ForeignKey |
NotNULL |
Data Type |
Field Size(character) |
Default Value |
Description |
| 1 |
INBOXID |
yes |
|
yes |
ANSI VARCHAR |
60 |
|
INBOXID is Globally Unique identifier(GUID). Generated by PC SMS bulk Sender |
| 2 |
PDU |
no |
|
yes |
ANSI VARCHAR |
640 |
|
SMS PDU |
| 3 |
SMSMESSAGE |
no |
|
no |
ANSI VARCHAR |
320 |
|
SMS Message |
| 4 |
UDHI |
no |
|
no |
ANSI VARCHAR |
160 |
|
SMS UDHI |
| 5 |
MESSAGEREF |
no |
|
no |
ANSI VARCHAR |
160 |
|
SMS Message ref |
| 6 |
ANUMBER |
no |
|
no |
ANSI VARCHAR |
160 |
|
SMS sender number |
| 7 |
SMSC |
no |
|
no |
ANSI VARCHAR |
160 |
|
SMS centre (originating message) |
| 8 |
SMSCTIMESTAMP |
no |
|
no |
DATETIME |
|
|
SMS PDU date |
| 9 |
RECEIVEDDATE |
no |
|
yes |
DATETIME |
|
|
Date when SMS inserted to database |
| 10 |
DEVICECODE |
no |
|
yes |
ANSI VARCHAR |
60 |
|
DEVICECODE is Device Name on Device Settings |
| 11 |
SMSSTATUS |
no |
|
yes |
integer |
|
0 |
0=UNREAD SMS, 1=READ SMS |
b. OUTBOXMASTER
| No. |
FieldName |
IsPrimaryKey |
ForeignKey |
NotNULL |
Data Type |
Field Size(character) |
Default Value |
Description |
| 1 |
OUTBOXID |
yes |
|
yes |
ANSI VARCHAR |
60 |
|
OUTBOXID is Globally Unique identifier(GUID). Generated by PC SMS bulk Sender |
| 2 |
SUBJECT |
no |
|
no |
ANSI VARCHAR |
160 |
|
SMS subject for remainder purpose only, not will be added to the message |
| 3 |
SMSMESSAGE |
no |
|
no |
ANSI VARCHAR |
2000 |
|
SMS Message |
| 4 |
FLASHSMS |
no |
|
yes |
integer |
|
0 |
0=normal SMS, 1=flash SMS |
| 5 |
LONGSMS |
no |
|
yes |
integer |
|
1 |
0=Force sending SMS in shortSMS mode (SMS Message < 160 character)
1=automaticlly sending SMS in long SMS mode when SMS message>160 character) |
| 6 |
CREATEDDATE |
no |
|
yes |
datetime |
|
|
Date when the record is created |
| 7 |
SMSTYPE |
no |
|
yes |
integer |
|
1 |
Outbox=1, Inbox=0, Sent=2, Template=3, Failed=4 |
| 8 |
SMSENCODING |
no |
|
yes |
integer |
|
0 |
0=GSM Alphabet |
| 9 |
PRIORITY |
no |
|
yes |
integer |
|
0 |
Minimum priority = -3 , Maximum Priority=3 |
| 10 |
MAXRETRY |
no |
|
yes |
integer |
|
5 |
Maximum retry of sending SMS to SMS Centre before considered failed. |
| 11 |
DEVICECODE |
no |
|
yes |
ANSI VARCHAR |
60 |
|
DEVICECODE is Device Name on Device Settings. SMS will be sent by specified device |
| 12 |
SMSSTATUS |
no |
|
yes |
integer |
|
0 |
0=UNREAD SMS, 1=READ SMS |
| 13 |
USEVARIABLE |
no |
|
yes |
integer |
|
0 |
Create message using variable taken from phonebook.(e.g. firstname,lastname etc.) |
| 14 |
LASTSENTDATE |
no |
|
no |
datetime |
|
|
Last attempt sending SMS. |
c. OUTBOXSLAVE
| No. |
FieldName |
IsPrimaryKey |
ForeignKey |
NotNULL |
Data Type |
Field Size(character) |
Default Value |
Description |
| 1 |
OUTBOXSLAVEID |
yes |
|
yes |
ANSI VARCHAR |
60 |
|
OUTBOXSLAVEID is Globally Unique identifier(GUID). Generated by PC SMS bulk Sender |
| 2 |
OUTBOXID |
no |
OUTBOXMASTER.OUTBOXID |
yes |
ANSI VARCHAR |
60 |
|
|
| 3 |
BNUMBER |
no |
|
yes |
ANSI VARCHAR |
160 |
|
Recipient number |
| 4 |
COPY |
no |
|
yes |
integer |
|
1 |
Copy of the message |
| 5 |
SENT |
no |
|
yes |
integer |
|
0 |
SMS that has been sent to Service Centre |
| 6 |
RETRYCOUNT |
no |
|
yes |
integer |
|
0 |
The number of trying sending SMS to Service Centre |
d. PHONEBOOK
| No. |
FieldName |
IsPrimaryKey |
ForeignKey |
NotNULL |
Data Type |
Field Size(character) |
Default Value |
Description |
| 1 |
CONTACTID |
yes |
|
yes |
ANSI VARCHAR |
60 |
|
CONTACTID is Globally Unique identifier(GUID). Generated by PC SMS bulk Sender |
| 2 |
CONTACTNAME |
no |
|
yes |
ANSI VARCHAR |
160 |
|
|
| 3 |
MOBILENUMBER |
no |
|
yes |
ANSI VARCHAR |
160 |
|
|
| 4 |
FIRSTNAME |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 5 |
LASTNAME |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 6 |
TITLE |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 7 |
ORGANIZATION |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 8 |
EMAIL |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 9 |
STREETADDRESS |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 10 |
CITY |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 11 |
ZIPCODE |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 12 |
COUNTRY |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 13 |
FIELD1 |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 14 |
FIELD2 |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 15 |
FIELD3 |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 16 |
FIELD4 |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 17 |
FIELD5 |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
e.CONTACTGROUP
| No. |
FieldName |
IsPrimaryKey |
ForeignKey |
NotNULL |
Data Type |
Field Size(character) |
Default Value |
Description |
| 1 |
CONTACTGROUPID |
yes |
|
yes |
ANSI VARCHAR |
160 |
|
CONTACTGROUPID is Globally Unique identifier(GUID). Generated by PC SMS bulk Sender |
| 2 |
CONTACTGROUPNAME |
no |
|
yes |
ANSI VARCHAR |
160 |
|
|
| 3 |
PROPERTIES |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
f.CONTACTGROUPMEMBER
| No. |
FieldName |
IsPrimaryKey |
ForeignKey |
NotNULL |
Data Type |
Field Size(character) |
Default Value |
Description |
| 1 |
CONTACTGROUPID |
yes |
|
yes |
ANSI VARCHAR |
160 |
|
|
| 2 |
CONTACTID |
no |
|
yes |
ANSI VARCHAR |
160 |
|
|
1.2.Device Database
Device database is store the Device Settings, it could be integrated or separated with the SMS database.
There are 1 Table on the Device Database.
| No |
TableName |
Purpose |
| a |
DEVICE |
Store Device Settings |
a. DEVICE
| No. |
FieldName |
IsPrimaryKey |
ForeignKey |
NotNULL |
Data Type |
Field Size(character) |
Default Value |
Description |
| 1 |
DEVICECODE |
yes |
|
yes |
ANSI VARCHAR |
60 |
|
|
| 2 |
SETTINGSGUID |
no |
|
yes |
ANSI VARCHAR |
60 |
|
|
| 3 |
PORTNAME |
no |
|
no |
ANSI VARCHAR |
60 |
|
|
| 4 |
AUTOCONNECTATSTART |
no |
|
no |
integer |
|
|
|
| 5 |
BAUDRATE |
no |
|
no |
integer |
|
|
|
| 6 |
CONTROLDTR |
no |
|
no |
integer |
|
|
|
| 7 |
CONTROLRTS |
no |
|
no |
integer |
|
|
|
| 8 |
OUTCTSFLOW |
no |
|
no |
integer |
|
|
|
| 9 |
OUTDSRFLOW |
no |
|
no |
integer |
|
|
|
| 10 |
DSRSENSITIVITY |
no |
|
no |
integer |
|
|
|
| 11 |
TXCONTINUEONOFF |
no |
|
no |
integer |
|
|
|
| 12 |
XONXOFFIN |
no |
|
no |
integer |
|
|
|
| 13 |
XONXOFFOUT |
no |
|
no |
integer |
|
|
|
| 14 |
SMSAUTORETRIEVE |
no |
|
no |
integer |
|
|
|
| 15 |
SMSINBOXDEVICEDELETE |
no |
|
no |
integer |
|
|
|
| 16 |
ENABLESMSSEND |
no |
|
no |
integer |
|
|
|
| 17 |
SMSMAXRETRY |
no |
|
no |
integer |
|
|
|
| 18 |
SMSENCODING |
no |
|
no |
integer |
|
|
|
| 19 |
COMMANDTIMEOUT |
no |
|
no |
integer |
|
|
|
| 20 |
CONNECTIONSTRING |
no |
|
no |
ANSI VARCHAR |
1024 |
|
|
| 21 |
CONNECTIONTIMEOUT |
no |
|
no |
integer |
|
|
|
| 22 |
LOGINPROMPT |
no |
|
no |
integer |
|
|
|
| 23 |
DEVICETIMEOUT |
no |
|
no |
integer |
|
|
|
| 24 |
INITIALIZATIONCOMMAND |
no |
|
no |
ANSI VARCHAR |
1024 |
|
|
| 25 |
CHECKINBOXINTERVAL |
no |
|
no |
integer |
|
|
|
| 26 |
CHECKINBOXINTERVALLY |
no |
|
no |
integer |
|
|
|
| 27 |
CUSTOMEXECSQL |
no |
|
no |
integer |
|
|
|
| 28 |
STORESMSONINBOXDB |
no |
|
no |
integer |
|
|
|
| 29 |
CUSTOMEXECSQLSCRIPT |
no |
|
no |
ANSI VARCHAR |
1024 |
|
|
| 30 |
EXECHTTPSUBMIT |
no |
|
no |
integer |
|
|
|
| 31 |
HTTPSUBMITACTIONNAME |
no |
|
no |
ANSI VARCHAR |
30 |
|
|
| 32 |
PARAMSENDERNUMBER |
no |
|
no |
ANSI VARCHAR |
30 |
|
|
| 33 |
PARAMSMSMESSAGE |
no |
|
no |
ANSI VARCHAR |
30 |
|
|
| 34 |
PARAMPDU |
no |
|
no |
ANSI VARCHAR |
30 |
|
|
| 35 |
PARAMUDHI |
no |
|
no |
ANSI VARCHAR |
30 |
|
|
| 36 |
PARAMMESSAGEREF |
no |
|
no |
ANSI VARCHAR |
30 |
|
|
| 37 |
PARAMSMSC |
no |
|
no |
ANSI VARCHAR |
30 |
|
|
| 38 |
PARAMSMSCTIMESTAMP |
no |
|
no |
ANSI VARCHAR |
30 |
|
|
| 39 |
PARAMDEVICECODE |
no |
|
no |
ANSI VARCHAR |
30 |
|
|
| 40 |
HTTPSUBMITTYPE |
no |
|
no |
integer |
|
|
|
| 41 |
CUSTOMEXECSQLCONNECTIONTIMEOUT |
no |
|
no |
integer |
|
|
|
| 42 |
CUSTOMEXECSQLLOGINPROMPT |
no |
|
no |
integer |
|
|
|
| 43 |
CUSTOMEXECSQLCOMMANDTIMEOUT |
no |
|
no |
integer |
|
|
|
| 44 |
CUSTOMEXECSQLDEFAULTDATABASE |
no |
|
no |
integer |
|
|
|
| 45 |
CUSTOMEXECSQLCONNECTIONSTRING |
no |
|
no |
ANSI VARCHAR |
1024 |
|
|
| 46 |
USECUSTOMSMSC |
no |
|
no |
integer |
|
|
|
| 47 |
CUSTOMSMSCNUMBER |
no |
|
no |
ANSI VARCHAR |
160 |
|
|
| 48 |
RECEIVENEWSMSONLY |
no |
|
no |
integer |
|
|
|
2. Accessing SMS Directly Into database
2.1 Accessing Inbox
To access SMS on inbox, you can see detail on INBOX Table
2.2 Create a new SMS
EXAMPLE 1. Create SMS Outbox directly to database
To create SMS Outbox :
1. Create a record on "OUTBOXMASTER"
2. Create some records on "OUTBOXSLAVE" with corresponding "OUTBOXID" with "OUTBOXMASTER"
Scenario 1 :
Sending SMS message immediately through DEVICE1(one of the Device Name), the message is "Hallo Everybody!" and will be sent to 3 recipient: "111","222","333".
Each recipient will be received 1 copy of message except "333" will be received 10 copy of message.
Step 1. Create record on OUTBOXMASTER
| FieldName |
Value |
Description |
| OUTBOXID |
3E109592-DD81 |
Instead of GUID, any string value is permitted as long as it is unique |
| SUBJECT |
Test |
|
| SMSMESSAGE |
Hallo Everybody! |
|
| FLASHSMS |
0 |
|
| LONGSMS |
0 |
If the SMSMESSAGE <160 Character, set to 0 (Short SMS) |
| CREATEDDATE |
(DateTime Now!) |
|
| SMSTYPE |
1 |
When sending SMS, the SMSTYPE should be 1 (OUTBOX) |
| SMSENCODING |
0 |
|
| PRIORITY |
3 |
The message send with the highest priority |
| MAXRETRY |
5 |
|
| DEVICECODE |
DEVICE1 |
Device for sending this SMS |
| SMSSTATUS |
0 |
|
| USEVARIABLE |
0 |
|
| LASTSENTDATE |
(null value) |
|
Step 2. Create record on OUTBOXSLAVE, each record Contain one recipient number. The field value of OUTBOXID in OUTBOXSLAVE is foreign key from OUTBOXMASTER table.
#1 record
| FieldName |
Value |
| OUTBOXSLAVEID |
3E109AB2-DD81 |
| OUTBOXID |
3E109592-DD81 |
| BNUMBER |
111 |
| COPY |
1 |
| SENT |
0 |
| RETRYCOUNT |
0 |
#2 record
| FieldName |
Value |
| OUTBOXSLAVEID |
3ECD9592-DD81 |
| OUTBOXID |
3E109592-DD81 |
| BNUMBER |
222 |
| COPY |
1 |
| SENT |
0 |
| RETRYCOUNT |
0 |
#3 record
| FieldName |
Value |
| OUTBOXSLAVEID |
3E109592-FF81 |
| OUTBOXID |
3E109592-DD81 |
| BNUMBER |
333 |
| COPY |
10 |
| SENT |
0 |
| RETRYCOUNT |
0 |
|
