Connecting to Microsoft SQL Server using ODBC from Ubuntu Server

Previously I showed how to connect to Microsoft SQL Server in PHP using mssql_connect(). This works but is not as neat as using ODBC.

Unfortunately when writing this guide I got stuck at the last hurdle when trying to use ODBC and adodb in PHP. I could connect to the database using:

include(‘adodb5/adodb.inc.php’);
$connection=& ADONewConnection(‘odbc_mssql’);
$connection->Connect(‘datasourcename’,’username’,’password’);

Or using odbc_connect:

$connection= odbc_connect(‘datasourcename’, ‘username’, ‘password’);

But selecting data using “$connection->GetAll()” or “odbc_exec()” just resulted in a page that never loaded. Fortunately the ODBC part worked, there was just something I haven’t figured out yet with ODBC in PHP.

This guide will show you how to set up ODBC in Ubuntu using FreeTDS to talk to Microsoft SQL Server. I am trying to work out why the server connects fine but won’t execute queries in PHP. When I find out why I will update this post. To create this guide I went off a post on Ubuntu forums with some additions. There are a lot of steps, but at least some of the programs you use along the way allow you to check things are working as expected before you come to test in PHP.

First you need to install php5-odbc in Ubuntu:

sudo apt-get install php5-odbc

Now you need to add freetds to create a connection and talk to your server, plus a few other handy programs:

sudo apt-get install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc

Edit the example server details or create a new one in /etc/freetds/freetds.conf to point to your server (in my case I called it “sqlserver”):

[sqlserver]
host = ip.add.ress
port = 1433
tds version = 7.0

You can check the connection is set up using sqsh:

sqsh -S sqlserver -U username -P password

Alternatively, you can connect directly to the ip.add.ress:

sqsh -S ip.add.ress -U username -P password

Now in sqsh you can select from a table in your SQL Server database and display it on screen by entering your query and “go” on the next line:

1> select * from databasename.dbo.table
2> go

Hopefully you should see your data on screen. The next step is to configure ODBC so check where the config files are located using:

odbcinst -j

Which should say “DRIVERS: /etc/odbcinst.ini” and “SYSTEM DATA SOURCES: /etc/odbc.ini” which are the files we will be editing.

Now we set up the FreeTDS driver so it can be used by ODBC so open “/etc/odbcinst.ini” (was a blank file in my case) and add:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

Open “/etc/odbc.ini” and create the system data source “datasourcename” that we will use everywhere (including in PHP):

[datasourcename]
Driver = FreeTDS
Description  = ODBC connection via FreeTDS
Trace = No
Servername = sqlserver
Database = databasename

It’s important to note that “Servername” here refers to the server details in /etc/freetds/freetds.conf NOT to an IP address.

You can test the connection works using isql:

isql -v datasourcename username password

Now you can use this data source “datasourcename” in PHP, which is the goal! To test the connection works I tried odbc_connect():

$connection = odbc_connect(‘datasourcename’, ‘username’, ‘password’);
if (!$connection) die(“Connection failed”);
else echo “works”;

Which does work. Now I just need to work out why queries such as the following cause PHP to hang:

$query = odbc_exec($connection, ‘SELECT column FROM table’) or die (odbc_errormsg());

26 thoughts on “Connecting to Microsoft SQL Server using ODBC from Ubuntu Server

    • I’m afraid I’m still working on it. The solution for me was to go back to using adodb with mssql:// rather than mysql://, which I’ll be covering in a post *very* soon. It’s actually a lot easier than doing it yourself if you are writing code from scratch plus it has the added benefit of allowing you to change DBs without changing anything in your code..

  1. Pingback: Using ADODB to easily connect to MySQL and Microsoft SQL Server from PHP « James Rossiter

  2. I guess the question remains. Did you ever find out how to fix odbc_exec() hanging on select queries?

    I found the instructions easy to follow and especially benefited from the comment, It’s important to note that “Servername” here refers to the server details in /etc/freetds/freetds.conf NOT to an IP address.

    Thanks and keep up the good work.

  3. Did you resolve the issue with regards to your select statement?

    The following executes after following all of your steps, re-starting Apache, and putting the *.php file in the var/www/ directory:

    This script should print out all tables in the database (as defined in odbc.ini).

    Thanks James for the help, you were a God send!

  4. Ok, the code vanished !?

    $data_source=datasource;
    $user=user;
    $password=password;

    $conn=odbc_connect($data_source, $user, $password);

    $stmt = odbc_exec($conn, “SELECT * FROM information_shcema.tables”);

    $result = odbc_result_all($stmt);

    odbc_close($conn);

  5. Hey James
    I’m at the same stage as you are now?

    Did you end up resolving your odbc_exec() hanging issue?

    Thanks.

    Regards

  6. In case anybody has the same problem, I thought I’d leave this.

    After following this tutorial, I never could get PHP to execute the query without hanging. One thing I found that fixed this was to specify the server address and port directly in odbc.ini rather than in odbcinst.ini. For example, my odbc.ini is:

    [testdatasource]
    Driver = FreeTDS
    Description = Test Description
    Trace = No
    Server = 192.168.0.4
    port = 1433
    Database = dbname

    my odbcinst.ini is:

    [FreeTDS]
    Description = TDS Driver (Sybase/MS SQL)
    Driver = /usr/lib/odbc/libtdsodbc.so

    Doing it this way allowed PHP to execute the query. Unfortunately, I have no explanation as to why. Just thought others might like to know.

    • Works for me too, thanks!

      Can also make it work by specifying tds version = 6.0 in freetds.conf although I have no idea what we might be losing by not using version 7.0.

      • Changing to “tds version = 8.0″ gets odbc_exec working for me – its what found working on a couple of old Fedora based servers I inherited.

        Also if you’re using Ubuntu 12.0.4LTS you need to remove the path from the drivers is odbcinst.ini as they’ve been moved, so my /etc/odbcinst.ini now looks like:

        [FreeTDS]
        Description = TDS driver (Sybase/MS SQL)
        Driver = libtdsodbc.so
        Setup = libtdsS.so
        CPTimeout =
        CPReuse =
        FileUsage = 1

  7. Pingback: Doctrine 1.2, SQL Server, FreeTDS and Hanging Queries | James Halsall

  8. Pingback: Symfony 1.4 Doctrine 1.2 MS SQL Server | My Rant

  9. Hi Guys

    Just thought I would let you know. Here is the solution to your hanging problem. In /etc/freetds/freetds.conf you need to add “initial block size”. This has to be in increments of 512. Here is my freedts.conf to give you an idea: -

    host = 192.168.1.1
    port = 1433
    tds version = 7.0
    initial block size = 2048

    512 is the default. I tried 1536 but with no luck 2048 seems the sweet spot.

  10. I get this error:

    Message: odbc_connect(): SQL error: [unixODBC][Driver Manager]Can’t open lib ‘/usr/lib/odbc/libtdsodbc.so’ : file not found, SQL state 01000 in SQLConnect

    Except it’s there (though it wasn’t on install, I had to chase it down in a .deb package and extract it, I tried chmod 775 on it too).

    Any ideas?

  11. Pingback: Using ADODB to easily connect to MySQL and Microsoft SQL Server from PHP | James Rossiter

  12. SOLVED

    The hanging issue was solved for me by specifying the cursor type in `odbc_connect`.

    `odbc_connect(‘DSN’, ‘user’, ‘password’, SQL_CUR_USE_ODBC);`

    Hope this works for everyone else

    • Hi,

      I managed to get queries executed but I was unable to do it using the SQL_CUR_USE_DRIVER parameter. It only works without this parameter or with SQL_CUR_USE_DRIVER.
      How can I get it to work with the SQL_CUR_USE_DRIVER? I need to perform a synchronization of two databases with 12.000 rows in on of the tables and I need it for performance purposes.

      I followed the instructions in this tutorial and have ubuntu 9.10.

      Thanks.

  13. Thank You! You made my Day!!! =)

    Working ADODB with PHP and MSSQL 2000

    * I had to set the TDS Version to 8.0, also set the Page Size to 2048 as described

  14. thank you very much for your post ,
    the following code is useful to connect

    prepare($query);
    $statement->bindValue(1, ‘Value’, PDO::PARAM_STR);
    $statement->execute();
    $result = $statement->fetchAll(PDO::FETCH_NUM);
    ?>

    • try
      {
      $db = new PDO(‘odbc:Driver=FreeTDS; Server=hostname_or_ip; Port=port; Database=database_name; UID=username; PWD=password;’);
      }
      catch(PDOException $exception)
      {
      die(“Unable to open database.Error message:$exception.”);
      }
      echo ‘Successfully connected!’;
      $query = ‘SELECT * FROM table_name WHERE table_name.COLUMN = ?’;
      $statement = $db->prepare($query);
      $statement->bindValue(1, ‘Value’, PDO::PARAM_STR);
      $statement->execute();
      $result = $statement->fetchAll(PDO::FETCH_NUM);

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>