How to make scripted calls from SQL Anywhere

In this tutorial you will learn how to make scripted calls from SAP SQL Anywhere. You are required to have Ozeki Phone System installed to implement this solution. We are going to create a database and set up all neccessary configurations in Ozeki Phone System, then use this system to make a test call to a mobile device. We hope you will find this article helpful and informative. Now it's time to get started.

What is SAP 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 scripted calls?

Call scripts guarantee consistency across the call center and allow agents to act more naturally and listen to customers as they know they don’t have to worry about remembering what to say next. Call scripts can be easily integrated with telephony and IVR systems in order to provide the agent useful information about the customer and tailor each interaction accordingly.

What is Ozeki Phone System?

Ozeki Phone System is software for Windows that transforms a computer into a communication server. Ozeki Phone System lets you build applications like PBX, VoIP gateway, IVR and ACD. It can be used for voice calls, video calls, SMS messaging and new-, revolutionary channels like real time communication through webbrowsers and mobile phones.

How to make scripted calls from SQL Anywhere (video tutorial)

This video tutorial will show you how to make scripted calls from SQL Anywhere. To successfully implement this solution, you need to have Ozeki Phone System installed. In this tutorial we will go through the steps of downloading and installing SAP SQL Anywhere, setting up a database and finally using the database to make calls through Ozeki Phone System. 

// *******************************************
// Create the database table layout
// *******************************************
  
CREATE TABLE "ozekicallsin"
(
"id" integer NOT NULL DEFAULT autoincrement ,
"sender" varchar(255) NULL ,
"receiver" varchar(255) NULL ,
"msg" TEXT  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 "ozekicallsinindex" ON "ozekicallsin"
(
"id" ASC
)
go
commit work
go
 
CREATE TABLE "ozekicallsout"
(
"id" integer NOT NULL DEFAULT autoincrement ,
"sender" varchar(255) NULL ,
"receiver" varchar(255) NULL ,
"msg" TEXT NULL ,
"callid" varchar(255) NULL,
"startime" varchar(40) NULL,
"endtime" varchar(40) NULL,
"status" varchar(40) NULL,
"talkduration" varchar(40) NULL,
"ringduration" varchar(40) NULL,
"operator" varchar(100) NULL,
"errormsg" varchar(1024) NULL,
 PRIMARY KEY ("id"),
)
go
commit work
go

CREATE INDEX "ozekicallsoutindex" ON "ozekicallsout"
(
"id" ASC
)
go
commit work
go

CREATE TABLE ozekicallscripts (
    "id" integer NOT NULL DEFAULT autoincrement,
    "msg" TEXT NULL ,
    "phonenumber" varchar(40) NULL,
    PRIMARY KEY ("id"),
)
go
commit work
go
 
CREATE INDEX "ozekicallscriptsindex" ON "ozekicallscripts"
(
"id" ASC
)
go
commit work
go

// *******************************************
// How to make a call: example 1
// The script is in the ozekicallsout table
// *******************************************

INSERT INTO ozekicallsout (status,receiver,msg)
VALUES ('send','101','
<?xml version="1.0"?>
<Response>
	<Request>
        <OnAnswered>
            <Speak Party="caller">You will get dtmf keys 123.</Speak>
            <SendDTMF Party="caller" Keys="123"></SendDTMF>
            <Hangup/>
        </OnAnswered>
    </Request>
</Response>
')

// *******************************************
// How to make a call: example 2
// The script is in a file on drive C
// *******************************************

INSERT INTO ozekicallsout (status,receiver,msg) VALUES ('send','101','C:\ozml\script.ozml')

// *******************************************
// How to make a call: example 3
// The script is in the ozekicallscripts table
// *******************************************


INSERT INTO ozekicallscripts (id,msg)
VALUES ('1','
<?xml version="1.0"?>
<Response>
	<Request>
        <OnAnswered>
            <Speak Party="caller">You are using call script 1. Thank you for calling. </Speak>
            <Hangup/>
        </OnAnswered>
    </Request>
</Response>
')

INSERT INTO ozekicallsout (status,receiver,msg) VALUES ('send','101','1')

Download SQL Anywhere installer

To begin implementing this solution, first you need to download the SQL Anywhere installer (Figure 1). Do this by the official website and download the installer from the products. The installer will be located in your browser's default download directory.

download sql anywhere installer
Figure 1 - Download SQL Anywhere installer

Install SQL Anywhere database

Run the installer you have previously downloaded and begin the installation process (Figure 2). On the welcome screen, click next to continue.

install sql anywhere database
Figure 2 - Install SQL Anywhere database

Installation process

Wait for the installation process to complete (Figure 3).

installation process
Figure 3 - Installation process

Installation finished

SAP SQL Anywhere is now installed on your device. Click on Finish to complete the instllation (Figure 4).

installation finished
Figure 4 - Installation finished

Create database

After successfully installing SAP SQL Anywhere, its time to create your database. Open SQL central and run the Create Database Wizard (Figure 5). Name the datbase "ozekidb" by typing it into the textbox, then specify a directory with the Browse button. Click Next to continue.

create database
Figure 5 - Create database

Create ozekiuser

Next you must create a database user (Figure 6). Enter "ozekiuser" into the first textbox This will be your username. Enter your password into the second and third textboxes. Click on next to proceed and create your database user.

create ozekiuser
Figure 6 - Create ozekiuser

Create ozekicallsin table

Now that you have your database and user, the next step is to create tables. This is done Interactive SQL. The entire code for the project can be found above. Paste the piece of code shown in Figure 7 into the text editor. Run the code by clicking on the play button on the top. This will create the ozekicallsin table, which will contain incoming calls.

create ozekicallsin table
Figure 7 - Create ozekicallsin table

Create ozekicallsout table

Paste the piece of code shown in Figure 8 into the text editor. Run the code by clicking on the play button on the top. This will create the ozekicallsout table, which will contain outgoing calls.

create ozekicallsout table
Figure 8 - Create ozekicallsout table

Create ozekicallscript table

After creating the tables for incoming and outgoing calls, you must also create one for call scripts. Paste the piece of code shown in Figure 9 into the text editor. Run the code by clicking on the play button on the top. This will create the ozekicallscript table.

create ozekicallscript table
Figure 9 - Create ozekicallscript table

Create new service extension

The database is now properly set up. Now you have to switch over to Ozeki Phone System. Click on Extensions inside the menu on the right (Figure 10). Click on the blue Create new Extension button on the top. Click on the Service icon on the right.

create new service extension
Figure 10 - Create new service extension

Select SQL call type

Here you need to select which type of call extension you want to create. Select SQL Call by clicking on it (Figure 11).

select sql call type
Figure 11 - Select SQL call type

Install SQL Anywhere extension

Find SQL Anywhere Calls and click on Install next to it (Figure 12).

install sql anywhere extension
Figure 12 - Install SQL Anywhere extension

Setup connection details

After you have selected SQL Anywhere Calls, you must configure your new connection. This is done in the Connection settings section of Extension details (Figure 13). Here you need to provide the following details by entering them into the corresponding textboxes:

  • Driver
  • Host
  • Server
  • Port
  • Database
  • UserID
  • Password
Click on OK to create the connection.

setup connection details
Figure 13 - Setup connection details

Connect to SQL Anywhere database

You have now created your connection. Enable your connection by clicking on the swtich button on the right (Figure 14). The switch turns green to indicate that a connection is active.

connect to sql anywhere database
Figure 14 - Connect to SQL Anywhere database

Create new outside line

After you have successfully created the database connection, you have to create a new outside line connection. Click on Outside lines inside the menu on the left (Figure 15). Click on the blue Create new Phone Line button on the top. In the Phone line details menu on the right, click on VoIP PBX. This will bring you to a configuration menu.

create new outside line
Figure 15 - Create new outside line

Setup outside line connection

In this menu on the right side of the screen, you need to configure your outside line connection (Figure 16). Here you need to provide the following details by entering them into the corresponding textboxes:

  • Display name
  • Username
  • Register name
  • Password
  • Domain (IP address)
  • Port
Click on OK to create the connection.

setup outside line connection
Figure 16 - Setup outside line connection

Create dial plan rule

The last step before you are able to use the database, is to create a dial plan rule. Click on Dial plan inside the menu on the left (Figure 17). Click on the blue Create new Route button on the top. Look for the Settings section in the Route details menu on the right. Set From: as 9000@localhost. Set To: as voip_Phone_Line_1@localhost.

create dial plan rule
Figure 17 - Create dial plan rule

Insert call into database

You are now able to use the database for scripting calls. Open the details page of your SQL Anywhere connection. Click on the SQL tab to open the SQL text editor. Paste the code shown in Figure 18. Click on Execute to run the code, and insert the call into the database.

insert call into database
Figure 18 - Insert call into database

Phone called from database

Here you can see that the phone is being called from the database (Figure 19).

phone called from database
Figure 19 - Phone called from database

Summary

This is the end of the tutorial. You are now able to send SMS using SQL Anywhere and Ozeki Phone System. We hope you found the information presented in this article helpful. For more information on integrating SQL into Ozeki Phone System and our various different solutions, check out our other guides too.

More information