Products
Ozeki 10
Apps
Chat
SMS Gateway
SMS with Client Software
HTTP API
SMS from/to Database
MSSQL Express
Oracle SMS
Connection strings
Oracle 11g Express
Oracle Installation
Create Database
Configure Database User
Test Message
MySQL SMS
PostgreSQL
SAP SQL Anywhere
Microsoft Access
PHP SMS API
Solutions
C# SMS API
VB.NET SMS API
SQL Logging
System Database
Robot Controller
Softphone
Connections
Installation guide
Terms and Definitions
SMS
Bulk Messenger
Cluster
Developer tools
Robots
Hardware
Technology
Company


Ozeki SMS Gateway
MSSQL Express MSSQL Express | Connection strings Connection strings

SMS from/to Oracle

The connection between Oracle Database and Ozeki SMS Gateway is a perfect solution for SMS messaging. Ozeki SMS Gateway's Database User makes sure that incoming SMS messages can be inserted while outgoing SMS messages can be selected from database tables. Incoming and outgoing messages are handled in separate tables. The solution uses the Ozeki SMS Gateway software installed on your PC.

How to send SMS from Oracle SQL
Oracle SQL Installation (Video guide)
Create Database Tables (Video guide)
Configure Database User (Video guide)
Send Test Message (Video guide)

This page extends the general guide to SMS from/to Database.

You should also make sure that Ozeki SMS Gateway is installed. You should also check if Oracle Database is installed. If Oracle is not installed on your machine, please download the server and client from here. You will see how to store outgoing and incoming SMS messages in your Oracle Database's tables.

You can choose from a wide variety of options to send and receive SMS messages. The most popular hardware option is a mobile phone or 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. Please set up one of these connections and install a Database User from the top menu of Ozeki SMS Gateway ('Users and applications/Add user or application'). After the Database User has been properly installed and configured, you can connect it to your Oracle Database with a connection string, so it can take control over the tables by using the configured SQL statements. It can insert incoming messages as records into tables or select records for sending outgoing messages to mobile phones.

Each Database Connection needs it's own Database User.
The Database User belonging to an Oracle Database has the following connection string format:

Connection string type:Oracle
Connection string:Data Source=127.0.0.1; User ID=username; Password=pwd123;
(Other 'Connection strings for Oracle')

In the connection string the 127.0.0.1 IP address should be replaced with the IP address of the machine where Oracle Database server is running and do not forget to use the login credentials belonging to your Oracle Server's account.

By including 'Unicode=True' to the connection string, you can use unicode characters like NVARCHAR2. Make sure that NLS_CHARACTERSET and NLS_LANGUAGE parameters are set to your language. You can also try to set HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\NLS_LANG registry key to UTF8.

Now the connection should be configured between Ozeki SMS Gateway's Database User and your Oracle Server, so it is time to create two database tables both for incoming and outgoing message by creating a proper database table structure:

Oracle SQL statements:

CREATE TABLESPACE:

CREATE TABLESPACE ozeki
DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\ozeki.dbf'
SIZE 40M autoextend on;

Figure 1 - Create tablespace 'ozeki'

CREATE USER:

CREATE USER ozeki
IDENTIFIED BY qwe123
DEFAULT TABLESPACE ozeki;

GRANT DBA TO ozeki;

Figure 2 - Create user 'ozeki' and grant access to tablespace

CREATE TABLE:

CREATE TABLE ozekimessagein (
  id int,
  sender varchar(30) default NULL,
  receiver varchar(30) default NULL,
  msg varchar(160) default NULL,
  senttime varchar(100) default NULL,
  receivedtime varchar(100) default NULL,
  operator varchar(120) default NULL,
  msgtype varchar(160) default NULL,
  reference varchar(100) default NULL
 );

CREATE index index_id1 ON ozekimessagein(id);

CREATE SEQUENCE X;

CREATE TRIGGER ozekimessagein_auto BEFORE INSERT on ozekimessagein
for each row
when (new.id is null)
begin
   SELECT x.nextval INTO :new.id FROM DUAL;
end;
/

CREATE TABLE ozekimessageout (
  id int,
  sender varchar(30) default NULL,
  receiver varchar(30) default NULL,
  msg varchar(160) default NULL,
  senttime varchar(100) default NULL,
  receivedtime varchar(100) default NULL,
  operator varchar(120) default NULL,
  msgtype varchar(160) default NULL,
  reference varchar(100) default NULL,
  status varchar(20) default NULL,
   errormsg varchar(250) default NULL
);

CREATE index index_id2 ON ozekimessageout(id);

CREATE SEQUENCE Y;

CREATE TRIGGER ozekimessageout_auto BEFORE INSERT on ozekimessageout
for each row
when (new.id is null)
begin
   SELECT y.nextval INTO :new.id FROM DUAL;
end;
/

Figure 3 - Create table structure

The 'id' field should be maintained to support faster SQL updates.
The size of the message can be set higher than 160 character if it is necessary.
The 'msgtype' field can also be changed.


Copyright © 2000- - Ozeki Informatics Ltd. | info@ozeki.hu | Tel: +36 1 371 0150
Home > Products > Ozeki 10 > Apps > SMS Gateway > SMS from/to Database > Oracle SMS
Page: 5713 | Login