Course 3 / Lecture 5

How to build an IVR with Ozeki VoIP SIP SDK

How to create IVR with database integration

download icon Download: ivr-database.zip

This article is a detailed guide on how you can connect your multi-level IVR system to a database and how you can create a web-based configuration interface to this. You can find out more about a basic and a multi-level IVR system that uses XML code in the previous example. If you click on the following link, you can find the full description of the basic and multi-level IVRs example code. Both of these examples are written in C#.


Detailed guide for the C# and PHP implementation

The present sample is a version of the sample program, which can be found at the How to develop an IVR system written in C# by using XML code, extended with the option of querying the database.
That is why here you will find details regarding only this option.

In this sample, by using a simple website, an IVR XML script can be uploaded to (and this way modified in) a database.
The use of this language enables you to create IVR trees. In addition, you can respond to the incoming DTMF signals as you wish (for instance, you can navigate the menu simply with the help of pushing the buttons of your phone). Using a website, you can easily, with only a little programming knowledge, configure your IVR. Afterwards, with Ozeki VoIP SIP SDK, the IVR system that was written in C# can read the given IVR XML Script from the database to create the IVR tree. As a result, in the case of incoming calls, the XML script you set on the website will run.

The website will be a php file of minimalistic structure (Figure 1), since it will only have the functionalities necessary for the demontstration of how it operates. It will have a form with which the IVR XML can be sent to the database and as feedback, the contents of the current database will be shown. When the database is changed, i.e. somebody sends the form, the user will receive a short message saying that the modification was successful.

the basic webpage in php
Figure 1 - The basic webpage in PHP

If you want to write a database support for an IVR solution, you need to decide on the database type to use. If you want to use a local database provided by Visual Studio 2012, you need to know that the possibilities of that solution are limited.
The present sample makes use of MySQL database.

For creating a database and for deploying the website, an XAMPP or a WAMPP is needed.

IVR database integration example with Ozeki VoIP SIP SDK using C#

In order for you to be able to connect to a MySQL database using C#, a MySQL Connector is necessary. Such a connector can be downloaded from the website of ORACLE. After downloading it, you should install it and then add MySQL.Data.dll, in a similar manner as you add the .dll of Ozeki VoIP SIP SDK, to the project references of Visual Studio.
If you did all this, you are now able to add the MySQL Connector library to the class of your choice in the following way: 

using MySql.Data.MySqlClient;

In the present sample it is a DBConnect class that carries out the tasks related to the database. In this class variables should be created to store the data which is necessary for accessing the database. For example, the address of the server, the names and passwords of the user and the database and the MySQLConnection object itself are such data. The Program.cs class requests these data from the user with the help of the DatabaseAccountInitialization() method. The construcor of the DBConnect class initializes the connection by calling the Initialize() method. 

private void Initialize()
{
	server = Program.server;
    database = Program.databName;
    uid = Program.user;
    password = Program.password;
    string connectionString;
    connectionString = "SERVER=" + server + ";" + "DATABASE=" +
    database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

    connection = new MySqlConnection(connectionString);
}

At this point, you should provide the data necessary for establishing the connection. You can write a connection string using the above mentioned data. With this connection string, you will be able to connect to your database.

Two other methods are needed in order for you to be able to open and close the database connection: 

//open connection to database
private bool OpenConnection()
{
	try
    {
    	connection.Open();
        Console.WriteLine("DataBase opened!");
        return true;
    }
    catch (MySqlException ex)
    {
    	//0: Cannot connect to server.
        //1045: Invalid user name and/or password.
        switch (ex.Number)
        {
        	case 0:
            	Console.WriteLine("Cannot connect to server.");
                break;

            case 1045:
                Console.WriteLine("Invalid username/password, please try again.");
                break;
        }
        return false;
     }
}

//close connection
private bool CloseConnection()
{
	try
    {
    	connection.Close();
        return true;
    }
    catch (MySqlException ex)
    {
        Console.WriteLine(ex.Message);
        return false;
    }
}

Afterwards, the connection should be established.

The Select() method can be used for executing queries from the database. This method returns the read string, since in this sample there is only one line in the database in which line the XML script can be found. 

public string Select()
{
	string query = "SELECT * FROM " + Program.table;

	//Create a string to store the result
    string ivrXml = null;

    //Open connection
    if (this.OpenConnection() == true)
    {
    	//Create Command
        MySqlCommand cmd = new MySqlCommand(query, connection);
        //Create a data reader and Execute the command
        MySqlDataReader dataReader = cmd.ExecuteReader();

        //Read the data and store in the string
        while (dataReader.Read())
        {
        	ivrXml = dataReader.GetString(0);
        }

		//close Data Reader
        dataReader.Close();

        //close Connection
        this.CloseConnection();

		//return string to be displayed
        return ivrXml;
    }
    else
    {
    	return ivrXml;
    }
}

You can replace the IVR XML script with querying from the database using the Select() method in the ReadXML() method of Program.cs class, in which the ivrXml string was provided until now. 

private static Menu ReadXML()
{
	DBConnect database = new DBConnect();
	string ivrXml = @"" + database.Select();

	var menu = new Menu();
	MenuLevel(ivrXml, menu);
	return menu;
}

By now, the program you wrote in C# is ready for collaboration with the database.

Webpage in PHP

The website is made up of a PHP file, a few js and css file for formatting.

The HTML determining the structure of this website contains a form with which you can change your database access and another form with which you can send the IVR XML script to the database. This html also has a div, in which the present contents of the database can be seen. If you click the Create/Select button, the sample program will attempt to access your database using your present settings. When the access is granted but the given database and its corresponding table have not been created yet, they will be. Afterwards, a sample IVR XML script will be placed into the table. (If you provide an already existent database and table, the sample script will just be uploaded.) You can modify this script by writing your own IVR XML script to the IVR XML Script field and hit the Submit button. 

	<h1><span style="color: red;">OZEKI</span> VoIP SIP SDK example page</h1>
	<h2>IVR with MySQL database</h2>
	<body>
		<div id="info">
			<form action="#" method="post" id="postform">
				<div id="DBContainer">
					<div id="dataInfo">
						<p>Information about access to your MySQL database:</p>
						<b>Username:</b> <?php echo $_SESSION['username'];?><br>
						<b>Password:</b> <?php echo $_SESSION['passwd'];?><br>
						<b>Server address:</b> <?php echo $_SESSION['server'];?><br>
						
						<b>Database name:</b> <?php echo $_SESSION['database'];?><br>
						<b>Table name:</b> <?php echo $_SESSION['table'];?><br>
					</div>
					
					<div id="dataTable">
						<p>You can change the access data if you want, just fill any textbox and click to Change button:
						<table>
							<tr>
								<td><p>Username: </p></td>
								<td><input type="text" name="username" /></td>
							</tr>	
							<tr>	
								<td><p>Password: </p></td>
								<td><input type="text" name="passwd" /></td>
							</tr>	
							<tr>	
								<td><p>Server address: </p></td>
								<td><input type="text" name="server" /></td>
							</tr>	
							<tr>	
								<td><p>Database name: </p></td>
								<td><input type="text" name="database" /></td>
							</tr>	
							<tr>	
								<td><p>Table name: </p></td>
								<td><input type="text" name="table" /></td>
							</tr>
							<tr>
								<td><p></p></td>
								<td><input id="submitinfo" name="SubmitInfo" type="submit" value="Change"></input></td>
							</tr>
						</table>
					</div>
					<hr>
				</div>
			</form>
		</div>
		<div id="create">
			<p>If you can set your database connection you just click to the Create button and your database will be created.</p>
			<form action="#" method="post" id="postform">
				<input id="submitdb" name="DBSubmit" type="submit" value="Create/Select"></input>
			</form>
			<?php if(isset($_POST['DBSubmit'])) {createDatab();}?>
		</div>
		<div id="ozContainer">
			<div id="ozCode">
				<h2>Content of your database:</h2>
				<div id="xml">
					<pre id="xmlCode" class="brush: xml"><?php if(isset($res)) {echo $res;}?></pre>
				</div>
			</div>
			
			<div id="ozForm">
				<h2>IVR XML Script:</h2>
				<form action="#" method="post" id="postform">
					<textarea rows="10" cols="135" name="ivrxml"></textarea>
					<input id="submitbtn" name="BtnSubmit" type="submit" value="Submit"></input>
				</form>
			</div>
		</div>
		<div id="tutorial"><a href='http://www.voip-sip-sdk.com/p_574-ivr-database-integration-voip.html'>Online tutorial</a></div>
	</body>

In the PHP code, the data necessary for accessing the database are stored in SESSION variables so that the data remain valid as long as the browser is not closed by the user. 

<?php
	session_start();
	
	// Database connection submit form
		if(isset($_POST['SubmitInfo'])) {
			if($_POST['username']){
					$_SESSION['username'] = $_POST['username'];
			}
			if($_POST['passwd']){
					$_SESSION['passwd'] = $_POST['passwd'];
			}
			if($_POST['server']){
					$_SESSION['server'] = $_POST['server'];
			}
			if($_POST['database']){
					$_SESSION['database'] = $_POST['database'];
			}
			if($_POST['table']){
					$_SESSION['table'] = $_POST['table'];
			}
		} else {
			if(!isset($_SESSION['username'])) {
				$_SESSION['username'] = 'root';
			}
			if(!isset($_SESSION['passwd'])) {
				$_SESSION['passwd'] = '';
			}
			if(!isset($_SESSION['server'])) {
				$_SESSION['server'] = 'localhost';
			}
			if(!isset($_SESSION['database'])) {
				$_SESSION['database'] =  'ivrdata';
			}
			if(!isset($_SESSION['table'])) {
				$_SESSION['table'] = 'ivr';
			}
		}

Using these, you can connect to the MySQL database with the mysql_connect() function. This takes place when the user hits the Submit button on the website. If the attempt to connect to the MySQL database is unsuccessful, the cause of this issue will appear on the webpage. If the connection is successfully established, the XML script, which is posted through the form using the mysql_real_escape_string() function, should be placed into a variable. This function escapes special characters in a string for use in an SQL statement. Afterwards, this variable can be used in the SQL UPDATE command. This way, you can overwrite the content of your database with the new data (XML script) through the form. Then run the MySQL query to execute the modification.

Following the modification, the showHide() JavaScript function is called which shows the message saying the database has been updated. After this, an SQL SELECT query runs. With the help of this query, the modification appears in the Content of your database textbox. Since the XML script has special characters in it, the htmlspecialchars() function should be used. In the end, the connection should be closed. 

	// IVR submit form   
	   if(isset($_POST['BtnSubmit']))
			{
			$conn = mysql_connect($_SESSION['server'], $_SESSION['username'], $_SESSION['passwd']);
			
			if(! $conn )
			{
			  die('Could not connect: ' . mysql_error());
			}
			
			$xml = mysql_real_escape_string($_POST['ivrxml']);

			$sql = "UPDATE " . $_SESSION['table'] . " SET menu = '$xml' ";

			mysql_select_db($_SESSION['database']);
			$retval = mysql_query( $sql, $conn );
			
			if(! $retval )
			{
			  die('Could not update data: ' . mysql_error());
			}
			
			echo '<script type="text/javascript">'
				, 'showHide();'
				, '</script>';
				
			$sqlselect = "SELECT * FROM " . $_SESSION['table'];
			$result = mysql_query($sqlselect);
		
			while($row = mysql_fetch_array($result, MYSQL_BOTH)) {
			  $res = htmlspecialchars($row['menu'], ENT_QUOTES);
			}
			
			mysql_close($conn);
			}
	
	?>

In the present sample, a unique function (createDatab()) will serve the purpose of creating your database and table, if they do not exist. This function also uploads the IVR XML script sample to the table. This works in a manner quite simillar to the above mentioned one.

This means that it connects to your MYSQL database. If such a database does not exist, it will be created. Then, if necessary, the given table will be created as well. In this table a TEXT type "menu" field will be created to which the IVR XML script will be uploaded. Afterwards, it will run a SQL Select query. As a result, the content of the created table will appear on the website. 

	function createDatab() {
	  
		global $res;
		$ivrRaw = "<ivr>
			 <menu>
				 <init>
					 <speak>
							Introduce you the Interactive Voice Mail aka IVR example code written with Ozeki VoIP SDK.
							To get more information about Ozeki Ltd. and hear a sample mp3 song, please press button one.
							By pressing button two, you can listen an inform message
					 </speak>
					 <play>../../test.mp3</play>
				 </init>
				 <keys>
					 <key pressed='1'>
							<speak>
								   Ozeki Informatics Ltd. is a leading mobile messaging software vendor and Ozeki VoIP SIP SDK is 
								   an excellent software development kit that allows you to establish VoIP calls from your application 
								   easily and quickly. You do not need to have expert programming skills, with the most basic programming 
								   knowledge you will be able to create extraordinary VoIP solutions with this tool.
							</speak>
							<play>../../test.mp3</play>
					 </key>
					 <key pressed='2'>
							<speak>
								   You pressed button two. You did nothing.
							</speak>
					 </key>
					 <key pressed='3'>
							<menu>
								   <init>
										 <speak>You reached the lower menu.</speak>
								   </init>
								   <keys>
									   <key pressed='1'>
										   <speak>
											   You pressed button one at the lower menu level.
										   </speak>
									   </key>
								   </keys>
							</menu>
					 </key>
				 </keys>
			 </menu>
		</ivr>";
		
		// Connect to MySQL
		$con = mysql_connect($_SESSION['server'], $_SESSION['username'], $_SESSION['passwd']);
		if (!$con) {
			die('Could not connect: ' . mysql_error());
		}

		// Make $database the current database
		$db_selected = mysql_select_db($_SESSION['database'], $con);

		if (!$db_selected) {
		$sqlcreate = "CREATE DATABASE " . $_SESSION['database'];
		
			if (mysql_query($sqlcreate, $con)) {
				echo "Database " . "<b>" . $_SESSION['database'] . "</b>" . " created successfully!\n";
				
				// Select the created database
				$db_selected = mysql_select_db($_SESSION['database'], $con);
				
			} else {
				echo 'Error creating database: ' . mysql_error() . "\n";
			}
		}

		$sqlselect = "SELECT * FROM " . $_SESSION['table'];
		$result = mysql_query($sqlselect);
		
		if(empty($result)) {
            $sqltable = "CREATE TABLE " . $_SESSION['table'] . " (menu TEXT)";
			
			$sqlinsert = sprintf("INSERT INTO " . $_SESSION['table'] . " (menu) VALUES ('%s')", mysql_real_escape_string($ivrRaw));
		 
			$result = mysql_query($sqltable, $con);
			if (!$result) {
			  die('Error: ' . mysql_error() . "\n");
			}
			
			$insertresult = mysql_query($sqlinsert, $con);
			if (!$insertresult) {
			  die('Error: ' . mysql_error() . "\n");
			}
			
			echo "Table " . "<b>" . $_SESSION['table'] . "</b>" . " created successfully!\n";
			
			$result = mysql_query($sqlselect);
		}
		
		while($row = mysql_fetch_array($result, MYSQL_BOTH)) {
			  $res = htmlspecialchars($row['menu'], ENT_QUOTES);
			}
			
		mysql_close($con);
	  }

By now, you have a simple website for modifying the IVR XML script that is stored in a MySQL database.

Conclusion

This article introduced you the basic knowledge about IVR database integration and showed how Ozeki VoIP SIP SDK can help you to fulfill your wishes about this topic. If you have read through this page carefully, you already have all the knowledge you need to start on your own solution.

The full source code of this website together with the code of the IVR system written in C# can be found at the top of this website in a .zip file.

Related Pages

More information