linux php pdo mssql,LINUX php mssql(odbc /pdo_dblib)

1 安装unixODBC和freeTDS

apt-get install unixodbc

apt-get install unixodbc-dev

apt-get install tdsodbc

apt-get install freetds-dev

apt-get install freetds-bin

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

sudo apt-get install php5-odbc

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没问题.

select * from t1

go

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

// $c = new PDO('odbc:dbserverdsn','sa','Admin888');

$query = 'SELECT top 5 * FROM userinfo';

$stmt = $c->query( $query );

while ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ) {

print_r( $row );

}

$c = null;

?>

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

?>

我的/etc/odbc.ini

[dbserverdsn]

Driver = FreeTDS

Description = ODBC to SQLServer via FreeTDS

Trace = No

Servername = 192.168.1.103

Database = sc

cat /etc/odbcinst.ini

[FreeTDS]

Description = MS SQL SERVER

Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so

Setup = /usr/lib/arm-linux-gnueabihf/odbc/libtdsS.so

FileUsage = 1

client charset = utf-8

cat /etc/freetds/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

[192.168.1.103]

host = 192.168.1.103

port = 1433

tds version = 7.0

$c = new PDO('odbc:dbserverdsn','sa','Admin888');

$query = 'SELECT top 5 * FROM t1';

$stmt = $c->query( $query );

while ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ) {

print_r( $row );

}

$c = null;

PDO_DBLIB:

sudo apt-get install php5-sybase  安装DBLIB扩展

在MS SQL SERVER的发展历史中，微软最初就是和Sybase合作的

中文乱码:

连接字符串里加charset=GBK，freetds的配置文件里的[global]中加一句client charset = GBK 等等

freetds使用的源码1.0编译安装到/usr/local/freetds  安装好后就会有tsql工具可以执行SQL

ini_set('display_errors','On'); #如果有错，展示详细错

$db = new PDO("dblib:host=192.168.1.10:1433;dbname=Card;","sa","a88");

$sql = "Select top 5 ID,CardID, PInTime from CprCardConsumeRecordTmpxxxx";

foreach ($db->query($sql) as $rows)

{

$f1 = $rows[2]; // 中文乱码

//$f1 = iconv('GBK', 'UTF-8//IGNORE', $rows[1]);

print_r($f1);

}

$db = null;

?>

require_once(__DIR__.'/../class/ORM.php');

ORM::configure("dblib:host=192.168.0.10:1433;dbname=Card;");

ORM::configure('return_result_sets',true); // returns result sets

ORM::configure('username', 'sa');

ORM::configure('password', 'Admin');

$db = ORM::get_db();

$o = ORM::for_table('CprCardConsumeRecordTmp')->limit(20)->find_array();

print_r( $o);