Using ODBC in the core

From FreeSWITCH Wiki

Jump to: navigation, search

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

Obs.: You cannot set FreeSWITCH to use ODBC for db/core.db. This one is sqlite only.

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/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/libmyodbc3.so to /usr/lib/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

./configure --enable-core-odbc-support
make all install

Please note that as of 23 March 2009, the --enable-core-odbc-support flag is no longer required to compile in ODBC. This information is now autodetected.

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

sofia.conf.xml

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

limit.conf.xml

mod_voicemail

voicemail.conf.xml

mod_lcr

lcr.conf.xml

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
Retrieved from "http://wiki.freeswitch.org/wiki/Using_ODBC_in_the_core"
Personal tools
Community
Support FreeSWITCH