Send SMS messages from Sybase / SQL Anywhere

In this chapter you can learn how to send and receive SMS messages using a Sybase SQL Anywhere database with the Ozeki NG - SMS Gateway software. You will be provided with the connection string and the create table script.

Ozeki NG - SMS Gateway allows you to send and receive SMS messages using a database server with the help of SQL queries. The software supports, among others, the Sybase SQL Anywhere database.

For SMS messaging with a Sybase SQL Anywhere database you need to install and appropriately configure a database user in the Ozeki NG - SMS Gateway program. Also, you need a Sybase SQL Anywhere database server, in which you have to create an ozekimessageout and an ozekimessagein table for outgoing and incoming messages.

You can install and configure a Sybase SQL Anywhere user on the graphical user interface of the software. The installation and configuration of a Sybase SQL Anywhere user is similar to that of any other database user.

To find out how to install and configure a database user, check out the Database User section.

As the Sybase SQL Anywhere database does not require Open DataBase Connectivity (ODBC), select OleDb in the Connection string type dropdown menu.

Replace the default database connection string with the one for the Sybase SQL Anywhere database in the Connection string template (Figure 1):

Provider=ASAProv.90;UID=MyUsername;PWD=MyPassword;DatabaseFile= DatabaseFileLocationOnYourComputer.db;EngineName=Yourdbenginename;Start= YourDatabaseBinaryLocation.exe -c 32M;AutoStop=Yes

Modify the variables in the connection string according to your needs.

specifying the connection string for the oracle database
Figure 1 - Specifying the connection string for the Oracle database

You can create the ozekimessageout and ozekimessagein tables in Sybase SQL Anywhere using the following script.

Sybase SQL Anywhere create table script:

CREATE TABLE "ozekimessagein"
"id" integer NOT NULL DEFAULT autoincrement ,
"sender" varchar(255) NULL ,
"receiver" varchar(255) NULL ,
"msg" varchar(160) NULL ,
"senttime" varchar(100) NULL ,
"receivedtime" varchar(100) NULL ,
"operator" varchar(100) NULL ,
"msgtype" varchar(160) NULL ,
"reference" varchar(100) NULL ,
PRIMARY KEY ("id"),

)
go
commit work
go
CREATE TABLE "ozekimessageout"
(
"id" integer NOT NULL DEFAULT autoincrement ,
"sender" varchar(255) NULL ,
"receiver" varchar(255) NULL ,
"msg" varchar(160) NULL ,
"senttime" varchar(100) NULL ,
"receivedtime" varchar(100) NULL ,
"operator" varchar(100) NULL ,
"status"  NULL ,
"msgtype" varchar(160) NULL ,
"reference" varchar(100) NULL ,
"errormsg" varchar(250) NULL ,
PRIMARY KEY ("id"),
)
go
commit work
go
CREATE INDEX "ozekimessageinindex" ON "ozekimessagein"
(
"id" ASC
)
go
commit work
go
CREATE INDEX "ozekimessageoutindex" ON "ozekimessageout"
(
 "id" ASC
)
go
commit work
go

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.

More information