How to make scripted calls from Postgre SQL

This article is a detailed guide on how to make scripted calls from Postgre SQL. 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 let's get started!

What is Postgre SQL?

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. PostgreSQL is used as the primary data store or data warehouse for many web, mobile, geospatial, and analytics applications. PostgreSQL can store structured and unstructured data in a single product.

What is a scripted call?

A call script, a written script entailing correct wording and logic, assists an agent in handling a contact. Call scripts can be easily integrated with telephone 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 Postgre SQL (video tutorial)

In this video tutorial you will leatn how to make scripted calls from Postgre SQL. For this process you need to install Postgre SQL and Ozeki Phone System. In this example, we are going to create a databas and make a call with this database using Ozeki Phone system. Follow along the steps presented in this video to implement this solution successfully. 

// *******************************************
// Create the database 
// *******************************************

CREATE ROLE ozekiuser WITH
    LOGIN
    SUPERUSER
    CREATEDB
    CREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1
    PASSWORD 'ozekipass';
		
CREATE DATABASE ozekidb
    WITH 
    OWNER = ozekiuser
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

// *******************************************
// Create the database table layout
// *******************************************
  
CREATE TABLE ozekicallsin
(
id serial,
sender varchar(255),
receiver varchar(255),
msg text,
senttime varchar(100),
receivedtime varchar(100),
operator varchar(100),
msgtype varchar(160),
reference varchar(100)
);
  
CREATE TABLE ozekicallsout
(
id serial,
sender varchar(255),
receiver varchar(255),
msg text,
callid varchar(255),
startime varchar(40),
endtime varchar(40),
status varchar(40),
talkduration varchar(40),
ringduration varchar(40),
operator varchar(100),
errormsg varchar(1024)
);

CREATE TABLE ozekicallscripts (
id serial,
msg text,
phonenumber varchar(40)    
);


// *******************************************
// 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 PostgreSQL installer

The first step in setting up this solution, is to download the PostgreSQL installer. Visit the official website here. Click on the download link for Windows 64-bit (Figure 1). This will start downloading the PostgreSQL installer to your browser's default download directory.

download postgresql installer
Figure 1 - Download PostgreSQL installer

Install database

Run the installer you have previously downloaded. Click Next on the welcome screen to proceed (Figure 2).

install database
Figure 2 - Install database

Define admin password

On this screen you will be asked to define the admin password. Enter your desired password into both of the textboxes (Figure 3). Click to Next continue.

define admin password
Figure 3 - Define admin password

Installation process

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

installation process
Figure 4 - Installation process

Installation finished

When the installation process is completed, click on Finish (Figure 5).

installation finished
Figure 5 - Installation finished

Create ozekiuser

Now that you have installed Postgre SQL, it's time to create a database user. This is done in pgAdmin. The entire code for the project can be found above. Paste the code shown in Figure 6 into the text editor. Run the code to create the database user.

create ozekiuser
Figure 6 - Create ozekiuser

Create database

Paste the code shown in Figure 7 into the text editor. Run the code to create the database.

create database
Figure 7 - Create database

Create ozekicallsin table

Paste the code shown in Figure 8 into the text editor. Run the code to create a table for incoming calls.

create ozekicallsin table
Figure 8 - Create ozekicallsin table

Create ozekicallsout table

Paste the code shown in Figure 9 into the text editor. Run the code to create a table for outgoing calls.

create ozekicallsout table
Figure 9 - Create ozekicallsout table

Create ozekicallsscripts table

Paste the code shown in Figure 10 into the text editor. Run the code to create a table for call scripts.

create ozekicallsscripts table
Figure 10 - Create ozekicallsscripts table

Create new serevice 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 11). Click on the blue Create new Extension button on the top. Click on the Service icon on the right.

create new serevice extension
Figure 11 - Create new serevice 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 12).

select sql call type
Figure 12 - Select SQL call type

Install PostgreSQL call extension

Find Postgre SQL Calls and click on Install next to it (Figure 13).

install postgresql call extension
Figure 13 - Install PostgreSQL call extension

Setup connection details

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

  • Server (IP address)
  • Port
  • Database
  • UserID
  • Password
Click on OK to create the connection.

setup connection details
Figure 14 - Setup connection details

Connect to PostgreSQL database

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

connect to postgresql database
Figure 15 - Connect to PostgreSQL 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 16). 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 16 - 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 17). 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 17 - Setup outside line connection

Create dial plan rule

he 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 18). 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. Set To: as voip_Phone_Line_1.

create dial plan rule
Figure 18 - Create dial plan rule

Insert call into database

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

insert call into database
Figure 19 - Insert call into database

Phone called from database

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

phone called from database
Figure 20 - Phone called from database

Summary

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

More information