Short version:

1. Install unixODBC
2. Install FreeTDS
3. Configure /etc/odbc.ini to set up "DSNs"
4. Create and test connection in PHP (recommended to use PEAR's DB Abstraction Class - just specify the DSN as the $dbhost variable in PEAR's connection string)
5. Enjoy.
   

Long version:

Installing unixODBC

UnixODBC allows us to use the open standard of ODBC on Unix machines. For this step, we follow pretty much the standard source installation steps. Download the source code from www.unixodbc.org, un-tar and un-zip the file, and the install from source with "./configure; make; make install" (you will need to be root to perform the last step). The most important file from our perspective from this installation is the /etc/odbc.ini configuration file. We'll come to that later.

Installing FreeTDS

FreeTDS is a set of libraries that allows us to talk natively to Sybase and SQL Server databases from a Unix/Linux machine. To quote from the FreeTDS website:

Technically speaking, FreeTDS is an open source implementation of the TDS (Tabular Data Stream) protocol used by these databases for their own clients.

Pretty much the same as above applies: download, un-tar and un-zip, and then install with "./configure; make; make install". The most important part of this installation for our purposes is the location of the libtdsodbc.so file. In a standard installation (as above) it's dropped into /usr/local/lib/libtdsodbc.so. You'll see why we need this location in a moment.

Configure /etc/odbc.ini

Now that we have the elements in place, it's time to configure our connection and test it out! Exciting times, indeed. Personally, I learn best by example, so here's an example of an entry in my /etc/odbc.ini (details have been changed to protect etc., etc., etc.):

[SQLServerTest]
Driver = /usr/local/lib/libtdsodbc.so
Server = sqlserver.mydomain.com
Database = test
Port = 1433
TDS_Version     = 7.0

Okay, so you're going to see immediately a few things you'll need to know to be able to configure this test connection correctly. First off, the location of the libtdsodbc.so file (hope you were paying attention earlier). Second is the hostname of your SQL Server. Can't help you with that one - if you don't have one to test, you've read a lot of this article for not much gain. You obviously have more time on your hands than you know what to do with - please call your nearest Open Source project and promptly volunteer all that time. Third is your connection details (database name and port - username and password will be specified in the PHP connection), and lastly is the TDS_Version. You may need to experiment with this one to work out which one is most appropriate, but I'm using version 7.0 to connect to SQL Server 2000.

Once you've configured a test connection, let's test it out. Fire up the isql command line utility that was installed with unixODBC. Most likely it's been dropped into /usr/bin, so it'll be in your path and you can just type "isql". If not, you may need to search around for it and enter the full path. Running "isql" will drop you into a help utility. To actually get ourselves to a SQL prompt type "isql -v SQLServerTest sa mypassword". If you've swapped out my info for yours, you should now be connected. Congratulations!!

Create and test connection in PHP

So to continue in our series of examples, I'm going to provide an example showing how to access the above mentioned connection through PHP. This example uses the PEAR DB Abstraction layer.

$dbtype="odbc";
$dbuser="sa";
$dbpass="mypassword";
$dbhost="SQLServerTest";
$dbname="test";

$dsn="$dbtype://$dbuser:$dbpass@$dbhost/$dbname";

require_once('DB.php');

class Database extends PEAR {

        var $db;

        function Database(){

                global $dsn;
                $this->db=DB::connect($dsn);
                if (DB::isError($this->db)){
                        die ($this->db->getMessage());
                        }else{
                        $this->db->setFetchMode(DB_FETCHMODE_ASSOC);
                        }
                }

        }

$conn = new Database();

$sql = "SELECT test FROM test";

$result = $conn->db->query($sql);

while ($row = $result->fetchRow()){
         echo "Test: ".$row["test"]."<br>\n";
         }

Now this isn't production code, as likely most sections of this would be separated out into separate files, but you get the idea. Basically I'm specifying my connection parameters, creating a class to connect to the database using PEAR's DB Abstraction Layer, and then instantiating an instance of that class and creating and running a query against my SQL Server database.

Voila!!

Useful links:

FreeTDS.org
UnixODBC.org

Comments

Post a comment

Your name:

Comment: