Send and Receive SMS messages with the help of a Database

You can use any database with an ODBC driver. All you have to do is create two database tables: ozekismsin and ozekismsout. (The tables can have additional columns.) The SMS Server will insert the incoming messages to the ozekismsin table. If you want to send a message, you should insert a record into the ozekismsout table. The sms server checks this table periodically for messages to send.

SQL table definitions for sending and receiving messages with OZEKI SMS

Microsoft SQL Server

CREATE TABLE ozekismsin (
  id int IDENTITY(1,1),
  sender varchar(30),
  receiver varchar(30),
  msg varchar(160),
  senttime varchar(100),
  receivedtime varchar(100),
  operator varchar(100)
);

CREATE TABLE ozekismsout (
  id int IDENTITY(1,1),
  sender varchar(30),
  receiver varchar(30),
  msg varchar(160),
  senttime varchar(100),
  receivedtime varchar(100),
  reference varchar(100),
  status varchar(20),
  operator varchar(100)
);

Code 1 - SQL SMS example for Microsoft SQL Server

insert into ozekismsout (receiver,msg,status) values
('+36209937723','test','send');

Code 2 - Example for sending a message

Tips:

You can create autoreply function with database triggers:

CREATE TRIGGER autoreply
ON ozekismsin
FOR 
INSERT
AS
 DECLARE @tel VARCHAR(30)
 SELECT TOP 1 @tel=sender 
FROM ozekismsin ORDER BY ID DESC
 INSERT INTO ozekismsout 
(receiver,msg,status) VALUES (@tel,'Thank you for the 
message','send')
GO

Code 3 - Autoreply SMS from SQL

MySQL

CREATE TABLE ozekismsin (
  id int(11) NOT NULL auto_increment,
  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(100),
  PRIMARY KEY (id)
);

CREATE TABLE ozekismsout (
  id int(11) NOT NULL auto_increment,
  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,
  reference varchar(100) default NULL,
  status varchar(20) default NULL,
  operator varchar(100),
  PRIMARY KEY (id)
);

Code 4 - SQL SMS example from MySQL database

Interbase

Learn about Installation instructions for Interbase: 

CREATE TABLE ozekismsout (
  id int NOT NULL,
  sender varchar(30),
  receiver varchar(30),
  msg varchar(160),
  senttime varchar(100), 
  receivedtime varchar(100),
  reference varchar(100),
  operator varchar(120) default NULL,
  status varchar(20),
  PRIMARY KEY (id)
);

CREATE GENERATOR sms_id;

CREATE TRIGGER set_id FOR ozekismsout
BEFORE INSERT  AS
   BEGIN
   New.id = gen_id(sms_id,1);
   END;
      
CREATE TABLE ozekismsin (
  id int NOT NULL,
  sender varchar(30),
  receiver varchar(30),
  msg varchar(160),
  operator varchar(120) default NULL,
  senttime varchar(100),
  receivedtime varchar(100),
  PRIMARY KEY (id)
);
      
CREATE GENERATOR sms_id_;

CREATE TRIGGER set_id_ FOR ozekismsin
BEFORE INSERT AS
   BEGIN
   New.id = gen_id(sms_id_,1);
   END;

insert into ozekismsout (receiver,msg,status) values
('+36209937723','Hello world','send');

Code 5 - Database SMS example from Interbase SQL

Note: MySQL ODBC drivers can be downloaded from  www.mysql.com

Oracle

CREATE TABLE ozekismsin (
  id int,
  sender varchar(30) default NULL,
  receiver varchar(30) default NULL,
  msg varchar(160) default NULL,
  operator varchar(120) default NULL,
  senttime varchar(100) default NULL,
  receivedtime varchar(100) default NULL
);
      
      
CREATE SEQUENCE X;
      
      
CREATE TRIGGER ozekismsin_auto BEFORE INSERT on
ozekismsin
for each row
when (new.id is null)
begin
   SELECT x.nextval INTO :new.id FROM DUAL;
end;
/

CREATE TABLE ozekismsout (
  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,
  reference varchar(100) default NULL,
  status varchar(20) default NULL
);
      
CREATE SEQUENCE Y;
      
CREATE TRIGGER ozekismsout_auto BEFORE INSERT on
ozekismsout
for each row
when (new.id is null)
begin
   SELECT y.nextval INTO :new.id FROM DUAL;
end;

Code 6 - 

insert into ozekismsout (receiver,msg,status) values
('+36209937723','test','send');

Code 7 - Oracle SQL example for sending a text message

PostgresSQL

CREATE TABLE ozekismsin (
  id serial,
  sender varchar(30),
  receiver varchar(30),
  msg varchar(160),
  senttime varchar(100),
  receivedtime varchar(100),
  operator varchar(100)
);

CREATE TABLE ozekismsout (
  id serial,
  sender varchar(30),
  receiver varchar(30),
  msg varchar(160),
  senttime varchar(100),
  receivedtime varchar(100),
  reference varchar(100),
  status varchar(20),
  operator varchar(100)
);

Code 8 - PostgresSQL SQL example for sending a text message

Related pages:

More information