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 centre

Send SMS messages with SQL Anywhere
SQL Anywhere Installation(Video guide)
Create Database Tables (Video guide)
Configure Database User (Video guide)
Send Test Message (Video guide)

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. (Figure 1)

how to receive sms with sap sql anywhere database
Figure 1 - How to receive SMS with SAP SQL Anywhere database

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. (Figure 2)

how to send sms from sap sql anywhere database
Figure 2 - How to send SMS from SAP SQL Anywhere database

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.

Video 1 - How to connect an SAP SQL Anywhere database and the Ozeki SMS Gateway (Video tutorial)

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 database name and directory
Figure 3 - Specify database name and directory

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.

specify database user name
Figure 4 - Specify database username

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 (that you can see on Figure 5) 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 ozekimessagein table
Figure 5 - Create ozekimessagein table

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

create ozekimessageout table
Figure 6 - Create ozekimessageout table

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

install sql messaging user
Figure 7 - Install SQL messaging user

Choose the SQL Anywhere option

In the next install list, you can see all the SQL database managing software 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.

install sql anywhere user
Figure 8 - Install SQL Anywhere user

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.

define the sql anywhere database connection details
Figure 9 - Define the SQL Anywhere database connection details

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

enable sql anywhere connection
Figure 10 - Enable SQL Anywhere connection

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.

insert message to the database table
Figure 11 - Insert message to the database table

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.

the database users sent folder shows that your message has been sent by ozeki sms gateway
Figure 12 - The Database User's sent folder shows that your message has been sent by Ozeki SMS Gateway

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 its data type as well.

The index of each record is the 'id' attribute. Please maintain the 'id' in all tables.

FAQs

Can I create reports and statistics from the data of sent messages?

Enable Extensive Data Logging with SQL Integration: Ozeki SMS Gateway offers SQL logging functionality. By activating this feature, you can store all message data within a database table. This opens the door to utilizing SQL queries for creating detailed statistics and reports.

Can I maintain a log of all sent and received messages in an SQL Server?

Absolutely! Extensive Data Logging with Flexible Database Options

The Ozeki SMS Gateway offers impressive flexibility when it comes to message logging. You can configure it to connect to virtually any database server that supports either OleDb or ODBC drivers. This opens the door to popular choices like:

  • Microsoft SQL Server (MSSQL)
  • Oracle
  • MySQL
  • PostgreSQL
  • Microsoft Access
Every time the gateway receives or sends a message, a detailed record is automatically added to your chosen database. This record typically includes:
  • Sender phone number
  • Recipient phone number
  • Message text
  • Timestamp
The beauty of SQL logging is its customizability. You have the power to expand the stored information beyond the basics. For example, you could include:
  • Sending user ID
  • Message type
By leveraging SQL queries, you can extract valuable insights from this rich data. This empowers you to:
  • Generate comprehensive reports on message traffic
  • Analyse trends and patterns in communication
  • Gain a deeper understanding of your communication needs
The dedicated SQL logging page provides in-depth information on setup and configuration: SQL logging page

This approach provides a clear explanation, highlights the benefits of using a database, and encourages users to explore the dedicated information page for further details.

Does the software provide a way that limits the maximum number of messages a user can send?

Ozeki SMS Gateway provides granular control over message sending costs through user-based accounting. Here's how it works:

  • Enable Accounting: Activate accounting on individual user or application configuration forms.
  • Set Credit Limits: Define daily, weekly, monthly, and yearly limits to restrict the number of messages a user can send. This helps prevent unexpected charges and keeps your messaging budget in check.
  • Track Usage: The accounting feature automatically generates monthly reports detailing the number of messages sent by each user. This allows you to monitor usage trends and identify areas for potential optimization.
Benefits:
  • Cost Management: Set spending limits to avoid exceeding your budget.
  • User Accountability: Track individual message usage for better cost allocation or billing purposes.
  • Transparency: Monthly reports provide clear insights into message activity.
In essence, user-based accounting empowers you to manage message sending costs effectively, ensuring budget adherence and informed decision-making.

Can I set credit limits for each client / each user?

Ozeki SMS Gateway (version 2.1.0 and above) empowers you with the Credit Limit Handling feature for precise control over message costs.

Managing User Spending:

  • Individual User Configuration: Each user has a dedicated configuration form where you can enable accounting and set credit limits.
  • Flexible Limits: Define limits for different timeframes (daily, weekly, monthly, yearly, or all-time) to fit your specific needs.
  • Detailed Cost Control: Go beyond simple limits! You can even specify costs for individual message routes. For example, messages sent through Vodafone might deduct 1.2 credits, while T-Mobile messages might deduct 1 credit.
Gaining Visibility:
  • Usage Reports: Easily generate user-specific reports summarizing message traffic history. These reports, containing usage summary and details, can be used for accurate invoicing.
  • Informed Decision-Making: Track user activity and identify potential areas for cost optimization.
Benefits:
  • Prevent Overspending: Set limits to ensure users stay within your budget.
  • Cost Transparency: Gain clear insights into message costs with detailed reports.
  • Simplified Invoicing: Generate accurate invoices based on user-specific usage reports.
  • Granular Control: Manage costs with flexibility, adjusting limits and routing costs as needed.
In summary, the Credit Limit Handling feature empowers you to establish a robust system for responsible and cost-effective message transmission within Ozeki SMS Gateway.

Final thoughts

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.

More information