1、安装配置freetds
代码如下:
wget http://mirrors.xmu.edu.cn/ubuntu/archive/pool/main/f/freetds/freetds_0.82.orig.tar.gz
tar zxf freetds_0.82.orig.tar.gz
cd freetds_0.82
./configure --prefix=/usr/local/freetds --with-tdsver=8.0 --enable-msdblib --enable-dbmfix --with-gnu-ld --enable-shared --enable-staticmake && make install
2、编译php的mssql模块
代码如下:
cd /path/to/php/source 进入PHP源码目录
(比如:/home/lnmp/php-5.3.28)
cd ext/mssql 进入MSSQL模块源码目录
(mssql模块是源码自带的不需要下载)
执行/usr/local/php/bin/phpize 生成编译配置文件
./configure --with-php-config=/usr/local/php/bin/php-config --with-mssql=/usr/local/freetds
make
make install
编译完成生成 mssql.so,修改php.ini,将该模块载入:
extension=”/你的PHP扩展路径/mssql.so”
(可以同phpinfo,或者查看php.ini来确定)
比如我的是:
/usr/local/php/lib/php/extensions/no-debug-non-zts-20090626/
3、配置mssql
代码如下:
cd /usr/local/freetds/etc
vi freetds.conf
[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
client charset = UTF-8 #加入
#加入
[Server2005]
host = 192.168.x.x
port = 1433
tds version = 7.2
以下是我的配置:
# 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
[sql2008]
host = 10.213.24.27
port = 1433
tds version = 8.0
[rnc]
host = 192.168.0.134
port = 1433
tds version = 8.0
4、测试php连接mssql
try { $hostname='218.x.x.x';//注意,这里和上面不同,要直接用IP地址或主机名 $port=1433;//端口 $dbname="user";//库名 $username="database";//用户 $pw="passwd";//密码 $dbh= new PDO("dblib:host=$hostname:$port;dbname=$dbname","$username","$pw"); } catch (PDOException $e) { echo"Failed to get DB handle: ".$e->getMessage() ."n"; exit; } echo'connent MSSQL succeed'; $stmt=$dbh->prepare("select * from z_2010pinjiu_user"); $stmt->execute(); while ($row=$stmt->fetch()) { print_r($row); } unset($dbh); unset($stmt);
关于时间格式问题,如果PHP链接mssql时间格式不正确,解决方法如下:
第一个方法:php文档开头定义ini_set(”mssql.datetimeconvert”,0);
第二个方法:修改php.ini,找到;mssql.datetimeconvert = On ,去掉前面的分号,修改on为off,重启php
关于中文显乱码问题【2014年11月24日补充】
用自带mssql函数无法设置字符集,set names不被支持,最后参考了adodb的代码,解决办法是编辑/usr/local/freetds/etc/freetds.conf
在[global]下加上一行
client charset = GBK,设置成utf-8是不对的,因为mssql排序规则数据库默认设置是Chinese PRC,相当于gb2312,
另外php.ini 里面mssql.charset 是不需要设置的,其实说得很清楚:
; Specify client character set.
; If empty or not set the client charset from freetds.comf is used
; This is only used when compiled with FreeTDS
当FreeTDS的client charset 为空或者没有设置的时候才需要设置这个。曾经错误的将这里设置成utf-8,FreeTDS的client charset 也设置成utf8,结果mssql_connect的时候就开始报错了。最终结论,只需要设置FreeTDS的client charset 为GBK,然后在php的程序里面iconv('GBK', 'UTF-8//IGNORE', $str);一下就一切正常了。