SMS from/to SAP SQL Anywhere
See how to send and receive SMS messages through an SAP SQL Anywhere server with the Database User of Ozeki SMS Gateway. Here you can find a connection string and short CREATE TABLE statements to get started. You can send messages by inserting new message records. We assume you have already used the SAP SQL Anywhere and can perform basic operations with it. You will be provided some useful information and detailed step by step instructions. We hope you will find this example easy to understand. Begin now!
What is SAP SQL Anywhere?
SAP SQL Anywhere is a package that provides data management tools. It is intended to run on PCs, mobile devices, or anywhere where there is no traditional data center
You should have two important programs. The first one is an SAP SQL Anywhere server, while the second one is Ozeki SMS Gateway, which must have a Database User installed, so it can connect to the SAP SQL Database Server using a connection string. Please create a table for the incoming messages and another table for the outgoing messages. You should call these tables as 'ozekimessagein' and 'ozekimessageout', which is used in the current example. You can see your received SMS messages by using a simple SELECT and you can send SMS message with a simple INSERT SQL statement.
The browser GUI of Ozeki SMS Gateway can be used for installing a Database User. Please provide the ODBC connection string for your SAP SQL Anywhere database. The Database User can be configured from the browser GUI. Keep in mind to select 'Odbc' and type the connection string (Figure 4).
How to receive SMS with SAP SQL Anywhere database
The following SMS receiving system enables you to receive and store SMS messages using an SAP SQL Anywhere Database Server. The system consists of a phone, a computer running the Ozeki SMS Gateway software, a Database Server, and your application. The message sent from the phone will be received by the computer. It will send it to the Database server to store it. Your application will be then able to get the messages from the Database Server.
How to send SMS from SAP SQL Anywhere database
This SMS sending system will be able to send messages and store those using an SQP SQL Anywhere Database Server. The system consists of an application that you will use to send SMS. An SAP SQL Database Server, a computer running Ozeki SMS Gateway Software, and a phone to receive the messages with. The system works the following way: Your application will store the messages to send in an SAP SQL Anywhere Database Server. It will store the message, so the Ozeki SMS Gateway can check it periodically for messages to send. If it finds any, it will forward those messages to the predefined address.
SMS from/to SAP SQL Anywhere(Video tutorial)
In this video, we are going to show you the process of connecting an SAP SQL Anywhere database and the Ozeki SMS Gateway. You are going to learn how you can create a database in SAP SQL Anywhere. The video will start with opening the SQL central application and will take you to confirm your sent message. In the video, you will learn how you can create a database with two tables in SQL central, and how to set up an application interface in Ozeki SMS Gateway. In this 3:56 long video, we explain to you every detail about the process, so you will have no problem with following the steps.
Choose a database file
In the 'Create a Database Wizard', the first step will be choosing a file name for the database file. This could be anything but we advise you to choose the 'ozekidb' as the file name for easier identification. If you are satisfied with the name, click the 'Next' button located at the bottom of the window to proceed to the next step (Figure 3).
Specify the login credentials
In the next step (Figure 4), you can create a new database user. There are 3 textboxes. The first one is the name of the user and the remaining two are the login password and the confirmation textbox for the password. Make sure to fill out all the textboxes to have a safe DBA user.
Create a table for incoming messages
Now you can start executing SQL statements. The first thing you want to do is to create the 'ozekimessagein' table. This table will serve as storage for your incoming messages. All the incoming messages will be stored here as a record with all the details about them. You can find the code that you need to execute at the end of this tutorial.
Create a table for outgoing messages
Now that you have a table for incoming messages, all you need is a table to store outgoing messages. This will be the 'ozekimessageout' table. You can find the code that created the table at the end of this tutorial. Copy-paste it into SQL central and execute it. It will create a perfect table to use as storage for outgoing messages (Figure 6).
Choose the SQL messaging option
Open the Ozeki SMS Gateway and search for the 'Add new user/application' button on the left side of the window. Click it to open the 'Add new user or application' install list. There you need to search for the 'Application interfaces' section and choose the SQL messaging option. Click the blue Install button to proceed to the next step (Figure 7).
Choose the SQL Anywhere option
In the next install list, you can see all the SQL database managing softwares that can work with the Ozeki SMS Gateway. In this case, you need to choose the 'SQLAnywhere'. This will work great with the two tables you just set up. You can see the install list and the item you need to choose on Figure 8.
Provide connection settings
To establish a connection between the Ozeki SMS Gateway and SQL Anywhere, you need to provide connection details. To do so, open the general tab of the SQL Anywhere application interface and search for the Connection settings group box. There, you need to select the version of the SQL Anywhere you are using, into the Driver textbox. You need to type in the host address, server name, port number, database name, and login credentials of the SQL database (Figure 9). If you are done with it, click the 'Ok' button.
If you followed the steps correctly, you now have a working connection between the Ozeki SMS Gateway and SQL Anywhere. To start using it, you need to turn it on. To do so, click the 'Connection' switch button. If it is green and you can see a checkmark next to the SQL Anywhere, your connection is up a running (Figure 10).
Execute the INSERT command
To test your connection, you need to insert some messages into the 'ozekimessageout' table. To do so, execute the INSERT INTO statement. It will place a test SMS into the table and start the sending process. You can see the code in Figure 11.
The sent folder
Now to confirm that the message was sent successfully, open the Sent folder of the Ozeki SMS Gateway. This will open a list of all the messages sent from the gateway. You can get more information about the message like the time of sending, the text of the message, and the result of the sending process. You can see the example SMS message on Figure 12.
SAP SQL Anywhere CREATE TABLE script
Please create the 'ozekimessageout' and 'ozekimessagein' tables on your SAP SQL Anywhere database server.
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 INDEX "ozekimessageinindex" ON "ozekimessagein" ( "id" ASC ) 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 "ozekimessageoutindex" ON "ozekimessageout" ( "id" ASC ) go commit work go
The size of the 'msg' field can be always increased above 160 characters. You can also change it's data type as well.
The index of each record is the 'id' attribute. Please maintain the 'id' in all tables.
Congratulation! You have finished with the setup of connection between the Ozeki SMS Gateway and the SQL Anywhere. Feel proud of yourself!
Now, you can pause and catch your breath. We hope you didn’t get lost in the explanations. We did our very best to make it simple and to walk you through all the details as painlessly as possible. Now you can create this connection anytime and start sending SMS messages from an SQL database. This solution takes advantage of all the speed and reliability of the Ozeki SMS Gateway and the portability of SQL Anywhere.
If you wish to learn more about the software solutions that Ozeki offers, make sure to visit ozeki-sms-gateway.com.