Tag: mssql

This post is for anybody who runs a GNU/Linux server and needs to query a MSSQL database. This setup will work on Debian and its relatives. As it’s a dense mix of technologies, so I’ve included all of the details which worked for me.

An obvious note: Microsoft SQL is not an ideal choice of database to pair with a GNU/Linux server, but may be acceptable if you are writing something which needs to import some data from external application which has a better reason to be using it.

A command-line alternative to this setup would be sqsh, which will let you running scheduled queries without PHP, if that’s what you’re after.

Prerequisites

Once you have PHP, the required libraries can be fetched with:

sudo apt-get install unixodbc php5-odbc tdsodbc

MSSQL is accessed with the FreeTDS driver. Once the above packages are installed, you need to tell ODBC where to find this driver, by adding the following block to /etc/odbcinst.ini:

[FreeTDS]
Description=MSSQL DB
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
UsageCount=1

The path is different on platforms other than amd64. Check the file list for the tdsodbc package on your architecture if you lose track of the path.

The next step requires that you know the database server address, version, and database name. Add a block for your database to the end of /etc/odbc.ini:

[foodb]
Driver = FreeTDS
Description = Foo Database
Trace = Yes
TraceFile = /tmp/sql.log
ForceTrace = yes
Server = 10.x.x.x
Port = 1433
Database = FooDB
TDS_Version = 8.0

Experiment with TDS_Version values if you have issues connecting. Different versions of MSSQL require different values. The name of the data source (‘foodb’), the Database, Description and Server are all bogus values which you will need to fill.

An example

For new PHP scripts, database grunt-work is invariably done via PHP Data Objects (PDO). The good news is, it is easy to use it with MSSQL from here.

The below file takes a query on standard input, throws it at the database, and returns the result as comma-separated values.

Save this as query.php and fill in your data source (‘odbc:foodb’ here), username, and password.

#!/usr/bin/env php
<?php
$query = file_get_contents("php://stdin");
$user = 'baz;
$pass = 'super secret password here';

$dbh = new PDO('odbc:foodb', $user, $pass);
$dbh -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sth = $dbh -> prepare($query);
$sth -> execute();
$results = $sth -> fetchAll(PDO::FETCH_ASSOC);

/* Quick exit if there are no rows */
if(count($results) == 0) {
	return 0;
}
$f = fopen("php://stdout", "w");

/* Output header */
$a = $results[0];
$header = array();
foreach($a as $key => $val) {
	$header[] = $key;
}
fputcsv($f, $header);

/* Output rows */
foreach($results as $result) {
	fputcsv($f, $result);
}

fclose($f);

To test the new script, first make it executable:

chmod +x query.php

To run a simple test query:

echo "SELECT Name from sys.tables ORDER BY Name;" | ./query.php

Refining the setup

The above script has some cool features: It’s short, actually useful, and it sets PDO::ERRMODE_EXCEPTION. This means that if something breaks, it will fail loudly and tell you why.

Hopefully, if your setup has issues, you can track down the cause with the error, and solve it by scrolling through this how-to again.

If you encounter a MSSQL datablase with an unknown schema, then you may want to list al rows and columns. This is achieved with:

SELECT tables.name AS tbl, columns.name AS col FROM sys.columns JOIN sys.tables ON columns.object_id = tables.object_id ORDER BY tbl, col;

The catch

I’ve run into some bizarre limitations using this. Be sure to run it on a server which you can update at the drop of a hat.

A mini-list of issues I’ve seen with this combination of software (no sources as I never tracked down the causes):

• An old version of the driver would segfault PHP, apparently when non-ASCII content appeared in a text field.
• Substituting non-text values fails in the version I am using, although Google suggests that updating the ODBC driver fixes this.