Home
RECOMMENDED FOR YOU:
SMS Gateway software
Ozeki brings you outstanding
SMS Gateway technology. Use our SMS Server products on Windows,Linux, or Android

C# SMS API
Developers can use our C# SMS API to send SMS from C#.Net. The C# SMS API comes with full source code

PHP SMS API
The ozeki PHP SMS gateway software can be used to send SMS from PHP and to receive SMS usig PHP on your website

SMPP SMS Gateway
SMS service providers use our SMPP gateway solution, that offers a high performance SMPP server and SMPP client gateway with amazing routing capabilities
Ozeki NG SMS Gateway

SQL Reporting in Ozeki NG SMS Gateway - Database layout (Oracle)

This page gives you the database connection information and the database layout if you want to setup SQL reporting in an Oracle database server. SQL reporting makes it possible to keep track of all incoming and outgoing messages in the system regardless of who sent it.

To be able to send and receive SMS messages from Oracle 11G XE first you need to create two database tables: "ozekimessagein" and "ozekimessageout". You can find the instructions on this process on this page. The SMS gateway will use SQL queries for sending messages.

Ozeki NG SMS Gateway needs to be connected to the mobile network. You can do this in two ways: you can attach a GSM modem to the PC with a datacable (GSM connectivity) or the SMS gateway can connect directly to the SMS center of the mobile service provider over the Internet (IP SMS connectivity).

You can configure Ozeki NG SMS Gateway with the help of the Graphical User Interface (GUI).

Configuration steps

To configure your SMS system first you need to install Oracle 11G XE to your computer. Then log into it on the web interface (Figure 1).

login to ozeki ng sms gateway
Figure 1 - Login

Click on SQL button (Figure 2).

clicking sql button in sms software
Figure 2 - SQL button

Select SQL commands button (Figure 3).

sql commands in sms software
Figure 3 - SQL commands

Now specify SQL commands. Enter the statements below to create the table layouts. Please click on Run after each command (Figure 4).

running sql commands in sms software
Figure 4 - SQL commands

You can use the following sql example.


CREATE TABLE outbox (
  id int,
  username varchar(30) default NULL,
  msgtype varchar(160) default NULL,
  msgid varchar(60) default NULL,
  callbackid varchar(255) default NULL,
  sender varchar(255) default NULL,
  receiver varchar(255) default NULL,
  msgsubject varchar(1024) default NULL,
  msgdata varchar(1024) default NULL,
  acceptedfordeliverytime varchar(100) default NULL,
  deliveredtonetworktime varchar(100) default NULL,
  deliveredtohandsettime varchar(100) default NULL,
  operator varchar(30) default NULL,
  route varchar(30) default NULL,
  status varchar(130) default NULL,
  errormessage varchar(1024) default NULL,
  cost varchar(10) default NULL
 )
 
 
CREATE index index_id1 ON outbox(id)
 
 
CREATE SEQUENCE X
 
 
CREATE TRIGGER outbox_auto BEFORE INSERT on outbox
for each row
when (new.id is null)
begin
   SELECT x.nextval INTO :new.id FROM DUAL;
end
 
 
CREATE TABLE  inbox (
  id int,
  username varchar(30) default NULL,
  msgtype varchar(160) default NULL,
  msgid varchar(60) default NULL,
  sender varchar(255) default NULL,
  receiver varchar(255) default NULL,
  msgsubject varchar(1024) default NULL,
  msgdata varchar(1024) default NULL,
  senttime varchar(100) default NULL,
  receivedtime varchar(100) default NULL,
  operator varchar(30) default NULL
);
 
 
CREATE index index_id2 ON inbox(id)
 
 
CREATE SEQUENCE Y
 
 
CREATE TRIGGER inbox_auto BEFORE INSERT on inbox
  for each row
  when (new.id is null)
  begin
   SELECT y.nextval INTO :new.id FROM DUAL;
end

In Ozeki NG SMS Gateway go to Server preferences in Edit menu. On Databases tab check Enable SQL logging checkbox (Figure 5). Then select the Database connection type and specify the Database connection string:

The database connection type, you should select is:

OleDb

The database connection string you should use is:

Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=admin;Password=qwe123

databse tab in sms software
Figure 5 - Databases tab in Ozeki NG

If you have any questions please contact us at info @ ozekisms.com!

More information