php sqlserver 环境,ltnmp环境下php连接sqlserver

ltnmp环境下php远程连接sqlserver：

1、环境：

centos6.5_x64;

php5.6.11;

Tengine-2.1.0;

FreeTDS-0.95.19;

远程SQLServer：2008;

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

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

下面是截图：

测试是否安装成功：

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

测试连接：

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

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

关于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数据库信息

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版本测试：

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);

}

?>