ODBC
Description
Adding support in SME Server for a universal connection to foreign datebase servers using Open Database Connectivity (ODBC).
Used packages
unixODBC.i386 - is an API that provides a common interface to many different databases. unixODBC official site
unixODBC-devel.i386
php-odbc.i386 - 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
Installation
Install basic packages via server-manager:
unixODBC.i386
unixODBC-devel.i386
php-odbc.i386
Alt 1: Install FreeTDS precompiled
It save some work and you don't need gcc but usually contain an older version. Install FreeTDS from Dag repository:
yum --enablerepo=dag install freetds freetds-devel
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).
yum install gcc
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
yum install gcc
cd freetds-0.82
./configure --sysconfdir=/etc --enable-msdblib --with-tdsver=8.0
make
make install
make clean
signal-event post-upgrade; signal-event reboot
Test # 1
TSQL is a database client included in FreeTDS package for testing and troubleshooting. Check compile settings:
tsql -C
Should output someting similar to this:
Compile-time settings (established with the "configure" script) Version: freetds v0.82 freetds.conf directory: /usr/local/etc MS db-lib source compatibility: no Sybase binary compatibility: no Thread safety: yes iconv library: yes TDS version: 8.0 iODBC: no unixodbc: yes
Test connection to a foreign database, overriding settings in freetds.conf (modify to suit your conditions):
tsql -S 192.168.0.112 -U username -P password
Configuration
Since theese 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]
freetds.conf
This file contains the settings that tell freetds about each server it will connect to. Ip can (should) be replaced with FQDN.
mkdir -p /etc/e-smith/templates-custom/etc/freetds.conf 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
[xp3c]
host = 192.168.0.112
port = 1433
tds version = 8.0
instance = db_kalle
expand-template /etc/freetds.conf
odbcinst.ini
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):
[FreeTDS]
Description = Ms SQL Server access with FreeTDS driver Driver = /usr/lib/libtdsodbc.so
expand-template /etc/odbcinst.ini
odbc.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):
[sqlserver]
Driver = FreeTDS Description = Connection to DB_KALLE on server xp3c Trace = No Server = 192.168.0.112 Database = db_kalle Port = 1433 TDS_Version = 8.0
expand-template /etc/odbc.ini
Check PHP configuration
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
Input from and credit to Nick Critten unofficial copy of his HowTo and calio.it translated