MS’SQL on Ubuntu Linux via PHP

Hi Folks,

today we are going to install & use MS’SQL on Ubuntu Linux! Yay!

Well, in fact installation is very simple with a tiny twists 😉

Installation (very simple):

sudo apt-get install php5-mssql

Now how to use it?

PHP Code below:


// Connect to MSSQL
$link = mssql_connect($db['from']['server_ip'], $db['from']['username'], $db['from']['password']);
if (!$link) {
    die('Something went wrong while connecting to MSSQL');
}else{
    if( mssql_select_db($db['from']['database'],$link) ){
        echo "<h2>DB Selected: ".$db['from']['database']." </h2><hr>";
    }else{
        die("DB Not selected");
    }
    
    $sql="SELECT TOP 20 * FROM yaf_User";
    $sql_resource = mssql_query($sql);
    while($row = mssql_fetch_array($sql_resource)){
        echo "<br>".$row['Name'];
    }
    // Clean up
    mssql_free_result($sql_resource);
}

Looks simple – doesn’t? Well – there is a catch 😉 With Select * from table; you will most definitely get an error message saying something like:

Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

There are are two ways of fixing it.

Option 1:
Request fields with SQL Casting: SELECT CAST(field1 AS TEXT) AS field1 FROM table
So you cast your field as text for every varchar (or sometimes nvarchar in MS’SQL). Lousy option – to be honest. But works great for a quick fix.

Option 2:
Convert you PHP & your DB connection and everything arounf it to use proper UTF-8 aka Unicode.

Change this file: /etc/freetds/freetds.conf

Add lines as below:

[global]
;tds version = 4.2
tds version = 8.0
client charset = UTF-8

I also changed php.ini file (but not sure if it’s really needed):

mssql.charset = "UTF-8"

Have fun with you MS’SQL!

Posted on September 10, 2013 at 15:21 by arte · Permalink
In: English, Fighting the system, Linux, MS'SQL, Ubuntu

Leave a Reply