unixODBC - MS SQL Server/PHP

December 2005


Contents

Introduction

This document will help unixODBC users access an MS SQL Server from a unixODBC equipped client. As an added feature, it will show you how to make this all work with php.

We will assume that you have a recent version of unixODBC installed on your system and that you have an MS SQL Server running somewhere. The SQL Server should allow remote access and support connections using TCP/IP. As of this writing, connections using named pipes are currently unsupported. Of course; you will want a login id and password.

Background

First; a bit of background. MS SQL Server was spawned by a partnership between Microsoft and Sybase. Subsequently these two SQL Servers are very similar. In fact; so similar that the communication protocol used by client software to communicate with these servers are virtually identical. This protocol is called Tabular Data Stream (TDS).

Brian Bruns started a open source project to provide an implementation of the TDS protocol to anyone who wanted it. This project is called FreeTDS.

Getting It

FreeTDS comes with several interfaces to TDS including ODBC. As of the previous version of this article, the features of the FreeTDS ODBC driver were limited, but I cannot speak of them at this point. I would imagine they have improved somewhat.

FreeTDS works well with unixODBC but you want to get a recent version. v0.63 is the most recent version as of writing. This document is primarily aimed at MSSQL Server 2000, which is the most popular version out there presently. For other versions, you may need to change the TDSVer flag when compiling FreeTDS and configuring unixODBC. Consult http://www.freetds.org/userguide/choosingtdsprotocol.htm#TAB.PROTOCOL.BY.PRODUCT for what TDS version corresponds to your server version. 8.0 is the TDS protocol version of MSSQL 2000.

Installing

The easiest way to get FreeTDS is through your distro's package manager, if one is available. I did this on a debian 3.1 unstable system. Unstable is a package release level of debian that corresponds to packages more stable than bleeding-edge, but not yet stable enough to be in their stable respository. To change from stable to unstable, simply edit your /etc/apt/sources.list file and change stable to unstable, except for the security source. As of writing, the debian package tdsodbc is in unstable. Downloading and installing this package will add the odbc driver for FreeTDS/Sybase and if you have debconf configured and unixODBC already installed (a prerequisite for this tutorial), it will register the driver entry with unixODBC. Your mileage may vary, consult your distro’s package repository if you are in doubt. If you are able to do this, you can skip to the last step of configuration, creating an odbc datasource name.

If your package managed doesn't have such a driver available, you have to download the FreeTDS source code and compile it. Usually installing from source is a great way to go but it is more involved for the average user.

# tar zxvf freetds-0.63.tar.gz
# cd freetds-0.63
unpacking

Once you have unpacked the source (typically using something like "tar zxvf freetds-0.63.tar.gz") you will find all of the information you need to "./configure", "make", and "make install" (as root) FreeTDS in the "INSTALL" and "README" files.

Start by setting some environment variables. For example; bash shell user can add the following to /etc/profile.

#
# TDS
#
SYBASE=/usr/local/freetds
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$SYBASE/lib
 
export SYBASE LD_LIBRARY_PATH
 
set environment variables for bash

The "configure" options can be viewed by executing "./configure --help". The main thing to take note of is the "--with-tdsver" configure option.


# ./configure --with-tdsver=8.0 --with-unixodbc=/usr/local
# make
# make install
Building FreeTDS

The FreeTDS ODBC driver should be installed in /usr/local/freetds/lib - look for libtdsodbc.so.

Configuration

Registering the ODBC Driver With unixODBC

unixODBC needs to know about all ODBC drivers you intend to use. The best way to accomplished this is using the ODBCConfig graphical program which comes with unixODBC. An alternative method is to use the 'odbcinst' command which also comes with unixODBC. We will focus on using the odbcinst command. Create a file named tds.driver.template with a few lines describing the driver.

[FreeTDS]
Description     = v0.63 with protocol v8.0
Driver          = /usr/local/freetds/lib/libtdsodbc.so
tds.driver.template

Execute odbcinst, telling it to install a driver entry using the tds.driver.template file. Note that you must leave a space between the '-f' switch and the template file name.


# odbcinst -i -d -f tds.driver.template
 
register ODBC driver

Creating an ODBC Data Source Name

ODBC client applications will typically work with ODBC Data Source Names (DSN). The best way to create, edit and remove a DSN is to use the ODBCConfig tool. Again we will use the odbcinst command instead. We will do this because the ODBCConfig program is fairly self explanatory and because not all users will have the unixODBC GUI tools installed. Again, we start by creating a template file - this one is called tds.datasource.template and contains some options such as the default database and UID. Note; you will want to use your own Server address.

[MSSQLTestServer]
Driver  = FreeTDS
Description     = Northwind sample database
Trace   = No
Server      = 192.168.1.25
Port      = 1433
Database        = Northwind
tds.datasource.template

Valid attributes for use in odbc.ini (or the connection string) can be found at: http://www.freetds.org/userguide/odbcconnattr.htm

[SybaseTestServer]
Driver  = FreeTDS
Description     = Test Sybase Database with FreeTDS
Trace   = No
Server      = 192.168.1.25
Port      = 5050
TDS Version      = 5.0
Database        = testdb
tds.datasource.template

Note: Do NOT use "Servername" in an ODBC-only configuration! Use of "Server" should be preferred over "Address" (see http://lists.ibiblio.org/pipermail/freetds/2004q2/016086.html)

Note; we have executed previous commands as root (denoted by leading '#' character on given commands) but here we execute the command as a regular user. This is significant. All users of the system share FreeTDS and the ODBC Drivers but each user has his/her own list of DSN's (view odbcinst output for help on registering as a system DSN available to all users). So create the DSN as the user who is going to be using it.


$ odbcinst -i -s -f tds.datasource.template
 
create ODBC data source

Now, to make this work with PHP is very easy. Assuming you have a package manager, download and install the odbc extension for PHP if it isn't already installed. If you don't have a package manager, you will have to compile odbc.so. To enable odbc in PHP, just modify the php.ini file, usually located in /etc. Add this line anywhere in the file (convention dictates that it should be placed with the other extension calls, so search your file for the word "extension").


extension = odbc.so
 
php.ini

Now, reload apache as root to make the changes effective.


# apachectl graceful
 
reloading apache as root

Test

unixODBC comes with a variety of tools which allow you to test. We will use the command line tool 'isql'. isql allows us to submit commands (typically SQL statements) to the DSN and see the results.


$ isql -v MSSQLTestServer
SQL>
 
test using isql
You should see a connected message and an SQL prompt. If this fails then you may have a configuration problem or you may simply be using the incorrect UserName and PWD.

Now try a simple SQL statement.


SQL> SELECT contactname FROM customers
 
simple sql statement

To make sure that PHP is able to communicate with your SQL server, paste this code into a file with a .php extension and place it in your apache document root.


<?php

# connect to a DSN "MSSQLTest" with a user "cheech" and password "chong"
$connect = odbc_connect("MSSQLTest", "cheech", "chong");

# query the users table for all fields
$query = "SELECT * FROM users";

# perform the query
$result = odbc_exec($connect, $query);

# fetch the data from the database
while(odbc_fetch_row($result)) {
$field1 = odbc_result($result, 1);
$field2 = odbc_result($result, 2);
print("$field1 $field2\n");
}

# close the connection
odbc_close($connect);

?>

 
test using odbc and PHP

Getting Help

Ok, so things went wrong somewhere... what do you do? Start by making sure you can access your server remotely over TCP/IP.

$ telnet 1433

You should see a blinking cursor to enter commands. If this works then the problem likely lies within the realm of what we covered here. If not, you may get some other message about connection reset or being unable to connect. This is related to the server itself or a connectivity issue (firewall, routing, etc.).

Check the FreeTDS folks and see if you can get an answer there. If FreeTDS seems be ok then check the unixODBC folks.





Peter Harvey
CodeByDesign


Updated By: Brian Morton
Nerd Happens