Linux平台使用Freetds连接SQL Server服务器,兼容PHP和Laravel
本文在CentOS 7 64bit和Laravel 4.2环境测试通过。
1.下载源码并解压缩
- wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-stable.tgz
- tar zxvf freetds-stable.tgz
- cd freetds-0.91
2.配置并生成makefile
- ./configure --with-tdsver=8.0 --enable-msdblib
3.编译安装
- make
- sudo make install
4.配置
默认安装的配置文件位于/usr/local/etc,在/usr/local/etc编辑freetds.conf 文件,默认为
- # $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
- #
- # This file is installed by FreeTDS if no file by the same
- # name is found in the installation directory.
- #
- # For information about the layout of this file and its settings,
- # see the freetds.conf manpage "man freetds.conf".
- # Global settings are overridden by those in a database
- # server specific section
- [global]
- # TDS protocol version
- ; tds version = 4.2
- # Whether to write a TDSDUMP file for diagnostic purposes
- # (setting this to /tmp is insecure on a multi-user system)
- ; dump file = /tmp/freetds.log
- ; debug flags = 0xffff
- # Command and connection timeouts
- ; timeout = 10
- ; connect timeout = 10
- # If you get out-of-memory errors, it may mean that your client
- # is trying to allocate a huge buffer for a TEXT field.
- # Try setting 'text size' to a more reasonable limit
- text size = 64512
- # A typical Sybase server
- [egServer50]
- host = symachine.domain.com
- port = 5000
- tds version = 5.0
- # A typical Microsoft server
- [egServer70]
- host = ntmachine.domain.com
- port = 1433
- tds version = 7.0
在文件的最后位置添加如下配置,即可连接SQL Server 2000
- [sql-server-2000]
- host = 192.168.182.9
- port = 1433
- tds version = 7.0
如果要连接SQL Server 2005或2008,需要添加以下配置
- [sql-server-2005]
- host = 192.168.70.1
- port = 1433
- tds version = 8.0
4.测试
- /usr/local/bin/tsql -S sql-server-2000 -U sa -P test
如果成功连接,将会出现以下提示
- locale is "zh_CN.UTF-8"
- locale charset is "UTF-8"
- using default charset "UTF-8"
- 1>
至此,FreeTDS已经是Linux具备连接SQL Server的功能了。
5.编译PHP扩展
PHP 5.4之后已经没有原生支持的SQL Server的驱动了,因此需要手动编译PHP源码的扩展添加对SQL Server的驱动支持。CentOS 7自带的是5.4版本的PHP,因此我们通过编译5.4版的PHP源码获得扩展。
目前CentOS yum源里最新的php是5.4.16,php可以通过yum安装到系统
- sudo yum install php php-devel php-fpm php-common php-mysql php-pdo libzip
php官网上最新的5.4版本是 5.4.39,下载源码到本地
- wget http://cn2.php.net/distributions/php-5.4.39.tar.gz
解压并进入扩展目录
- tar zxvf php-5.4.39.tar.gz
- cd php-5.4.39/ext/mssql
使用phpize生成configure脚本文件
- phpize
生成makefile
- ./configure
编译
- make
编译之后将会在modules子目录生成mssql.so扩展文件。复制扩展文件到php的扩展文件目录
- sudo cp modules/mssql.so /usr/lib64/php/modules/
在/etc/php.d目录下新建mssql.ini 文件,输入以下内容
- ; Enable mssql extension module
- extension=mssql.so
这样PHP就能加载SQL Server驱动了。使用如下代码测试PHP连接SQL Server。
- <?php
- header("Content-type: text/html; charset=utf-8");
- $msdb=mssql_connect("sql-server-2000","sa","test");
- if (!$msdb) {
- echo "connect sqlserver error";
- exit;
- }
- mssql_select_db("msdb",$msdb);
- $result = mssql_query("SELECT top 5 * FROM employee", $msdb);
- while($row = mssql_fetch_array($result)) {
- var_dump($row);
- }
- mssql_free_result($result);
- ?>
代码中的数据库配置信息可以替换成别的。测试命令如下
- php -f test-mssql.php
成功执行后将会打印出数据库表中记录数据。
目前原生PHP代码已经可以连接SQL Server了,但是Laravel还是不行,还需要再编译生成一个pdo_dblib.so扩展驱动。
6.编译pdo_dblib.so扩展适配Laravel
- cd php-5.4.39/ext/pdo_dblib
- ./configure
- make
- sudo cp modules/pdo_dblib.so /usr/lib64/php/modules/
再到/etc/php.d下新建pdo_dblib.ini,输入以下内容
- ; Enable pdo_dblib extension module
- extension=pdo_dblib.so
再编辑Laravel的app/config/database.php文件,将sqlsrv区域改为一下形式
- 'sqlsrv' => array(
- 'driver' => 'sqlsrv',
- 'host' => 'sql-server-2000',
- 'database' => 'msdb',
- 'username' => 'sa',
- 'password' => 'test',
- 'prefix' => '',
- ),
这样Laravel也可以连接SQL Server了。