ubuntu 配置PHP通过PDO unixODBC 连接 MSSQL Server 2005

jopen 10年前

ubuntu 配置PHP通过PDO unixODBC 连接 MSSQL Server 2005


1 安装unixODBC和freeTDS

apt-get install unixodbc

apt-get install unixodbc-dev

apt-get install odbcinst1debian1

apt-get install tdsodbc

apt-get install freetds-dev

apt-get install freetds-bin

(本人在测试的过程中,只需要安装 unixodbc和freetds-bin)

2 freeTDS配置

 

vi /etc/freetds/freetds.conf 

 

[global]增加

client charset = UTF-8

 

下面增加

[mssql20051]

    host=192.168.0.1

    port=1433

    tds version=8.0

 

[mssql20052]

    host=192.168.0.2

    port=1433

    tds version=8.0

 

tsql测试连接两个mssql服务器

tsql -S mssql20051 -U sa -P 123 -D test

tsql -S mssql20052 -U sa -P 123 -D test

执行sql没问题

sql返回的编码与系统locale有关。

 

3 unixodbc配置文件

 

vi /etc/odbcinst.ini

 

[TDS]

Description=MS-SQLServer

Driver=/usr/lib/odbc/libtdsodbc.so

Setup=/usr/lib/odbc/libtdsS.so

UsageCount=1

 

vi /etc/odbc.ini

 

[ODBC Data Sources]

mssql1     = MS SQL ODBC 2.50 Driver DSN

mssql2     = MS SQL ODBC 2.50 Driver DSN

 

[mssql1]

Driver       = /usr/lib/odbc/libtdsodbc.so

Description  = MySQL ODBC 2.50 Driver DSN

Servername   = mssql20051

Database     = test

Trace        = No

 

[mssql2]

Driver       = /usr/lib/odbc/libtdsodbc.so

Description  = MySQL ODBC 2.50 Driver DSN 

Server       = 192.168.0.2

Port         = 1433 

User         = sa 

Password     = 123

Database     = test

Option       = 3

Socket       =

Trace        = No 

TDS_VERSION  = 8.0

 

 

注意mssql1和mssql2里面一个是Servername一个是server的ip

如果使用Servername,则填写freetds.conf里面的配置

 

测试连接

isql -v mssql1 sa 123

执行sql返回结果的编码与locale有关

 

isql -v mssql2 sa 123

执行sql返回结果的编码是GBK

 

 

4 编译php

./configure  --with-unixODBC=/usr --with-pdo-odbc=unixODBC,/usr --with-apxs2=/usr/local/apache2/bin/apxs 

make

make install

 

 

5 php测试代码

 

 <?php       //TDS Driver , only support GBK       $dsn = "odbc:Driver=TDS; Server=192.168.0.1; Uid=sa; Pwd=123; Database=test;";       $c = new PDO($dsn);            //MiscroSoft sqlsrv for php windows ,support utf8,  add mssql.charset=utf8  in php.ini       //$c = new PDO( "sqlsrv:Server=192.168.0.1 ; Database = test", "sa", "123", array(PDO::SQLSRV_ATTR_DIRECT_QUERY => true));             //Linux, TDS Data Source, support utf8       //$c = new PDO('odbc:mssql1','sa','123');       $query = 'SELECT top 5 * FROM userinfo';       $stmt = $c->query( $query );       while ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ) {          print_r( $row );       }       $c = null;    ?>

 

 <?php       //TDS Driver , only support GBk       $dsn = "odbc:Driver=TDS; Server=10.1.3.252; Uid=sa; Pwd=sqltest; Database=bc_cric_lj;";       $c = new PDO($dsn);            //MiscroSoft sqlsrv for php windows        //$c = new PDO( "sqlsrv:Server=172.18.3.104 ; Database = test", "sa", "123", array(PDO::SQLSRV_ATTR_DIRECT_QUERY => true));             //Linux, TDS Data Source, support utf8       //$c = new PDO('odbc:mssql','sa','sqltest');       $query = 'SELECT top 5 * FROM criclj_internal_home_baseinfo';       $stmt = $c->query( $query );       while ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ) {          print_r( $row );       }       $c = null;    ?>
来自:http://blog.csdn.net/fanyunlei/article/details/21395301