CentOS7 PHP 连接 MSSQL 2008

1. CentOS 7.2 / MSSQL Server 2008 R2

略

2. CentOS <-> MSSQL Server 2008 R2

1. 安装 epel 源

rpm -Uvh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-7.noarch.rpm

2. 默认禁用

vi /etc/yum.repos.d/epel.repo
enabled=0

3. yum 安装 freetds mssql 驱动

yum install freetds php-odbc php-mssql --enablerepo=epel

注：这里有个问题：服务器环境是从lnmp.org直接下载的包，而非yum默认安装的php，即服务器中有两个php。这导致后面的 第三部分工作。

另：php-odbc应可不必安装

4. 编辑配置

vi /etc/freetds.conf
[global]
    tds version = 8.0
    port = 1433
    client charset = UTF-8
    debug flags = 0x4fff
    timeout = 180
    connect timeout = 180
    text size = 20971520

[SQLServer]
    host = 192.168.0.81
    port = 1433
    tds version = 8.0
    encryption = request

5.准备配置文件。odbc.ini 不存在，先建个空的，inst应该已经存在了，不动即可。

vi /usr/local/etc/odbc.ini
vi /usr/local/etc/odbcinst.ini

移动到 /usr/local/etc

mv /etc/odbc.ini /usr/local/etc/
mv /etc/odbcinst.ini /usr/local/etc/

创建软连接，修改权限

cd /etc
ln -s /usr/local/etc/odbc.ini
ln -s /usr/local/etc/odbcinst.ini
chmod 0664 /usr/local/etc/odbc*.ini

6.创建源模版

cd ~
vi tds.datasource.template
[DSNName]
Driver = FreeTDS
Description = TDS_Servername
Trace = No
Server = IPAddress
Port = 1433
TDS Version = 8.0
Database = DBName

创建驱动模版

vi tds.driver.template
[FreeTDS]
Description = v0.91 with protocol v8.0
Driver = /usr/lib64/libtdsodbc.so.0

7.安装源和驱动

odbcinst -i -d -f tds.driver.template
odbcinst -i -s -f tds.datasource.template
cp /root/.odbc.ini /etc/odbc.ini

用#注释掉无用的驱动

vi /etc/odbcinst.ini

8.使用 -H 测试连接

tsql –H Hostname_OR_IP –p PortNumber –U Username
USE DBName;
SELECT top 10 *FROM TableName WHERE FieldName = SampleValue;
GO

使用-S 测试连接

tsql –S Servername –U Username –D DBName
SELECT * FROM TableName WHERE FieldName = ‘SampleValue’;
GO
exit

3. PHP <-> SQLServer

1. php + mssql extension

如第二部分所述，使用lnmp.org安装的php，地址为：/usr/local/php/etc/php.ini 与 centos自带php /etc/php.ini 不同。需要修改所有的phpize, pear pecl php 的链接，或者制定所使用的php。

方法较多，举例：

[root@localhost ext]# cd mssql
[root@localhost mssql]# /usr/local/php/bin/phpize 
Configuring for:
PHP Api Version:         20100412
Zend Module Api No:      20100525
Zend Extension Api No:   220100525
[root@localhost mssql]# find / -name libsybdb.so
/usr/lib64/libsybdb.so
[root@localhost mssql]# ln -s /usr/lib64/libsybdb.so /usr/lib/libsybdb.so
[root@localhost mssql]# ./configure --with-php-config=/usr/local/php/bin/php-config
appending configuration tag "CXX" to libtool
configure: creating ./config.status
config.status: creating config.h
[root@localhost mssql]# make
[root@localhost mssql]# make install
Installing shared extensions:     /usr/local/php/lib/php/extensions/no-debug-non-zts-20100525/
[root@localhost mssql]# vi /usr/local/php/etc/php.ini
extension=mssql.so;
[root@localhost mssql]# service php-fpm restart
Gracefully shutting down php-fpm . done
Starting php-fpm  done

2. 测试  

    
[root@localhost mssql]# vi /wwwroot/default/pi.php 
<?php phpinfo();

访问phpinfo，确认mssql OK。

测试查询：

putenv(“FREETDSCONF=/etc/freetds.conf”);
putenv(“TDSVER=80”);
$conn=mssql_connect($FTS_DSN,$ACC,$pwd);
mssql_select_db($dbname,$conn);
$query="select * from Ts_User";
$result=mssql_query($query);
$num=mssql_num_rows($result);
$data = array();
for($i=0;$i<$num;$i++) {
 $row = mssql_fetch_array($result);
 $data[] = $row;
}
echo json_encode($data);