Asterisk CDR with almost any database
There is a module called cdr_odbc that supports the unixODBC library. ODBC is a standard interface between an application and a database. The unixODBC library supports many databases, from MySQL and MS SQL Server to text files. The cdr_odbc also adds functionality for a more stable connection to the database: If the database connection is lost, cdr_odbc automatically reconnects.Install ODBC
First you need unixODBC installed. In the past, lots of people have had problems with use ODBC from RPMs, so when possible, please use the ODBC source package. You can install it with the --disable-gui if you don't have/want X.
On Debian or Ubuntu systems, the standard packages should work fine ("sudo apt-get install unixodbc").
unixODBC install instructions:
cd /usr/src
tar zxf unixODBC-2.2.7.tar.gz
cd unixODBC-2.2.7
./configure --disable-gui
make
make install
Using ODBC with MySQL
Now you need to configure your datasource. If you choose to use MySQL you need to install MyODBC (Debian: "apt-get install libmyodbc").MySQL ODBC Install Help
http://dev.mysql.com/doc/connector/odbc/en/faq_2.htmlMySSQL ODBC 3.51 Download Page
http://dev.mysql.com/downloads/connector/odbc/3.51.htmlIODBC (Required by MySQL ODBC)
http://www.iodbc.org/index.php?page=downloads/indexConfiguring ODBC
Basic odbcinst.ini:This is where you configure your various odbc drivers. On Debian systems, the drivers' entries will be created automatically when you install the distribution-supplied odbc driver.
[MySQL]
Description = MySQL ODBC MyODBC Driver
Driver = /usr/lib/libmyodbc3.so
FileUsage = 1
[Text]
Description = ODBC for Text Files
Driver = /usr/lib/libodbctxt.so
Setup = /usr/lib/libodbctxtS.so
FileUsage = 1
CPTimeout =
CPReuse =
[PostgreSQL]
Description = PostgreSQL driver for Linux & Win32
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1
[DB2]
Description = DB2 Driver
Driver = /opt/IBM/db2/V8.1/lib64/libdb2.so
FileUsage = 1
DontDLClose = 1
DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={DB2INSTANCE=db2inst1}
Note: you may have to change the path to the .so files depending on where they are installed.
Default location for Suse 10: /usr/lib/unixODBC
Basic odbc.ini:
This is where you configure your datasources.
[MySQL-asterisk]
Description = MySQL ODBC Driver Testing
Driver = MySQL
Socket = /var/run/mysqld/mysqld.sock
Server = localhost
User = username
Password = password
Database = database
Option = 3
#Port =
[Text-asterisk]
Description = ODBC for Text Files test
Driver = Text
Database = /root/flatfile
Trace = Yes
Tracefile = /root/trace.log
[PostgreSQL-asterisk]
Description = Test to Postgres
Driver = PostgreSQL
Trace = Yes
TraceFile = sql.log
Database = asterisk
Servername = localhost
UserName = username
Password = password
Port = 5432
Protocol = 7.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
[DB2-asterisk]
Database = astdb
Driver = DB2
Configuring Asterisk
cdr_odbc.conf:
[global]
dsn=MySQL-asterisk
username=username
password=password
loguniqueid=yes
Setting up the CDR Database/Table
Table Setup for Specific DB's:
Postgres:
CREATE TABLE cdr (
calldate timestamp with time zone NOT NULL default now(),
clid varchar(80) NOT NULL default '',
src varchar(80) NOT NULL default '',
dst varchar(80) NOT NULL default '',
dcontext varchar(80) NOT NULL default '',
channel varchar(80) NOT NULL default '',
dstchannel varchar(80) NOT NULL default '',
lastapp varchar(80) NOT NULL default '',
lastdata varchar(80) NOT NULL default '',
duration bigint NOT NULL default '0',
billsec bigint NOT NULL default '0',
disposition varchar(45) NOT NULL default '',
amaflags bigint NOT NULL default '0',
accountcode varchar(20) NOT NULL default '',
uniqueid varchar(32) NOT NULL default '',
userfield varchar(255) NOT NULL default ''
);
Mysql:
CREATE TABLE cdr (
calldate datetime NOT NULL default '0000-00-00 00:00:00',
clid varchar(80) NOT NULL default '',
src varchar(80) NOT NULL default '',
dst varchar(80) NOT NULL default '',
dcontext varchar(80) NOT NULL default '',
channel varchar(80) NOT NULL default '',
dstchannel varchar(80) NOT NULL default '',
lastapp varchar(80) NOT NULL default '',
lastdata varchar(80) NOT NULL default '',
duration int(11) NOT NULL default '0',
billsec int(11) NOT NULL default '0',
disposition varchar(45) NOT NULL default '',
amaflags int(11) NOT NULL default '0',
accountcode varchar(20) NOT NULL default '',
uniqueid varchar(32) NOT NULL default '',
userfield varchar(255) NOT NULL default ''
);
DB2:
CREATE TABLE cdr (
calldate timestamp NOT NULL default current timestamp,
clid varchar(80) NOT NULL default '',
src varchar(80) NOT NULL default '',
dst varchar(80) NOT NULL default '',
dcontext varchar(80) NOT NULL default '',
channel varchar(80) NOT NULL default '',
dstchannel varchar(80) NOT NULL default '',
lastapp varchar(80) NOT NULL default '',
lastdata varchar(80) NOT NULL default '',
duration bigint NOT NULL default 0,
billsec bigint NOT NULL default 0,
disposition varchar(45) NOT NULL default '',
amaflags bigint NOT NULL default 0,
accountcode varchar(20) NOT NULL default '',
uniqueid varchar(32) NOT NULL default '',
userfield varchar(255) NOT NULL default ''
);
.....More info coming.
See also:
- Asterisk | FAQ | Tips & Tricks

Comments
333HELP!!!!!!! how to connect SQL SERVER 2005 with Asterisk?
333starting asterisk with mysql-db
app_voicemail.so => (Comedian Mail (Voicemail System))
== Registered application 'VoiceMail'
== Registered application 'VoiceMail2'
== Registered application 'VoiceMailMain'
== Registered application 'VoiceMailMain2'
== Registered application 'MailboxExists'
== Parsing '/etc/asterisk/voicemail.conf': Found
— Logging into database with user test, password test, and database vmdb
Jul 5 23:26:40 WARNING20079: /var/tmp/portage/asterisk-1.0.8/work/asterisk-addons-1.0.8/mysql-vm-r:20 mysql_login: Error Logging into databaseJul 5 23:26:40 WARNING20079: app_voicemail.c:4366 load_module: SQL init
Jul 5 23:26:40 WARNING20079: loader.c:345 ast_load_resource: app_voicemail.so: load_module failed, returning -1
== Unregistered application 'VoiceMail'
== Unregistered application 'VoiceMail2'
== Unregistered application 'VoiceMailMain'
== Unregistered application 'VoiceMailMain2'
== Unregistered application 'MailboxExists'
Jul 5 23:26:40 WARNING20079: loader.c:440 load_modules: Loading module app_voicemail.so failed!
Ouch ... error while writing audio data: : Broken pipe
asterisk asterisk #
333PostgreSQL should use text field
333Nick Gorham
333MSSQL CDR files: /etc/odbc.ini /etc/odbcinst.ini
FreeTDS
Description = FreeTDS ODBC driver for MSSQL
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/libtdsS.so
FileUsage = 1
/etc/odbc.ini
MSSQL-asterisk
description =
driver = FreeTDS
server = localhost
port = 1433
database = asterisk
username = username
password = password
tds_version = 7.0
language = us_english
trace = yes
tracefile = /root/mssql.trace
I am working on geting asterisk to store CDR records in an MSSQL database. If you need any more information, please feel free to email me at dcox@illicom.net
333Mysql syntax is incorrect
"It is not possible to create a column with a default value which is a function or expression, such as NOW()"
Other sources suggest using a timestamp field, which of course doesn't give you a readable date, just a large integer...