ltnmp环境下php连接sqlserver

ltnmp环境下php远程连接sqlserver:

1、环境:

centos6.5_x64;

php5.6.11;

Tengine-2.1.0;

FreeTDS-0.95.19;

远程SQLServer:2008;

官网下载:ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz(好像下载不了,技安在下载的时候始终只能下载到10%,然后就停住了,就停住了,就停住了…)。

这是技安费好大力气才下载的,分享出来下载:

0.95.19: freetds-patched.tar.gz

0.95.65: freetds-patched-0.95.65.tar

2、安装freetds。

# tar -zxvf freetds-patched.tar.gz
# cd freetds-0.95.19/
# ./configure --prefix=/usr/local/freetds --with-tdsver=7.3 --enable-msdblib
# make
# sudo make install
# sudo vim /etc/ld.so.conf.d/freetds.conf
  内容为:/usr/local/freetds/lib/
# sudo ldconfig

注:安装的TDS Version的详见官网文档:http://www.freetds.org/userguide/choosingtdsprotocol.htm

下面是截图:

freetds2

测试是否安装成功:

# /usr/local/freetds/bin/tsql -C

freetds

测试连接:

# /usr/local/freetds/bin/tsql -H 服务器IP/域名 -p 端口 -U 用户名 -P 密码

出现下图所示说明已经连接成功:

freetds1

关于freetds/etc/freetds.conf配置项
很多其他帖子写了需要配置/usr/local/freetds/etc/freetds.conf,其实这个不需要配置。如果配置也可以,配置了PHP就可以调用这个配置项,否则需要PHP代码里指定数据库服务器信息即可。
另外需要注意的是/usr/local/freetds/etc/下的freetds.conf不同于前面/usr/local/freetds/lib/那个freetds.conf。
如果配置了这里,那么PHP页面就可以使用这里的配置,不然PHP页面指定一样可以。

默认是这样的:

# $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

如果要添加配置可以在最后添加类似如下代码:

[sqlServer2008]
    host = 服务器ip或者域名
    port = 端口
    tds version = 7.3

3、php添加mssql扩展和pdo的pdo_dblib扩展

# 进入php源码目录
# cd php-5.6.11/
# cd ext/mssql/
# phpize
# ./configure --with-php-config=/usr/local/php/bin/php-config --with-mssql=/usr/local/freetds
# make
# sudo make install

# 修改php.ini文件使之支持mssql
# sudo vim /usr/local/php/etc/php.ini
# 增加扩展
# extension=mssql.so

添加pdo_dblib扩展

# cd ext/pdo_dblib/
# phpize
# ./configure --with-php-config=/usr/local/php/bin/php-config --with-pdo-dblib=/usr/local/freetds/
# make
# sudo make install
# 修改php.ini文件使之支持pdo_dblib
# sudo vim /usr/local/php/etc/php.ini
# 增加扩展
# extension=pdo_dblib.so

# 重启php-fpm
# sudo /etc/init.d/php-fpm restart

4、测试php调用SQLServer数据库信息

<?php
header("Content-type: text/html; charset=utf-8");

$msdb=mssql_connect("数据库地址:端口","账号","密码");
if (!$msdb) {
    echo "connect sqlserver error";
    exit;
}

mssql_select_db("数据库名称",$msdb);
$result = mssql_query("SELECT top 5 * FROM 表名称", $msdb);
while($row = mssql_fetch_array($result)) {
    var_dump($row);
}

mssql_free_result($result);
?>

PDO版本测试:

<?php 
header("Content-type: text/html; charset=utf-8"); 
   try { 
        $hostname = "数据库IP"; 
        $port = 端口; 
        $dbname = "数据库名"; 
        $username = "账号"; 
        $pw = "密码"; 
        $dbh = new PDO ("dblib:host=$hostname:$port;dbname=$dbname","$username","$pw"); 
} catch (PDOException $e) { 
    echo "Failed to get DB handle: " . $e->getMessage() . "\n"; 
    exit; 
} 
 
$stmt = $dbh->prepare("SELECT top 5 * FROM 数据表名"); 
$stmt->execute(); 
while ($row = $stmt->fetch()) { 
    var_dump($row); 
}  
?>