Direct access to MiaRec DB

MiaRec uses open architecture with ability to access MiaRec database directly from your web application.

Comparing to REST API a direct access to MiaRec database allows to implement advanced integration between your web application and MiaRec recorder. This will allow you to build complex SQL queries on MiaRec data and optimize these queries according to your scenarios of data usage (for example, you can create additional database indexes on specific columns to speedup search operations). Also direct access to MiaRec database provides access to internal data, which is not possible to retrieve via REST API.

MiaRec uses PostgreSQL as a database engine. PostgreSQL has many application interfaces for many monogramming languages, like C, C#, Java, Python, Ruby, PHP etc.

Check the following web-page to see a list of available interfaces to PostgreSQL:

Simplified diagram

Direct access to MiaRec DB

Detailed diagram

Direct access to MiaRec DB

See also: MiaRec Architecture

Access MiaRec DB via ODBC driver

MiaRec uses PostgreSQL as database engine. This article describes how to connect to PostgreSQL database via ODBC driver.

First of all, download ODBC driver and install in the same computer, where your application is running.

Here you will find a sample source code on C#  for accessing PostgreSQL database:

Please, note, MiaRec database (PostgreSQL) is listening on port 6082 (by default, but this may be changed during installation of MiaRec).
Database name: miarec
Database user: miarec
Password: password

If you are running your application on different computer than MiaRec is running on, then you need to do additional changes inside MiaRec configuration.
By default, only local applications can access MiaRec Database. Access to database via network is forbidden.

In order to access MiaRec Database (PostgreSQL) from another computer (non-localhost), you need to make two things:

  1. Edit file pg_hba.conf (located in C:\Program Files\MiaRec Business\Data\DB)
  2. Edit file postgresql.conf

In the first file you need to add ip-address of another computer, from where you do access of database.
For example:

host    all         all          trust

Of course, you can allow access to whole your network, like:

host    all         all          trust

In the second file you need to find following string:

#listen_addresses = 'localhost'               # what IP address(es) to listen on;

By default it is commented (see hash symbol #).
You need to uncomment it and change ‘localhost’ to ‘*’, like:

listen_addresses = '*'                # what IP address(es) to listen on;

Save the file and restart “MiaRec DB” service.

After that you will be able to access database from other computers.

Now you need to configure ODBC Data Source on your computer.

Open "ODBC Data Source Administrator" in Control Panel->Administration.

Note for 64-bit systems:

If you are running 64-bit operating system, then you need to start "ODBC Data Source Administrator" manually from here: C:\Windows\SysWOW64\odbcad32.exe

PostgreSQL itself is 32-bit and ODBC driver is also 32-bit. So, you need to open 32-bit version of "ODBC Administrator".

Inside "ODBC Data Source Administrator" select tab "User DSN" and click on button "Add.." (see below screenshot).


Find in the list "PostgreSQL Unicode", select it and click on "Finish" button (see below screenshot).

ODBC New Data Source

You will see new dialog, where you will need to enter settings of your ODBC connection (see below screenshot).

"Data Source" is a name of this connection. You can type here any value, for example, "MiaRecDatabase".

"Database" should have value "miarec" (without quotes).

"Server" is ip-address of the server, where MiaRec is running, for example, or "localhost", if you start Excel and MiaRec on the same machine.

"User Name" is "miarec" (without quotes).

"Port" is 6082 (this is a default port. It may be changed during installation of MiaRec).

"Password" is "password" (without quotes).

You can click on "Test" button to check, if connection of database is working.
Then click on "Save" button.

PostgreSQL ODBC Driver Setup

Now you can use this ODBC connection for accessing MiaRec Database from your application.

There are many tables inside MiaRec Database. But for you only one table is a point of interest. It is ‘calls’ table. In this table MiaRec stores information about every recorded call.

Audio files themselves are stored on disk rather than inside database. The database contains only a path to that file (for example, C:\Program Files\MiaRec Business\Data\Recordings\20090127\20090127100507.mp3).

Connect to MiaRec DB from Excel

You can connect from Excel to MiaRec Database and export all call records to spreadsheet. This will allow you to do some analyzing of data with the help of Excel.

This article contains step by step instructions for importing call details from MiaRec Database to Excel. These instructions are written for Excel 2007, but it is possible to use also other versions of Excel.

Excel will connect to MiaRec Database via ODBC driver. You need to create ODBC connection according to instructions in following article:

Access MiaRec DB via ODBC driver

When ODBC connection is created, you are ready to configure Excel.

Open Excel and select "Data" tab, then click on button "From Other Sources". Then select from drop-down list "From Data Connection Wizard" (see below screenshot).

Excel ODBC configuration

You will see new dialog (see below screenshot). Select "ODBC DSN" and click "Next".

Excel ODBC configuration

On the next step you will see a list of available ODBC connections. One of these connections should be our new one ("MiaRecDatabase"). Select it and click "Next".

Excel ODBC configuration

Now you will see the list of tables from MiaRec database. You should select only "calls" table and click "Next". Table "calls" contains information about all recorded calls. All other tables contain data, which is used internally by MiaRec.

Excel ODBC configuration

Now Excel will ask you to save this connection in a file, so, you will be able to use it later.

Excel ODBC configuration

On the next step you will see "Import Data" dialog. You can choose simple "Table" view or "PivotTable Report" or even "PivotChart and PivotTable Report". You should read a documentation about Pivot Table inside Excel help. They are very powerful for analyzing data.

In this example we choose "Table" view.

Excel ODBC configuration

On below screenshot you will see how data is looking inside  Excel.

Excel ODBC configuration

There are many fields in "calls" table. Some of them may be not interested for you. The most important are:

  •     setuptime
  •     disconnecttime
  •     callernumber
  •     callername
  •     calleenumber
  •     calleename
  •     filename

There is no "duration" column, because it can be automatically calculated as "disconnecttime" minus "setuptime". You can add new column in Excel and enter such formula there. Change a Display Format for this column to "Time" and you will see a duration of every call.