Products
Ozeki 10
Apps
Chat
SMS Gateway
Quick Start Guide
SMS with Client Software
HTTP API
SMS from/to Database
PHP SMS API
Solutions
C# SMS API
VB.NET SMS API
SQL Logging
MySQL table layout
Oracle table layout
MSSQL table layout
SQL templates
SQL Log Tracking
Keywords
Advanced Traffic Queries
System Database
Robot Controller
Softphone
PBX
Connections
Installation guide
Terms and Definitions
HTTP API
SMS
Bulk Messenger
Cluster
Developer tools
Robots
Hardware
Installer
Technology
Company


Ozeki 10 - SMS Gateway server software
MySQL table layout MySQL table layout | MSSQL table layout MSSQL table layout

Oracle table layout

On this page you can see a connection to an Oracle database layout. You can use the CREATE TABLE statements in the examples below. Generate reports of all SQL queries. You can set this option in your SMS Gateway. SQL queries are generated by incoming and outgoing messages.

You can use almost every kind of database server to log messages. In this example the message flow can be logged by modifying your Oracle 11g database. All messages sent from and received by your Ozeki SMS Gateway will be logged.

Connect Ozeki SMS Gateway to the mobile network to send and receive SMS messages. You can use a GSM modem or a IP SMS connectivity. The most popular hardware option is a mobile phone or a GSM modem attached to your computer. Or you can use popular software options which connects through an IP SMS service provider over the internet. For example SMPP, CIMD2 or UCP/EMI are service provider connections.

How to configure your Oracle 11g server

First you should start by downloading and installing your Oracle 11g, so you can log into the user interface (Figure 1).


Figure 1 - Login to your Oracle application

Click the 'SQL workshop' button to manage the database server (Figure 2), which is a perfect choice for creating tables.


Figure 2 - Click 'SQL Workshop' to manage the database server

Select 'SQL commands' button (Figure 3), so you can provide any statement you wish.


Figure 3 - Click 'SQL commands', so you can add the CREATE TABLE statements

Now you can simply provide the SQL statements, so you can CREATE the required tables. See how to create them on Figure 4, below. Copy and paste the codes as you can see on Figure 5. Please click 'Run' to execute the statements.

Here you can find the statements to create your tables:
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(30) default NULL,
  receiver varchar(30) 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 index index_id1_msgid ON outbox(msgid)
 
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;
 
CREATE TABLE  inbox (
  id int,
  username varchar(30) default NULL,
  msgtype varchar(160) default NULL,
  msgid varchar(60) default NULL,
  sender varchar(30) default NULL,
  receiver varchar(30) 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 index index_id2_msgid ON inbox(msgid)
 
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;
Figure 4 - CREATE TABLE statements to use on your Oracle Server

Paste the SQL statements and click 'Run' to create the tables (Figure 5).


Figure 5 - Paste the statements and click 'Run'

Now please jump back to the browser GUI of Ozeki SMS Gateway, so you can provide the database connection string. Please modify the appropriate parameters to fit your Oracle Database (Figure 6).

Connection Type:OleDb
Connection String:Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=admin;Password=qwe123
Figure 6 - Example OleDb connection string, which needs to be modified

Every modification of the tables will be logged by Ozeki SMS Gateway after you have set it similarly as you can see on Figure 7. Just select 'Edit/Server preferences' from the top menu and select 'Databases' tabpage and 'Logging database settings' sub-tabpage. Finally add the modified connection string. You can also modify SQL statements as well.


Figure 7 - Connect your Oracle database to Ozeki SMS Gateway


Copyright © 2000- - Ozeki Ltd | info@ozeki.hu
Home > Products > Ozeki 10 > Apps > SMS Gateway > SQL Logging > Oracle table layout
Legal | Privacy policy | Terms of use
Page: 5778 | Login | 3.95.131.97