Difference between revisions of "ODBC"

From SME Server
m (Test #1: adjusted output)
m (Test #1: minor mod)
Line 71: Line 71:
  yum remove gcc}}
  yum remove gcc}}
====Test #1====
====Test with tsql====
TSQL is a database client included in FreeTDS package for testing and troubleshooting.
TSQL is a database client included in FreeTDS package for testing and troubleshooting. "man tsql" give more info.
Check compile settings:
Check compile settings:

Revision as of 18:10, 18 June 2009

Warning.png Warning:
Under construction, some content may be totally wrong and misleading.

PythonIcon.png Skill level: medium
The instructions on this page require a basic knowledge of linux.


Adding support in SME Server for a universal connection to foreign datebase servers using Open Database Connectivity (ODBC).

This HowTo focus on building PHP support for ODBC.

Used packages

unixODBC - is an API that provides a common interface to many different databases. unixODBC official site


php-odbc - is simply an extension for PHP that allows you to use unixODBC to connect to database servers.

freetds - a re-implementation of the Tabular Data Stream protocol made by Sybase and Microsoft. FreeTDS official site

Optional: gcc - The GNU Compiler Collection includes front ends for C, C++, Objective-C, Fortran, Java, and Ada, as well as libraries for these languages. GCC official site


Install basic packages in server-manager, instructions here:




gcc.i386 (only needed if you are going to install from source)

Alt 1: Install FreeTDS precompiled

It save some work and you don't need gcc but usually contain an older version. Testing suggests it still works for SQL Server 2005.

Install FreeTDS from Dag repository, (you must add Dag repo first, instructions here):

yum --enablerepo=dag install freetds freetds-devel

If Dag repo does not work or you want another rpm package, this will do, modify path as needed:

wget http://dag.wieers.com/rpm/packages/freetds/freetds-0.64-1.el4.rf.i386.rpm
wget http://dag.wieers.com/rpm/packages/freetds/freetds-devel-0.64-1.el4.rf.i386.rpm
yum localinstall freetds-*.rpm

In any case, reconfigure the server afterwards:

signal-event post-upgrade; signal-event reboot

Alt 2: Install FreeTDS from source

This will give you full control and the latest version (or any version, modify path as you like).

wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.82.tar.gz
gzip -d freetds-0.82.tar.gz
tar -xvf  freetds-0.82.tar
cd freetds-0.82
./configure --sysconfdir=/etc --enable-msdblib --with-tdsver=8.0
make install
make clean
signal-event post-upgrade; signal-event reboot

Warning.png Warning:
Leaving gcc on a production server is considered a security risk, if you do not really need it you should not install it, if you need it you should seriously consider uninstalling gcc when you no longer need it. Removing gcc can be done using the following command:
yum remove gcc

Test with tsql

TSQL is a database client included in FreeTDS package for testing and troubleshooting. "man tsql" give more info.

Check compile settings:

tsql -C

Should output someting similar to this:

Compile-time settings (established with the "configure" script):
Version: freetds v0.64
MS db-lib source compatibility: yes
Sybase binary compatibility: unknown
Thread safety: yes
iconv library: yes
TDS version: 4.2
iODBC: no
unixodbc: yes

Test connection to a foreign database, overriding settings in freetds.conf (modify to suit your conditions):

tsql -S -U username -P password


Since these configuration files reside in /etc they may be overwritten by other server modifications. Therefore we use a templating system to make sure key information survives. More info here


This file contains the settings that tell freetds about each server it will connect to. Ip can (should) be replaced with FQDN. The original file contain examples. Make directory to hold template fragment to be included in the original file: /etc/freetds.conf

mkdir -p /etc/e-smith/templates-custom/etc/freetds.conf

Make and edit template fragment, "10xp3c" is my example and can be modified:

pico /etc/e-smith/templates-custom/etc/freetds.conf/10xp3c

Add your foreign database server (modify to suit your conditions):

# Microsoft SQL Server 2005 Express on a Win XP machine
       host =
       port = 1433
       tds version = 8.0  
       instance = db_kalle
expand-template /etc/freetds.conf


This file tells unixODBC about the FreeTDS driver. Its kind of like an Alias, e.g.

mkdir -p /etc/e-smith/templates-custom/etc/odbcinst.ini
pico /etc/e-smith/templates-custom/etc/odbcinst.ini/20sqlserver

Add driver path (modify to suit your conditions):

       Description   = Ms SQL Server access with FreeTDS driver
       Driver        = /usr/lib/libtdsodbc.so
expand-template /etc/odbcinst.ini


This file defines the DSN's (Data Source Names) for each ODBC connection. A DSN is simply a label for a given connection.

mkdir -p /etc/e-smith/templates-custom/etc/odbc.ini
pico /etc/e-smith/templates-custom/etc/odbc.ini/20sqlserver

Add text (modify to suit your conditions):

       Driver = FreeTDS
       Description = Connection to DB_KALLE on server xp3c
       Trace = No
       Server =
       Database = db_kalle
       Port = 1433
       TDS_Version = 8.0
expand-template /etc/odbc.ini

Check PHP

phpinfo(), section Configure Command, outputs: --with-unixODBC=shared,/usr There should also be a section "odbc".

PHP function odbc_connect (and related) should now work.

Additional information

Current forum discussion

FreeTDS user guide with troubleshooting section.

php.net ODBC

Input from, and credit to, Nick Critten unofficial copy of his HowTo and calio.it translated


Tab spaces in config files may cause troubles, use single space before and after =.

If your Ms SQL Server are using instances, add the line "instance = db_kalle" in your freetds.conf template fragment (replace db_kalle with the name of your instance).