In this video series you can see how to connect Ozeki SMS Gateway to an SQL Anywhere database server for SMS messaging. This video shows how to create the proper database table structure by using the GUI of your SQL Anywhere database server. The article also contains screenshots, which show you step by step how to successfully complete this task. Now let's get started!

What is SQL Anywhere?

SAP SQL Anywhere is an embedded relational database management system (RDBMS) that is designed to make it possible for database applications to run outside of traditional data centres. It is intended to run on PCs in satellite offices, mobile devices, and servers in smaller businesses such as restaurants and retail stores.

What are database tables?

A table is a collection of related data held in a table format within a database. It consists of columns and rows. In relational databases, and flat file databases, a table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows. Each row is identified by one or more values appearing in a particular column subset. A specific choice of columns which uniquely identify rows is called the primary key.

How to create database tables (video tutorial)

In this quick video tutorial, you will learn how to create database tables. This process consists of 4 steps. First you need to open SQL Central and create a database. After creating it, you need to connect to the database. Then, you can create tables by running SQL commands. In the video we create two tables. One for incoming, and one for outgoing messages.

Please scroll down to copy the SQL statements (Figure 1) used in the video. If you have created the database in SQL Anywhere, you can jump to the next video.

Create SQL Anywhere database

Before you can start creating tables, you need to create a database in SQL Anywhere. First you need to launch SQL Central. Then, click on 'Create database...' inside the menu on the left (Figure 1). This will open the Create Database Wizard in a new window, where you can configure your database.

Create Database Wizard

Click Next on the welcome screen (Figure 2).

Select the machine where to store the database

Here you need to select where to store the database. This is done by clicking on the checkbox next to the option you want to select. We have selected 'Create a database on this computer' (Figure 3). Click Next to continue.

Specify the database's location in the filesystem

In this page you need to specify the database's directory in the files. Click on the Browse... button, to open up the explorer (Figure 4). Here you are able to specify the path for the database in the filesystem. When you have selected the location, click Next again to proceed.

Select where to use the transaction log on your filesystem

You can choose to have the database maintain a transaction log file (Figure 5). Click on the checkbox if you wish to enable this option (we recommend you do). Click on the Browse... button again to specify a directory for the log file. Finally, click Next to continue.

Create a database user

After creating the database, you also need to create a database user (Figure 6). This user will have rights to the database. Write your username into the first textbox. Then, write your password into the second textbox. Confirm your password by also typing it into the third textbox. Click Next to continue.

Select the type of encryption

In this page you have the option to enable encryption (Figure 7). You can also select the type of encryption you wish to use. We have decided not to use encryption in this tutorial. Click Next to continue.

Specify the size of the database

Here you need to select the page size your database is going to use. We have selected 4096 bytes, as this will fit our example project well (Figure 8). Select the size by clicking on the checkbox next to it. Click Next again to continue.

Choose additional database settings

In this page you can select additional database settings (Figure 9). We have selected Create SYSCOLUMNS and SYSINDEXES views and Include checksum with each database page. You may select these options by clicking on the checkboxes next to them. Click Next to proceed.

Specify the collation sequence

Here you have to select the collation sequence. We have opted for the default setting (Figure 10). After you have selected it, Click Next to continue.

Choose the security model

Next you must choose the security model for the system procedures (Figure 11). Select Execute the system procedures as the invoker. Click on Next again to continue.

Connect to database

The database configuration process is complete. Now you just need to connect to the database (Figure 12). Click the checkbox next to Connect to the new database. Then, enter a Server name and a Database name into the respective textboxes below. Click Next one more time to continue to the last page.

Run CREATE DATABASE SQL statement

In this page you can finalize your database. You can see which commands will be executed immediately according to your selected options (Figure 13). Click Finish to finalize and create your database.

Open interactive SQL

After successfully creating the database, you will be brought back to the main page of SQL Central. Click on Open interactive SQL inside the menu on the left (Figure 14).

Connect to your database

In this window you can connect to your database. Enter your username and password previously specified by you earlier in the installation (Figure 15). Click on the Connect button to connect to your database.

SQL Anywhere CREATE TABLE statements to copy:

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" varchar(20) 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

Copy CREATE TABLE statements>

Copy the SQL code from Code 1. Select the entire code and right click, then copy (Figure 16).

Run CREATE TABLE statements

Paste the code from Code 1 into the text editor in Interactive SQL (Figure 17). Run the code by clicking on the black play button in the toolbar on the top. Running this piece of code will create the ozekimessagein and ozekimessageout tables, which contain incoming and outgoing messages.

Summary

This is the end of this tutorial. If you have reached this point, you have completed this process successfully. For more information on implementing this solution, check out the next part of this tutorial.

More information

• SQL Anywhere Installation
• Create Database
• Configure Database User
• Send test SMS from SQL anywhere