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-8I also changed php.ini file (but not sure if it’s really needed):
mssql.charset = "UTF-8"
Have fun with you MS’SQL!
In: English, Fighting the system, Linux, MS'SQL, Ubuntu