login | register
Sat 17 of May, 2008 [06:35 UTC]

voip-info.org

Navigation
Today's Deals
Recently Visited Pages
Search with Google
Search this site with Google. Results may not include recent changes.
 
Google Ads
Last Changes
Shoutbox
  • Juan Ortega, Thu 15 of May, 2008 [10:33 UTC]: Hi everybody, I'm Juan, an ITCom student, and I need to know what basic elements I need to create a VoIP network. Can anybody helpme, please?,Thank you very much
  • gineta, Wed 14 of May, 2008 [03:58 UTC]: any here not fine the configuration of firewall juniper -screem for VOIP asterisk????
  • Anoop Prabhakaran, Tue 13 of May, 2008 [12:16 UTC]: I am developing Asterisk IVR, Whenever i make a internation call to the IVR system, the DTMF is not getting detected properly, this happens only for the first time, second call onwards system works fine. why this is happening
  • joe, Mon 12 of May, 2008 [04:27 UTC]: Is there an opensource browser based softphone, or a system like Busta where everything is not manages through their website?
  • Nick Barnes, Fri 09 of May, 2008 [11:36 UTC]: Christopher - yesterday I tried an Asterisk install on a CentOS 5.1 box with stock GUI and it all worked fine. Sorry I can't help.
  • aero, Fri 09 of May, 2008 [08:20 UTC]: can someone help me out on this, i tried to play some sound files on my asterisk box and this is the error message i got. WARNING[4429]: format_wav.c:169 check_header: Unexpected freqency 22050 May 8 11:17:39 WARNING[4433]: codec_gsm.c:194 gsmtolin_fra
  • Christopher Faust, Thu 08 of May, 2008 [14:15 UTC]: I beleive that I may have to change something in the xserver configuration. Please advise
  • Christopher Faust, Thu 08 of May, 2008 [14:14 UTC]: Everything was perfect. In the bios I have increased the memory allocated Still receive input not supported on my display.
  • Christopher Faust, Thu 08 of May, 2008 [14:13 UTC]: This would not be my main box. I am doing some testing to see if I can install zaptel and asterisk 1.4 on a full centos 5.1 box with development software Its bizzare, because before I went through the asterisk and zaptel installation everything was perfe
  • Nick Barnes, Thu 08 of May, 2008 [13:44 UTC]: Christopher - I can't see any way in which an Asterisk installation would muck your GUI, but remember that it is advised not to use a GUI on an Asterisk box anyway.
Server Stats
  • Execution time: 0.43s
  • Memory usage: 2.20MB
  • Database queries: 34
  • GZIP: Disabled
  • Server load: 0.65

FreeTDS

What is FreeTDS?


Definition From the FreeTDS web page:

  • FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases.

Technically speaking, FreeTDS is an open source implementation of the TDS (Tabular DataStream) protocol used by these databases for their own clients. It supports many different flavors of the protocol and supports several APIs to access it. Additionally FreeTDS works with other software such as Perl and PHP, providing access from those languages as well. There is also a native (type 4) JDBC driver available for platform-independent Java clients (including Java Server Pages applications), with support for most of the JDBC 1 API and portions of the JDBC 2 API.


FreeTDS and Asterisk:


UnixODBC has been thrown around as a bright shiny button but it adds an
additional layer of DB abstraction. That additional layer of abstraction
can cause problems when it is not necessary for our TDS (MS SQL) usage. I
generally exchange data through as few hands as possible to eliminate
potential areas for problems.

For cdr_tds.c
Asterisk -> cdr_tds.c -> FreeTDS -> TDS Database

For cdr_unixodbc.c
Asterisk -> cdr_unixodbc.c -> unixODBC -> FreeTDS -> TDS Database
or
Asterisk -> cdr_unixodbc.c -> unixODBC -> (unixODBC driver for DB) ->
Database (Oracle, Sybase, MS SQL Server, MySQL, Postgres, etc...)

FreeTDS already has a unixODBC driver (libtdsodbc.so) so for our needs we
can use FreeTDS directly or through a additional abstraction with unixODBC.

Currently in Asterisk there is support for a CDR CSV (cdr_csv.c), cdr_mysql.c,
and I saw on the list a few weeks ago cdr_sybase.c

What would help the Asterisk community the most? Is there really a demand
out there for anything other than
MySQL
CSV flat file
TDS (Sybase and MS SQL Server)

I have never heard the words Oracle or Postgres mentioned often in Asterisk
so I am wondering if there is a justifiable need to support such systems.
I am nearly 100% sure that there should not be any support for Oracle as
supporting Oracle is diametrically opposite (i.e Supporting closed Source)
of what Open source revolution stands for. But so is Microsoft Product
SQL Server, is int it? --Seshu Kanuri

Our needs:
Asterisk to populate a CDR table in MS SQL Server

How to use FreeTDS for Asterisk CDR Storage to MS Sql Server?

MSSQL
Asterisk can currently store CDRs into an MSSQL database two different ways: cdr_odbc.c or cdr_tds.c

Call Data Records can be stored using unixODBC (which requires the FreeTDS package) cdr_odbc.c or directly by using just the FreeTDS package cdr_tds.c The following provide some examples known to get asterisk working with mssql. NOTE: Only choose one db connector.

ODBC cdr_odbc.c
Compile, configure, and install the latest unixODBC package:

tar -zxvf unixODBC-2.2.9.tar.gz &&
cd unixODBC-2.2.9 &&
./configure --sysconfdir=/etc --prefix=/usr --disable-gui &&
make &&
make install

Compile, configure, and install the latest FreeTDS package:

tar -zxvf freetds-0.62.4.tar.gz &&
cd freetds-0.62.4 &&
./configure --prefix=/usr --with -tdsver=7.0 \
--with-unixodbc=/usr/lib &&
make &&
make install


Compile, or recompile, Asterisk so that it will now add support for cdr_odbc.c
make clean &&
make update &&
make &&
make install

Setup odbc configuration files. These are working examples from my system. You will need to modify for your setup. You are not required to store usernames or passwords here.

/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 = Asterisk ODBC for MSSQL
driver = FreeTDS
server = 192.168.1.25
port = 1433
database = voipdb
tds_version = 7.0
language = us_english


Only install one database connector. Do not confuse asterisk by using both ODBC (cdr_odbc.c) and FreeTDS (cdr_tds.c). This command will erase the contents of cdr_tds.conf

-f /etc/asterisk/cdr_tds.conf > /etc/asterisk/cdr_tds.conf

NOTE: unixODBC requires the freeTDS package, but asterisk does not call freeTDS directly.

Setup cdr_odbc configuration files. These are working samples from my system. You will need to modify for your setup. Define your usernames and passwords here, secure file as well.

/etc/asterisk/cdr_odbc.conf

global
dsn=MSSQL-asterisk
username=voipdbuser
password=voipdbpass
loguniqueid=yes


And finally, create the 'cdr' table in your mssql database.

CREATE TABLE cdr (
calldate datetime NOT NULL ,
clid varchar (80) NOT NULL ,
src varchar (80) NOT NULL ,
dst varchar (80) NOT NULL ,
dcontext varchar (80) NOT NULL ,
channel varchar (80) NOT NULL ,
dstchannel varchar (80) NOT NULL ,
lastapp varchar (80) NOT NULL ,
lastdata varchar (80) NOT NULL ,
duration int NOT NULL ,
billsec int NOT NULL ,
disposition varchar (45) NOT NULL ,
amaflags int NOT NULL ,
accountcode varchar (20) NOT NULL ,
uniqueid varchar (32) NOT NULL ,
userfield varchar (255) NOT NULL
)


Start asterisk in verbose mode, you should see that asterisk logs a connection to the database and will now record every call to the database when it's complete.

TDS cdr_tds.c
Compile, configure, and install the latest FreeTDS package:

tar -zxvf freetds-0.62.4.tar.gz &&
cd freetds-0.62.4 &&
./configure --prefix=/usr --with-tdsver=7.0
make &&
make install


Compile, or recompile, asterisk so that it will now add support for cdr_tds.c (Currently only asterisk CVS supports cdr_tds.c)

make clean &&
make update &&
make &&
make install


Only install one database connector. Do not confuse asterisk by using both ODBC (cdr_odbc.c) and FreeTDS (cdr_tds.c). This command will erase the contents of cdr_odbc.conf

-f /etc/asterisk/cdr_odbc.conf > /etc/asterisk/cdr_odbc.conf

Setup cdr_tds configuration files. These are working samples from my system. You will need to modify for your setup. Define your usernames and passwords here, secure file as well.

/etc/asterisk/cdr_tds.conf

global
hostname=192.168.1.25
port=1433
dbname=voipdb
user=voipdbuser
password=voipdpass
charset=BIG5


And finally, create the 'cdr' table in your mssql database.

CREATE TABLE cdr (
accountcode varchar (20) NULL ,
src varchar (80) NULL ,
dst varchar (80) NULL ,
dcontext varchar (80) NULL ,
clid varchar (80) NULL ,
channel varchar (80) NULL ,
dstchannel varchar (80) NULL ,
lastapp varchar (80) NULL ,
lastdata varchar (80) NULL ,
start datetime NULL ,
answer datetime NULL ,
end datetime NULL ,
duration int NULL ,
billsec int NULL ,
disposition varchar (20) NULL ,
amaflags varchar (16) NULL ,
uniqueid varchar (32) NULL
)


Start asterisk in verbose mode, you should see that asterisk logs a connection to the database and will now record every call to the database when it's complete.

FAQ

  1. I Cannot Insert rows to SQL Server. How do I check what the problem is?
Ans: It's probably a database or SQL Server configuration problem. The module works, because I'm using it in production.

If you are using safe_asterisk to start up, put the following lines into /usr/sbin/safe_asterisk near the top, just after the DUMPDROP definition:

TDSDUMP=/tmp/tdsdump.$$.log
TDSDUMPCONFIG=/tmp/tdsdumpconfig.$$.log
export TDSDUMP TDSDUMPCONFIG

Then look for those files in the /tmp directory when asterisk is running.
They will contain a lot more debugging information from the FreeTDS libraries.

If you're not using safe_asterisk, then just call the above lines from your shell before you invoke asterisk.


See also


Created by oej, Last modification by iaxbox on Tue 19 of Jul, 2005 [20:59 UTC]

Comments

Comments Filter

HELP PLS !!!!!!!!!!!

by tafilaj on Thursday 02 of June, 2005 [00:40:42 UTC]
i went through all of the instructions, thank you , but
i cant wo the following
"
Compile, or recompile, Asterisk so that it will now add support for cdr_odbc.c
make clean &&
make update &&
make &&
make install

"

I cant get the asterisk to compile the crd_odbc.

on the CVS head i can only see the MySQL addon no odbc.

if anyone can give me a hand here would be greate


Thank you

Please update this page with new information, just login and click on the "Edit" or "Add Comment" button above. Get a free login here: Register Thanks! - support@voip-info.org

Page Changes | Comments

Sponsored by:
Terms of Service Privacy Policy
© 2003-2008 VOIP-Info.org LLC

Powered by bitweaver