ODBC
Description
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
unixODBC-devel
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
Installation
Install basic packages in server-manager, instructions here:
unixODBC.i386
unixODBC-devel.i386
php-odbc.i386
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
make install
make clean
signal-event post-upgrade; signal-event reboot
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, ip could be replaced with FQDN):
tsql -S 192.168.0.112 -U username -P password
A response something like this would indicate that connection was successful but login failed (wich is normal at this stage):
locale is "sv_SE" locale charset is "ISO-8859-1" Msg 18456, Level 14, State 1, Server XP3B\SQLEXPRESS, Line 1 Login failed for user 'testuser'. Msg 20014, Level 9, State 0, Server OpenClient, Line 0 Login incorrect. There was a problem connecting to the server
Configuration
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
freetds.conf
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
[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 = Access Ms SQL Server 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
Test again with tsql
Test connection, using input in freetds.conf:
tsql -S xp3c -U testuser -P testpass
This should output something like this:
locale is "sv_SE" locale charset is "ISO-8859-1" 1>
1> indicate that you are connected, logged in and the server are ready for SQL commands. Exit tsql console with:
quit
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.
Sample php code to check above could be (add standard html code):
if (function_exists('odbc_connect'))
{ echo "Function odbc_connect exists!"; }
else { echo "Function odbc_connect does NOT exist!"; }
and
phpinfo();
Additional information
FreeTDS user guide with troubleshooting section.
Input from, and credit to, Nick Critten unofficial copy of his HowTo and calio.it translated
Troubleshooting
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).