php7版本安装微软的驱动后,安装扩展pdo_sqlsrv即可.低版本使用dblib扩展
Centos7下LNMP,Php7.3连接sqlserver
扩展下载地址,我下载的是5.6.1 stable版本
目前还没有验证直接下载扩展安装成功的必要条件是不是已经让之前的很多操作实现了.
以上地址中含有dll文件,在win下我没有测试,我是根据以下方式安装成功的.
Win10 下连接sqlserver
我是按着网上的说明,根据如下图所示找到对应版本后安装成功的,具体参考
image
微软的yum源: curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssqlrelease.repo
防止冲突卸载原有驱动: yum remove unixODBC
安装驱动: yum install msodbcsql mssql-tools unixODBC-devel
LAMP环境下,PHP5.6连接SqlServer方法如下
首先安装 FreeTDS,再安装pdo_dblib扩展,否则报错
# wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz
# tar -zxvf freetds-patched.tar.gz
# cd freetds-1.00.48/
# ./configure --prefix=/usr/local/freetds --with-tdsver=7.3 --enable-msdblib
# make && make install
安装pdo_dblib扩展,没有源码可查看php版本后去官网下载同版本php即可
pdo连接数据库时中文乱码,一定要指定utf-8,如下
$mssql = new PDO('dblib:host=10.0.6.218;dbname=UFDATA_001_2018;charset=UTF8', "sa","abc@123");
if( !$mssql ){
die("连接错误啦！".mssql_error()).'<br /><br />';
}else{
echo "连接成功了！<br /><br />";
}
$sql = "SELECT * from AA_Agreement";
$res = $mssql->prepare($sql);
$res->execute();
while ($row = $res->fetch()) {
echo "<pre>";
print_r($row);
echo "</pre>";
}
unset($mssql);
unset($res);
mssql_close($mssql);
dblib和odbc等这些扩展是依赖pdo.so的,所以必须在引入pdo.so之后才能引入
extension=pdo_dblib.so
extension=pdo_odbc.so
于是找到php.d/pdo.ini将这两个模块的引入放到pdo.so的后面即可(我的机器是在/etc目录下)
php代码如下
<?php
/**
* Class Dblib
*/
class Dblib{
const SQL_SERVER2008_HOST = '127.0.0.1';
const SQL_SERVER2008_PORT = 1433;
const SQL_SERVER2008_NAME = 'db_qyim';
const SQL_SERVER2008_USER = 'XXX';
const SQL_SERVER2008_PASS = 'XXX';
const SQL_SERVER2008_DEBUG = true;
private static $pdo = null;
public function __construct()
{
if(!isset(self::$pdo) || empty(self::$pdo))
self::$pdo = new PDO('dblib:host='.self::SQL_SERVER2008_HOST.':'.self::SQL_SERVER2008_PORT.';dbname='.self::SQL_SERVER2008_NAME,self::SQL_SERVER2008_USER,self::SQL_SERVER2008_PASS);
}
/**
* @param $sql
* @param null $msg
* @return bool|int
*/
public function execute($sql,&$msg=null)
{
try
{
$fruit = self::$pdo->exec($sql);
}
catch(Exception $e)
{
$msg = $e->getMessage();
$fruit = false;
}
if(self::SQL_SERVER2008_DEBUG && self::SQL_SERVER2008_DEBUG == true)
{
self::err($sql);
self::err('-----------------------------------------');
self::err(self::$pdo->errorInfo());
}
return $fruit;
}
public function insert($sql,&$msg=null)
{
if($this->execute($sql,$msg))
{
return $this->getLastId();
}
return false;
}
public function getList($sql,&$msg=null)
{
$fruit = array();
$sth = self::$pdo->prepare($sql);
$sth->execute();
while ($row = $sth->fetch(PDO::FETCH_ASSOC))//返回一个关联数组PDO::FETCH_ASSOC
{
$fruit[] = $row;
}
return $fruit;
}
public function getRow($sql,&$msg=null)
{
$result = $this->getList($sql,$msg);
if(!empty($result) && !empty($result[0]))
{
return $result[0];
}
$msg = 'The result is empty';
return array();
}
public function arr2insertsql($data,$table_name='')
{
$fields = implode(',',array_keys($data));
$values = implode('\',\'',$data);
$sql = 'INSERT INTO '.$table_name.'('.$fields.') VALUES (\''.$values.'\');';
return $sql;
}
public function getLastId()
{
$sql = "select @@identity as id ;";
$result = $this->getRow($sql);
if($result && $result['id'])
{
return $result['id'];
}
return false;
}
public static function err($data = '',$pr='info')
{
error_log(date('Y-m-d H:i:s').' '.$pr.' : '.var_export($data,1).PHP_EOL,3,'/tmp/zzz.sqlserver2008.'.$pr.'.'.date('Y-m-d').'.logs');
}
/**
* 创建用户，直接调用该存储过程
* @param null $name
* @return string 成功后的用户ID
*/
public function callCreateUser($name = null)
{
$sql = <<<db
exec dbo.CreateUser @name=N'{$name}', -- nvarchar(256)
@nickname=N'{$name}', -- nvarchar(256)
@password=N'', -- nvarchar(256)
@email=N'', -- nvarchar(256)
@deptId=0, -- int
@subType=0 -- int
db;
$sth = self::$pdo->prepare($sql);
$sth->execute();
$result = $sth->fetchColumn();
return $result;
}
/**
* 创建多人会话，直接调用该存储过程
* @param null $master
* @param array $members
* @return string 成功后的会话ID
*/
public function callCreateTempGroup($master = null,$members = array())
{
$users = implode(',',$members);
$sql = <<<db
exec dbo.CreateTempGroup @creator={$master}, -- int
@members=N'{$users}' -- nvarchar(4000)
db;
$sth = self::$pdo->prepare($sql);
$sth->execute();
$result = $sth->fetchColumn();
return $result;
}
/**
* 创建群组，直接调用该存储过程
* @param null $master
* @param null $name
* @return string 成功后的群组ID
*/
public function callCreateGroup($master = null,$name = null)
{
$sql = <<<db
exec dbo.CreateGroup @creator={$master}, -- int
@name=N'{$name}', -- nvarchar(256)
@nickname=N'{$name}', -- nvarchar(256)
@deptId=0, -- int
@subType=0, -- int
@remark=N'{$name}' -- nvarchar(1024)
db;
$sth = self::$pdo->prepare($sql);
$sth->execute();
$result = $sth->fetchColumn();
return $result;
}
}
$db = new Dblib();
$sql = "SELECT top 1 * FROM Users;";
var_dump($db->getList($sql));
网友评论