Send SMS from SQL Anywhere (part 2/4)
Create Database Tables

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 centers. 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.

Video content
1. Start SQL Central
2. Create Database
3. Connect to Database
4. Run CREATE TABLE statements

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 sql anywhere database
Figure 1 - Create SQL Anywhere database by clicking 'Create database...'

Create Database Wizard

Click Next on the welcome screen (Figure 2).

create database wizard
Figure 2 - Create Database Wizard

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.

select database location
Figure 3 - Select the machine where to store the database

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.

specify databases directory
Figure 4 - Specify the database's location in the filesystem

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.

select where to use the transaction log on your filesystem
Figure 5 - Select where to use the transaction log on your filesystem

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.

specify the dba user
Figure 6 - Create a DBA user. This user will have rights to the database

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.

select the type of encryption you prefer
Figure 7 - Select the type of encryption you prefer

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.

specify the size of the database you wish to create
Figure 8 - Specify the size of the database you wish to create

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.

choose additional database settings
Figure 9 - Choose additional database settings

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.

specify collation settings
Figure 10 - Specify the collation sequence to perform alphanumeric sorting operations in the database

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.

choose the security model for the system procedures
Figure 11 - Choose the security model for the system procedures

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.

connect to database by using the server and database name
Figure 12 - Connect to database by using the server and database name

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.

run create database sql statement
Figure 13 - Run CREATE DATABASE SQL statement

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).

open interactive sql
Figure 14 - Open interactive SQL in the freshly opened SQL Central

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.

connect to your database with the dba user credentials
Figure 15 - Connect to your database with the DBA user credentials you have previously set

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
Code 1 - CREATE TABLE statements to copy

Copy CREATE TABLE statements>

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

copy create table statements from figure sixteen
Figure 16 - Copy CREATE TABLE statements from Code 1

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 creat the ozekimessagein and ozekimessageout tables, which contain incoming and outging messages.

run create table statements on the database server
Figure 17 - Paste CREATE TABLE statements and run them on the SQL Anywhere database server

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