If you are working on telephone solutions, please check
out the Ozeki VoIP SIP SDK.
It can be used to create VoIP client and server software. |
|
How to send SMS messages from Oracle
In this guide you can learn
how to send and receive SMS messages using an Oracle database. This guide is
an extension of the general SQL to SMS
configuration guide. It contains Oracle specific information. It is assumed,
that you already have an Oracle database server and the Oracle client
components installed. You also need Ozeki
NG SMS Gateway installed on your
system to have this example work.
In order to be able to send and receive SMS messages from
Oracle, you need to install and appropriately configure Ozeki NG - SMS Gateway.
Ozeki NG SMS Gateway can send and receive SMS messages through a
GSM phone or GSM modem
that is attached to your computer with a phone-to-pc data cable or
through IP SMS service provider connections
over the Internet, such as SMPP,
CIMD2 or
UCP/EMI connections. After one of
these connections is set up, you need to
install a Database user
in the "Users and Applications" menu of Ozeki NG. Once the Database user
is installed and configured properly, you will be able to send SMS messages
from Oracle simply by inserting a record into a database table, and incoming
SMS messages will be saved into another database table in your Oracle database
server.
To configure the database user in Ozeki NG, please use the following information:
| Connection string type: | Oracle |
| Connection string: | Data Source=127.0.0.1; User ID=username;
Password=pwd123; |
(Alternative connection strings for Oracle can be found at the
Oracle connections strings page)
Of course you have to replace the 127.0.0.1 IP address to the IP address or the
Data source name of your Oracle
database server, and the username and pwd123 field to the database username
and database password that matches your user account information stored in Oracle.
Once the connection is configured in Ozeki NG, all you have to do is create
two database tables in your Oracle database according to the following
table definition.
Oracle create table script:
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;
/ |
If you want to work with larger messages, you can increase the size of the msg
field, or you may also change it's data type.
It is strongly recommended to maintain an index in the database
for the 'id' field of the outgoing SMS table to support SQL updates.
If you want to use unicode characters, you should use NVARCHAR2 as the
datatype, you should put "Unicode=True" into the connection string and you
should make sure that the NLS_CHARACTERSET and NLS_LANGUAGE parameters
for your database are set to your language. You may also try to set the
following registry key
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\NLS_LANG to UTF8
Note: Oracle 8i clients cannot access NVARCHAR2 data in Oracle 9i databases
when the server's national character set is specified as AL16UTF16 (the default
setting for Oracle 9i). Because support for the UTF-16 character set was not
introduced until Oracle 9i, Oracle 8i clients cannot read it.
Dig deeper!
People who read this also read...
|
