How to connect your database to Ozeki Phone System?

On this page you will find a detailed installation guide on how to configure Ozeki Phone System to connect MySQL Server. By following this guide your Ozeki Phone System can store data in the previously configured MySQL database.

Before you start to create an SQL API, you need to prepare your SQL server to store data.

  • On the ">Installing MySQL Server page you can find information about how you can install a MySQL server and ODBC driver for your SQL API.
  • On the ">Configure MySQL Database page you can find information about how to create a database for the SQL API.
  • And finally, on the ">Setup SQL Database page you can find information about the database tables that are necessary for different SQL APIs.

The SQL API guide consists of two parts:

  1. First steps
  2. Setting up database connection
  3. Setting up SQL Template
  4. Logging information

id="first">1. First steps

In order to create an SQL API in the Ozeki Phone System, first open a web browser, load the home page and login with your username and password (Figure 1).

login page
Figure 1 - Login page

On the main page select the Productivity and then the SQL API buttons (Figure 2).

main page
Figure 2 - Main page

On the next page, click on the Install button on the right side of the screen (Figure 3).

sql api page
Figure 3 - SQL API page

On the next page, click on the Install button next to the SQL API you would like to install (Figure 4).

  • Authenticator: Creates a database connection, from which the SIP registrations will be queried.
  • Logger: System logs to the database.
  • Call reporter: Call reporting to the database.
  • Database extension: SMS sending/receiving from database. The configuration of the Database extension SQL API can be found on the ">Setup SMS messaging web page.

sql api types
Figure 4 - SQL API Types

2. Setting up database connection for Authenticator, Logger and Call Reporter SQL API

If you select the Authenticator, Logger or Call reporter SQL API, you will need to give the following data (Figure 5):

  • Connection name: The name of the SQL API.
  • Data source: ODBC (it requires an "> installed ODBC driver to the MySQL server).
  • Connection string: It indicates the type of the ODBC driver, the server's address, the name of the database, the username and the password for the MySQL server.

Connection string example:

Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=OzekiPBX;User=root;Password=;Option=4;

database connection for authenticator logger or call reporter
Figure 5 - Database connection for Authenticator, Logger or Call reporter

3. Setting up SQL Templates for Authenticator, Logger and Call Reporter SQL API

Under the SQL template menupoints, you will see different screens according to the type of the SQL API.

Figure 6 shows the SQL template that you need to specify to be used for SQL authentication.

sql template for authenticator
Figure 6 - SQL Template for Authenticator

The following example can be a possible row in the `ozpbxusers` table:

  • username: 102
  • authname: 102
  • password: 102

In this case, you can connect any SIP based phone to the Ozeki Phone System with these data and you don't have to add a new SIP extension to the system. The PBX will authenticate the phone by comparing the database data and the data from the SIP phone.

Figure 7 shows the SQL template that you need to specify to be used for SQL call reporter.

sql template for call reporter
Figure 7 - SQL Template for Call reporter

When a call is initiated, a new row will be insterted into the ozpbxsessions, ozpbxcalls and ozpbxcallstates table.

The structure of the Session creating SQL template:

  • The SessionID is a unique identifier for the call session.
  • The Time shows when the call is initiated.
  • The CallInitiator shows the extension phone number of the caller.

The structure of the Call creating SQL template:

  • The SessionID is a unique identifier for the call session.
  • The CallID is a unique identifier for the call.
  • The Time shows when the call is initiated.
  • The Caller shows the extension phone number of the caller while the Callee shows the extension phone number of the receiver.

The structure of the Call state changing SQL template:

  • The CallID and the Time are the same than in other tables.
  • The Duration is the length of the session.
  • The State shows the state of the calling, such as Setup, Ringing or InCall.

The Session creating SQL template uses the code in the box to create a new row when a call is initiated by somebody. The session-id is the identifier for the call, the time shows when the call is happened and the callinitiator shows the extension phone number of the caller.

Figure 8 shows the SQL template that you need to specify to be used for SQL logger.

sql template for logger
Figure 8 - SQL Template for Logger

The Logger SQL API inserts a new entry into the ozpbxlog according to the System log.

4. Logging information

In the following pictures you can see different logging information according to the type of the SQL API. You can see in the pictures that every API connected successfully to the database.

  • Figure 9: Log of the Authenticator SQL API
  • Figure 10: Log of the Logger SQL API
  • Figure 11: Log of the Call reporter SQL API

authenticator sql api
Figure 9 - Authenticator

The Figure 9 shows that an extension found in the database.

logger sql api
Figure 10 - Logger

The Figure 10 shows that the logging information is inserted into the database.

call reporter
Figure 11 - Call reporter

The Figure 11 shows that the Ozeki Phone System inserts different data into the database tables.

This guide demonstrated how to setup SQL API in the Ozeki Phone System effectively.

If you have any questions or need assistance, please contact us at info@ozekiphone.com.

More information