2017/04/30

Published 4月 30, 2017 by

Linux下PHP連線MSSQL

OS:RHEL 6.7 64bit
PHP: PHP 5.3.3

說明：
透過ODBC連結MSSQL DB


安裝步驟：
1.   FreeTDS and unixODBC.

可透過yum或下載RPM安裝：
#yum install unixODBC freetds

[root@test tmp]# rpm -qa | grep freetds
freetds-doc-0.91-2.el6.x86_64
freetds-0.91-2.el6.x86_64

[root@test tmp]# rpm -qa | grep unixODBC
unixODBC-devel-2.2.14-14.el6.x86_64
unixODBC-2.2.14-14.el6.x86_64


2.     Registering the ODBC driver with freeTDS

[root@test tmp]#  ldconfig -p | grep libtdsodbc
libtdsodbc.so.0 (libc6,x86-64) => /usr/lib64/libtdsodbc.so.0

3.     Create and edit files /etc/odbcinst.ini and /etc/odbc.ini with following contents:
[root@test tmp]# cat /etc/odbcinst.ini
[FreeTDS]
Description = Freetds v 0.95
Driver =/usr/lib64/libtdsodbc.so.0

[root@test tmp]# cat /etc/odbc.ini
[MESDB1]
Driver = FreeTDS
Description = Any description
Trace = No
Server = 10.3.2.1
Port = 1433
TDS version = 0.95
Database = db_ControlManager

4.     Testing MSSQL connection via command line
#isql -v dw364 account password

5.     Creating symlinks to the .ini files
# ln -s /etc/odbc.ini /usr/local/zend/etc/odbc.ini
# ln -s /etc/odbcinst.ini /usr/local/zend/etc/odbcinst.ini

6.     Testing MSSQL connection via PHP
<?php

// Replace the value's of these variables with your own data:
$dsn = "Driver=FreeTDS;DSN=MESDB1"; // Data Source Name (DSN) from the file /usr/local/zend/etc/odbc.ini
$user = "nacadm"; // MSSQL database user
$password = "abcNAC"; // MSSQL user password

$conn = odbc_connect($dsn, $user, $password);
if ($conn) {
    echo "Connection established.";
} else {
    die("Connection could not be established.");
}


$sql = "select EI_DomainName as Domain ,EI_OS_MachineName as Host_Name, EI_IPAddressList as IP, EI_UserAccount as login_name from dbo.tb_EntityInfo where EI_state=100 and EI_IPAddressList like '10.2.23%' or EI_IPAddressList like '10.2.3%'order by  Domain";

$rs=odbc_exec($conn,$sql);
while (odbc_fetch_row($rs))
{
  $compname=odbc_result($rs,"Domain");
  $conname=odbc_result($rs,"Host_Name");
  echo "<tr><td>$compname</td>";
  echo "<td>$conname</td></tr>";
}
odbc_close($conn);
?>

參考資料：
https://support.zend.com/hc/en-us/articles/218197897-Configuring-a-Linux-Server-to-Connect-to-an-MSSQL-Database-Using-ODBC