PostgreSQL create table script

Introduction

To create an SQL SMS Gateway with Ozeki NG, you can use a PostgreSQL database, which can be accessed using an ADO or ODBC driver. All you have to do is create two database tables (in the ozekisms database): ozekimessagein and ozekimessageout. (The tables can have additional columns.) Ozeki NG - SMS Gateway will insert incoming messages into the ozekimessagein table. If you want to send a message, you should insert a record into the ozekimessageout table. The SMS Server periodically checks this table for messages to send.

Creating the database and the tables

Start pgAdmin: Start menu -> All Programs -> PostgreSQL 8.2 -> pgAdmin III. Right-click PostgreSQL Database Server 8.2, and left-click Connect. Then, fill in the password field and connect.

create an ozeki sms database — Figure 1 - Create an Ozeki SMS databese

Create the ozekisms database. Right-click Databases, then left-click New Database.

Fill in the Name and the Encoding field, and click OK.

Select the ozekisms database, and click the SQL icon for creating tables.

Copy the following text into the window, and click "Execute query":

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

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

You have to create a user. It will be named "sqluser" and its password will be abc123.

Select the ozekisms database and click the SQL icon for grant rights.

Copy the following text into the window, and click "Execute query":

grant select,update,insert,delete on ozekimessagein to sqluser;
grant select,update,insert,delete on ozekimessageout to sqluser;
grant update on ozekimessagein_id_seq to sqluser;
grant update on ozekimessageout_id_seq to sqluser;

We need the last 2 rows to access the serial ids.

When you are finished, you have to install Odbc driver for PostgreSQL.
In Ozeki NG the connection string type will be "Odbc" and the connection string will be:

DRIVER={PostgreSQL Unicode};Server=localhost;Database=ozekisms;uid=sqluser;Password=abc123;

More information

Next page: Access