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.

Potential updates

This was taken from an IRC conversation which may have more relevant information:
6:24 < seanbright> lesouvage: basically. that page has a lot of unnecessary crap on it.
16:24 < seanbright> lesouvage: you don't need unixodbc to use freetds with asterisk
16:26 < seanbright> you install freetds, re-run asterisk's ./configure, make sure cdr_tds is selected in menuselect, rebuild
16:26 < seanbright> take a look at /usr/etc/freetds.conf, setup your connection there
16:26 < seanbright> then update /etc/asterisk/cdr_tds.conf to reflect the stuff in /usr/etc/freetds.conf
16:26 < seanbright> bing bam boom, you're done.

See also

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.

Potential updates

This was taken from an IRC conversation which may have more relevant information:
6:24 < seanbright> lesouvage: basically. that page has a lot of unnecessary crap on it.
16:24 < seanbright> lesouvage: you don't need unixodbc to use freetds with asterisk
16:26 < seanbright> you install freetds, re-run asterisk's ./configure, make sure cdr_tds is selected in menuselect, rebuild
16:26 < seanbright> take a look at /usr/etc/freetds.conf, setup your connection there
16:26 < seanbright> then update /etc/asterisk/cdr_tds.conf to reflect the stuff in /usr/etc/freetds.conf
16:26 < seanbright> bing bam boom, you're done.

See also

Created by: oej, Last modification: Mon 23 of Apr, 2012 (23:24 UTC) by admin
Please update this page with new information, just login and click on the "Edit" or "Discussion" tab. Get a free login here: Register Thanks! - Find us on Google+