linux下php+freetds连接SQL server2012

linux下php+freetds连接SQL server2012

官方网站：
http://www.freetds.org
ftp://ftp.freetds.org/pub/freetds/stable/freetds-stable.tgz
http://www.freetds.org/userguide/choosingtdsprotocol.htm

参看：
https://github.com/lj2007331/lnmp
http://blog.linuxeye.com/31.html
freetds0.91
一.下载
[root@test1 ~]# wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-stable.tgz
[root@test1 ~]# pwd
/root
[root@test1 ~]# ls
freetds-stable.tgz

二.编译安装
[root@test1 ~]#yum -y install gcc gcc-c++ libxml2-devel openssl-devel pcre-devel curl-devel gd-devel bzip2-devel
[root@test1 ~]#tar -zxvf freetds-stable.tgz -C /usr/local/src/
[root@test1 ~]# cd /usr/local/src/freetds-0.91/
[root@test1 freetds-0.91]# ls
aclocal.m4    config.sub   freetds.spec     Makefile.am    src
AUTHORS       configure     freetds.spec.in  Makefile.in    tds.dox
autogen.sh    configure.ac  include          missing        TODO
BUGS          COPYING       INSTALL          mkinstalldirs  vms
ChangeLog     COPYING.LIB   install-sh       NEWS           win32
compile       depcomp       interfaces       Nmakefile
config.guess  doc           locales.conf     PWD.in
config.log    freetds.conf  ltmain.sh        README
config.rpath  FreeTDS.sln   m4               samples

下面是截取的freetds官方安装说明

How to build: Configure and make

If you've built other GNU projects, building FreeTDS is a fairly straightforward process. We have a terse and verbose description.

FreeTDS is known to build with GNU and BSD make. If you encounter a large number of build errors, and your operating system's make is not GNU make (as is the case on most non-GNU/Linux systems), you may wish to install GNU make from ftp.gnu.org.

For Experts

 $ ./configure --prefix=/usr/local
        

$ make


$ make install

Table 3-1. Versions of the TDS Protocol, by Product

Product	TDS Version	Comment
Sybase before System 10, Microsoft SQL Server 6.x	4.2	Still works with all products, subject to its limitations.
Sybase System 10 and above	5.0	Still the most current protocol used by Sybase.
Sybase System SQL Anywhere	5.0 only	Originally Watcom SQL Server, a completely separate codebase. Our best information is that SQL Anywhere first supported TDS in version 5.5.03 using the OpenServer Gateway (OSG), and native TDS 5.0 support arrived with version 6.0.
Microsoft SQL Server 7.0	7.0	Includes support for the extended datatypes in SQL Server 7.0 (such as char/varchar fields of more than 255 characters), and support for Unicode.
Microsoft SQL Server 2000	7.1	Include support for bigint (64 bit integers), variant and collation on all fields. Collation is not widely used.
Microsoft SQL Server 2005	7.2	Includes support for varchar(max), varbinary(max), xml datatypes and MARS[a].
Microsoft SQL Server 2008	7.2 (unchanged)
Notes: a. Multiple Active Result Sets. FreeTDS does not support MARS.

For best results, use the highest version of the protocol supported by your server. If you encounter problems, try a lower version. If that works, though, please report it to the mailing list! [1]

[root@test1 freetds-0.91]# ./configure --prefix=/usr/local/freetds --enable-msdblib --with-tdsver=8.0
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
... ...
config.status: executing depfiles commands
config.status: executing libtool commands

[root@test1 freetds-0.91]# make && make install
Making all in include
make[1]: Entering directory `/usr/local/src/freetds-0.91/include'
make  all-am
... ...
make[2]: Leaving directory `/usr/local/src/freetds-0.91'
make[1]: Leaving directory `/usr/local/src/freetds-0.91'

编译php freetds库支持
[root@test1 freetds-0.91]# cp include/tds.h /usr/local/freetds/include/
[root@test1 freetds-0.91]# cp src/tds/.libs/libtds.a /usr/local/freetds/lib
[root@test1 freetds-0.91]# echo /usr/local/freetds/lib/ >>/etc/ld.so.conf.d/freetds.conf
[root@test1 freetds-0.91]#ldconfig
[root@test1 freetds-0.91]# echo "export FREETDSCONF=/usr/local/freetds/etc/freetds.conf" >> /etc/profile
[root@test1 freetds-0.91]# source /etc/profile
[root@test1 freetds-0.91]# echo $FREETDSCONF
/usr/local/freetds/etc/freetds.conf
[root@test1 freetds-0.91]# ln -s /usr/local/freetds/lib /usr/local/freetds/lib64
[root@test1 freetds-0.91]# vim /usr/local/freetds/etc/freetds.conf
[root@test1 freetds-0.91]# cat /usr/local/freetds/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
[SQLserver2008]    #标识，可有可无
    host = 192.168.8.112
    port = 1433
    tds version = 8.0
    client charset = UTF-8

[root@test1 freetds-0.91]# echo 'PATH=$PATH:/usr/local/freetds/bin/' >>/etc/profile
[root@test1 freetds-0.91]# source /etc/profile
[root@test1 freetds-0.91]# echo $PATH
/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/freetds/bin/
[root@test1 ~]# freebcp
usage:  freebcp [[database_name.]owner.]table_name {in | out} datafile
        [-m maxerrors] [-f formatfile] [-e errfile]
        [-F firstrow] [-L lastrow] [-b batchsize]
        [-n] [-c] [-t field_terminator] [-r row_terminator]
        [-U username] [-P password] [-I interfaces_file] [-S server]
        [-v] [-d] [-h "hint [,...]" [-O "set connection_option on|off, ...]"
        [-A packet size] [-T text or image size] [-E]
        [-i input_file] [-o output_file]
       
example: freebcp testdb.dbo.inserttest in inserttest.txt -S mssql -U guest -P password -c
可以看到freebcp安装成功。


php5.5.19
一.编译安装php
编译php扩展库
libmcrypt ftp://mcrypt.hellug.gr/pub/crypto/mcrypt/attic/libmcrypt
[root@test1 ~]# wget ftp://mcrypt.hellug.gr/pub/crypto/mcrypt/attic/libmcrypt/libmcrypt-2.5.7.tar.gz
[root@test1 ~]# tar -zxvf libmcrypt-2.5.7.tar.gz -C /usr/local/src
[root@test1 ~]# cd /usr/local/src/libmcrypt-2.5.7
[root@test1 libmcrypt-2.5.7]# ./configure && make && make install
[root@test1 ~]#ldconfig

libmhash http://sourceforge.net/projects/mhash/files/
[root@test1 ~]# tar -jxvf mhash-0.9.9.9.tar.bz2 -C /usr/local/src
[root@test1 ~]# cd /usr/local/src/mhash-0.9.9.9/
[root@test1 mhash-0.9.9.9]# ./configure && make && make install
[root@test1 ~]#ldconfig

mcrypt ftp://mcrypt.hellug.gr/pub/crypto/mcrypt/attic
[root@test1 ~]# wget ftp://mcrypt.hellug.gr/pub/crypto/mcrypt/attic/mcrypt-2.6.4.tar.gz
[root@test1 ~]# tar -zxvf mcrypt-2.6.4.tar.gz -C /usr/local/src
[root@test1 ~]# cd /usr/local/src/mcrypt-2.6.4
[root@test1 mcrypt-2.6.4]#LD_LIBRARY_PATH=/usr/local/lib ./configure && make && make install
[root@test1 ~]#ldconfig
注意：上面标红的部分一定不能省，不然会报如下错误
checking for libmcrypt - version >= 2.5.0... no
*** Could not run libmcrypt test program, checking why...
*** The test program compiled, but did not run. This usually means
*** that the run-time linker is not finding LIBMCRYPT or finding the wrong
*** version of LIBMCRYPT. If it is not finding LIBMCRYPT, you'll need to set your
*** LD_LIBRARY_PATH environment variable, or edit /etc/ld.so.conf to point
*** to the installed location 

Also, make sure you have run ldconfig if that
*** is required on your system
***
*** If you have an old version installed, it is best to remove it, although
*** you may also be able to get things to work by modifying LD_LIBRARY_PATH
***
configure: error: *** libmcrypt was not found

libgd
http://libgd.bitbucket.org/
http://autosetup1.googlecode.com/files
[root@test1 ~]#wget http://autosetup1.googlecode.com/files/gd-2.0.35.tar.gz
[root@test1 ~]#tar -zxvf gd-2.0.35.tar.gz -C /usr/local/src/
[root@test1 ~]#cd /usr/local/src/gd-2.0.35/
[root@test1 gd-2.0.35]# ./configure --with-png --with-jpeg
[root@test1 gd-2.0.35]# make && make install
[root@test1 ~]#ldconfig
注意：gd库很重要，诸如dedeCMS, DiscuzX!,PHPwind等建站框架都需要gd库支持，并且官方下载的gd库一直安装不成功，所以这里才用了第三方的地址。


php http://cn2.php.net/get/php-5.5.19.tar.bz2/from/a/mirror
[root@test1 ~]# tar -jxvf php-5.5.19.tar.bz2 -C /usr/local/src
[root@test1 ~]# cd /usr/local/src/php-5.5.19
[root@test1 php-5.5.19]# ./configure \
--prefix=/usr/local/php \
--with-config-file-path=/usr/local/php/etc \
--with-config-file-scan-dir=/usr/local/php/etc/php.d \
--disable-debug \ 
--enable-fpm \
--enable-sysvshm \
--enable-pcntl \
--enable-zip \
--enable-mbstring \
--with-pcre-regex \
--with-curl \
--with-bz2 \
--with-zlib \
--with-mcrypt \
--with-gd
[root@test1 php-5.5.19]# make && make install
注意：
编译PHP5.5 make 时出现错误
make: *** [ext/fileinfo/libmagic/apprentice.lo] Error 1
这是由于内存小于1G所导致，解决办法在./configure加上选项:
--disable-fileinfo     #禁用 fileinfo

添加环境变量
 [root@test1 php-5.5.19]#vim /etc/profile
PATH=$PATH:/usr/local/freetds/bin/:/usr/local/php/sbin:/usr/local/php/bin
 [root@test1 php-5.5.19]#source /etc/profile
 [root@test1 php-5.5.19]#echo $PATH
/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/freetds/bin/:/usr/local/freetds/bin/:/usr/local/php/sbin:/usr/local/php/bin


二.编译php扩展功能，己支持freetds
1.mssql模块
[root@test1 php-5.5.19]# cd /usr/local/src/php-5.5.19/ext/mssql/
[root@test1 mssql]# /usr/local/php/bin/phpize
Configuring for:
PHP Api Version:         20121113
Zend Module Api No:      20121212
Zend Extension Api No:   220121212
[root@test1 mssql]# ./configure --with-php-config=/usr/local/php/bin/php-config  --with-mssql=/usr/local/freetds/
... ...
creating libtool
appending configuration tag "CXX" to libtool
configure: creating ./config.status
config.status: creating config.h
[root@test1 mssql]# make && make install

2.pdo_dblib模块
[root@test1 mssql]# cd /usr/local/src/php-5.5.11/ext/pdo_dblib/
[root@test1 pdo_dblib]# /usr/local/php/bin/phpize
Configuring for:
PHP Api Version:         20121113
Zend Module Api No:      20121212
Zend Extension Api No:   220121212
[root@test1 pdo_dblib]# ./configure --with-php-config=/usr/local/php/bin/php-config --with-pdo-dblib=/usr/local/freetds/
... ...
creating libtool
appending configuration tag "CXX" to libtool
configure: creating ./config.status
config.status: creating config.h
[root@test1 pdo_dblib]# make && make install

三.配置php,php-fpm
[root@test1 ~]# cp /usr/local/src/php-5.5.11/php.ini-production /usr/local/php/etc/php.ini
[root@test1 ~]# echo 'extension="mssql.so"' >>/usr/local/php/etc/php.ini
[root@test1 ~]# echo 'extension="pdo_dblib.so"' >>/usr/local/php/etc/php.ini

[root@test1 ~]#cp /usr/local/php/etc/php-fpm.conf.default /usr/local/php/etc/php-fpm.conf
[root@test1 ~]# cp /usr/local/src/php-5.5.11/sapi/fpm/init.d.php-fpm /etc/init.d/php-fpm
[root@test1 ~]# chmod +x /etc/init.d/php-fpm
[root@test1 ~]# ll /etc/init.d/php-fpm
-rwxr-xr-x. 1 root root 2354 Nov 19 08:58 /etc/init.d/php-fpm
[root@test1 php]# /etc/init.d/php-fpm start
Starting php-fpm  done
[root@test1 php]# /etc/init.d/php-fpm status
php-fpm (pid 1579) is running...
[root@test1 php]#netstat -tunlp|grep php
tcp        0      0 127.0.0.1:9000              0.0.0.0:*                   LISTEN      32468/php-fpm
[root@test1 ~]# chkconfig php-fpm on
[root@test1 ~]# chkconfig --list php-fpm
php-fpm            0:off    1:off    2:on    3:on    4:on    5:on    6:off

四.测试SQLserver数据库连接
[root@test1 ~]# tsql -H 192.168.8.112 -p1433  -U administrator -P P@ssw0rd
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Msg 18456 (severity 14, state 1) from WIN-UQPK77TJ2DM Line 1:
    "用户 'administrator' 登录失败。"
Error 20002 (severity 9):
    Adaptive Server connection failed
There was a problem connecting to the server
SQLserver数据库用户才能登录
[root@test1 ~]# tsql -H 192.168.8.112 -p1433  -U sa -P 12345
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> select * from dbo.spt_monitor
2> go
lastrun    cpu_busy    io_busy    idle    pack_received    pack_sent    connections    pack_errors    total_read    total_write    total_errors
Apr  2 2010 05:34:58:817PM    9    7    792    28    28    14    00    0    0
(1 row affected)
-S #直接读取配置文件/usr/local/freetds/etc/freetds.conf
[root@test1 html]# tsql -S 192.168.8.112 -U sa
Password:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> quit



nginx1.6.2
请参看：
Nginx-1.x.x源码自动安装配置(CentOS6)


通过phpinfo()函数显示以下参数，证明php, freetds模块生效。