ODBC

Languages:

English

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

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

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 --prefix=/usr --enable-msdblib --with-tdsver=8.0

make

make install

make clean

signal-event post-upgrade; signal-event reboot

	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 something 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 -H 192.168.0.112 -p 1433 -U dbuser -P dbpass

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 'dbuser'.
Msg 2002, Level 9, State -1, Server OpenClient, Line -1
Adaptive Server connection failed
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. Host should be a FQDN (Fully Qualified Domain Name). Make directory to hold template fragments to be included in the original path: /etc/freetds.conf

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

Copy the original file to be included as a template fragment when we regenerate the file:

cp /etc/freetds.conf /etc/e-smith/templates-custom/etc/freetds.conf/10original

Make additional fragment for your foreign server, "20xp3c" is my example name and can be modified:

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

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

# Microsoft SQL Server 2005 Express on a Win XP machine
[remoteserver]
host = xp3c
port = 1433
tds version = 8.0

Exit with ctrl-x, save with y and enter. Regenerate the complete file:

expand-template /etc/freetds.conf

The generated file can now be viewed with:

pico /etc/freetds.conf

If you want to edit the text, edit the template fragments, not this file.

odbcinst.ini

This file tells unixODBC about the FreeTDS driver.

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

cp /etc/odbcinst.ini /etc/e-smith/templates-custom/etc/odbcinst.ini/10original

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

cp /etc/odbc.ini /etc/e-smith/templates-custom/etc/odbc.ini/10original

pico /etc/e-smith/templates-custom/etc/odbc.ini/20xp3c

Add text (modify to suit your conditions):

[remoteserver]
Driver = FreeTDS
Description = Connection to DB_KALLE on server xp3c
Trace = No
Server = xp3c
Database = db_kalle
Port = 1433
TDS_Version = 8.0

expand-template /etc/odbc.ini

Hosts

If the DNS does not resolve the ip of the machines running the remote SQL server (try ping the host name), add them to your hosts file (ip and change the machine name ...):

mkdir -p /etc/e-smith/templates-custom/etc/hosts 

pico /etc/e-smith/templates-custom/etc/hosts/20xp3c

192.168.0.112  xp3c

expand-template /etc/hosts

Test again

tsql

Test connection, using input in freetds.conf:

tsql -S remoteserver -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

isql

At the command line enter the command isql -v DSN USERNAME PASSWORD e.g.:

isql -v remoteserver dbuser dbpass

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

If you do not get output similar to the above, then check your config files, pay special attention to upper/lowercase, for example XP3C is not the same as xp3c!

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

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

Troubleshooting

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

Ms SQL Server

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

From SQL Server 2000 TDS version 8.0 should be used. Add the line "tds version = 8.0" in your template fragment for freetds.conf

Ms SQL Server might not allow the db system user "sa" to connect from outside the server, create another account with proper permissions.

Express edition have remote access disabled by default. Can be changed in the menu "Surface Area Configuration". More info

---