Using ODBC in the core

From FreeSWITCH Wiki
Revision as of 16:41, 23 November 2010 by Nikko (Talk | contribs)

Jump to: navigation, search

Instead of using SQLite as the internal FreeSWITCH database, you can use an ODBC database. This works for.

Contents

Setup unixODBC

Ubuntu/Debian

For using unixODBC and MySQL you need to install.

 sudo apt-get install unixodbc-dev
 sudo apt-get install libmyodbc

The library path for the MySQL ODBC driver for the template file (see below) should be in:

 /usr/lib[64]/odbc/libmyodbc.so

CentOS 5.2

Install unixODBC and the MySQL ODBC Connector

yum install unixODBC-devel mysql-connector-odbc
  • Make symlink from /usr/lib[64]/libmyodbc3.so to /usr/lib[64]/libmyodbc.so
  • Run odbcinst -j. This will show you list of config files.
  • Uncomment the MySQL sample driver confirguration in /etc/odbcinst.ini.
  • Add the following with the correct information into your odbc.ini file located at /etc/odbc.ini
[maxpowersoft_odbc]
Driver   = MySQL
SERVER   = localhost
PORT     = 3306
DATABASE = myDatabase
OPTION  = 67108864
Socket   = /var/lib/mysql/mysql.sock

Note: If you are connecting your freeswitch server to a remote MySQL database, you can take out the last line from the above setting. Take off the "Socket = /var/lib/mysql/mysql.sock" line.

Note: OPTION allows you to set client specific FLAGS, in the example 67108864 (FLAG_MULTI_STATEMENTS) is set - See [1] for all flags. The number represents the addition of all flag numbers that you want enabled.

Note: On CentOS is OPTION (without S), instead of OPTIONS.

For some tips on setting up your dsn up in unixODBC see Mod_spidermonkey_odbc#unixodbcc

  • Test your ODBC setup by running the utility isql
    • isql maxpowersoft_odbc myUser myPass

FreeBSD

Install the following ports and follow instructions as detailed within the CentOS section

  • /usr/ports/databases/mysql-connector-odbc
  • /usr/ports/databases/unixODBC

Go to Mod_spidermonkey_odbc#General_Configuration for instructions to configure unixODBC

Create the FreeSWITCH Database

  1. Create a database in mysql or pgsql
  2. Create a user
  3. Do not worry about creating the actual tables. FreeSWITCH will take care of this part itself if they do not exist.

MySQL Client 4.x => MySQL Server 5.x

If you're getting an error about authentication method not being supported, use old password format in db.

UPDATE mysql.user
SET password=OLD_PASSWORD('somepassword')
WHERE user='someuser'
AND host='somehost';

PostgreSQL 8.4 on Debian Squeeze

Install PostgreSQL and ODBC

    apt-get install postgresql
    apt-get install odbc-postgresql

Create /etc/odbcinst.ini by command

    odbcinst -i -d -f /usr/share/psqlodbc/odbcinst.ini.template

Create /etc/odbc.ini Like this

 [tuxza]
 Description         = PostgreSQL Unicode
 Driver              = PostgreSQL Unicode
 Trace               = No
 TraceFile           = /tmp/psqlodbc.log
 Database            = tuxza
 Servername          = 127.0.0.1
 UserName            =
 Password            =
 Port                = 5432
 ReadOnly            = Yes
 RowVersioning       = No
 ShowSystemTables    = No
 ShowOidColumn       = No
 FakeOidIndex        = No
 ConnSettings        =

config switch.conf.xml

  <param name="core-db-dsn" value="tuxza:admin:pass" />

Compile FreeSWITCH with ODBC support

UnixODBC support will be autodetected by ./configure, and if found, will be compiled into FreeSWITCH.

Tell FreeSWITCH to use ODBC

Add or uncomment the following line in appropriate config file within <settings></settings>

<param name="odbc-dsn" value="your_dsn_name:your_db_user:your_db_password"/>

mod_sofia

Adding your odbc-dsn param setting under global_settings will NOT work. It has to be put within <settings></settings> in each sip profile.

sip_profiles/external.xml
sip_profiles/internal.xml
sip_profiles/internal-ipv6.xml

mod_limit

mod_voicemail

mod_lcr

Problems

Message: "mod_sofia: ODBC IS NOT AVAILABLE!" means "FreeSWITCH is compiled without ODBC support."

Message: "[unixODBC][Driver Manager]Data source name not found, and no default driver specified" Try linking your ODBC config files to your FreeSWITCH install directory.

Link ODBC*.ini to FreeSWITCH Directory

mkdir /usr/local/freeswitch/etc
ln -s /etc/odbcinst.ini /usr/local/freeswitch/etc/odbcinst.ini
ln -s /etc/odbc.ini /usr/local/freeswitch/etc/odbc.ini