php连接mssql的方法（ADODB）

zhgc · 发表于 2020-4-19 14:30:48

本帖最后由 zhgc 于 2020-4-19 14:36 编辑

php连接mssql的方法（ADODB）

Microsoft Drivers for PHP for SQL Server

https://www.microsoft.com/en-us/download/details.aspx?id=20098

Version 4.0 supports PHP 7.0+ on Windows and Linux
Version 3.2 supports PHP 5.6, 5.5, and 5.4 on Windows

php是5.6.31 -->SQLSRV32.EXE

Version       3.0.2.2R (Unofficial)
Unofficial Changes       Works with Native Clients 11/10/9

There are different versions of the Native Client components for each SQL Server.

SQL Server 2005 - Native Client 9.0
SQL Server 2008 - Native Client 10.0
SQL Server 2012 - Native Client 11.0

SELECT
SERVERPROPERTY('servername') AS 实例名,
SERVERPROPERTY('ProductVersion') AS 实例版本,
SERVERPROPERTY('Edition') AS 产品版本,
SERVERPROPERTY('ProductLevel') AS 版本级别,
@@VERSION AS 版本信息

找SQL Server 2008 - Native Client 10.0

<?php
require_once("adodb/adodb.inc.php"); //版本升级为5.20.14
//string ERPconn = "Data Source=10.0.10.70;Password=Xc;User ID=zf;Initial Catalog=AHXC";
$db = ADONewConnection('mssqlnative');
//$db->setConnectionParameter('ReturnDatesAsStrings',false);
//$db->connect('SERVER\SQLEXPRESS','user','password','NORTHWND');
$db->setConnectionParameter('characterSet','UTF-8');
$db->connect("10.0.10.70","zf","Xc","AHXC");
$sql="select getdate()";
$rs = $db->Execute($sql);
print_r($rs->GetRows());

复制代码

成功！

结论：
1. php不再自带连接mssql的模块，需要去微软驱动SQLSRVXX.EXE
2. 微软官方提供的驱动都是32位的，不支持64位的。要找非观方的64位版
3. 一定要下载对应版本的Native Client

zhgc · 发表于 2020-12-13 23:13:37

保存位置

\tools.syant\php.syant\SQLServer_MSSQL_PHP

zhgc · 发表于 2020-12-13 23:18:48

保存位置

\tools.syant\php.syant\SQLServer_MSSQL_PHP

zhgc · 发表于 2021-1-28 22:11:31

再次总结：

1.sqlncli64.msi一定要安装，他实际是SQL SERVER 2008 Native Client

2.SQLSRV32.EXE已经没有安装必要，因为只有32位的，没有64位的

只接把64位目录中的
php_pdo_sqlsrv_56_ts.dll
php_sqlsrv_56_ts.dll
放在php的ext目录

然后在php.ini中加上
extension=php_mysql.dll
extension=php_mysqli.dll
extension=php_pdo_sqlsrv_56_ts.dll
extension=php_sqlsrv_56_ts.dll

这样php就可以连通

zhgc · 发表于 2021-9-21 11:12:26

第一个案例分析：

'确定BOM
If (bCheckBom) Then
sUrl = "http://192.168.100.18/mesapi/index.php/erp/bom/checkbom/" & Trim(Me.cmbproduct.Text) & "/save?character=gbk"
sBom = GetUrlStr(sUrl)
'{"tag":418,"res":"OK","msg":"","tick":0.104,"time":"2020-04-21 20:19:41","log":["DGSC找到8HC019CMMA0!","1e50e588ef00c898b2cbc65bd6a96479","MES有记录1"],"data":[]}
If InStr(sBom, "res"":""OK""") > 0 Then
MsgBox "注意：" & Trim(Me.cmbproduct.Text) & "料表ERP数据同步成功！"
Else
MsgBox "警告：" & Trim(Me.cmbproduct.Text) & "料表ERP数据同步不成功！"
' Screen.MousePointer = vbDefault
' Exit Sub
End If
End If

复制代码

Attribute VB_Name = "MesAPI"
Option Explicit
'vb代替inet控件获得网页源代e79fa5e98193e78988e69d8331333363386162码（解决代码不完整的问题）
Public Const INTERNET_OPEN_TYPE_PRECONFIG = 0
Public Const INTERNET_OPEN_TYPE_DIRECT = 1
Public Const INTERNET_OPEN_TYPE_PROXY = 3
Public Const scUserAgent = "Microsoft Internet Explorer 6.0"
Public Const INTERNET_FLAG_RELOAD = &H80000000
Public Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long
Public Declare Function InternetOpenUrl Lib "wininet.dll" Alias "InternetOpenUrlA" (ByVal hOpen As Long, ByVal sUrl As String, ByVal sHeaders As String, ByVal lLength As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long
Public Declare Function InternetReadFile Lib "wininet.dll" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer
Public Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer
Public Function GetUrlStr(URL) As String '调用时 URL 参数一定要带 http:// 前缀.
Dim hOpen As Long
Dim hOpenUrl As Long
Dim sUrl As String
Dim bDoLoop As Boolean
Dim bRet As Boolean
Dim sReadBuffer As String * 2048
Dim lNumberOfBytesRead As Long
Dim sBuffer As String
sUrl = URL
hOpen = InternetOpen(scUserAgent, INTERNET_OPEN_TYPE_PRECONFIG, vbNullString, vbNullString, 0)
hOpenUrl = InternetOpenUrl(hOpen, sUrl, vbNullString, 0, INTERNET_FLAG_RELOAD, 0)
bDoLoop = True
While bDoLoop
sReadBuffer = vbNullString
bRet = InternetReadFile(hOpenUrl, sReadBuffer, Len(sReadBuffer), lNumberOfBytesRead)
sBuffer = sBuffer & left$(sReadBuffer, lNumberOfBytesRead)
If Not CBool(lNumberOfBytesRead) Then bDoLoop = False
Wend
GetUrlStr = sBuffer
If hOpenUrl <> 0 Then InternetCloseHandle (hOpenUrl)
If hOpen <> 0 Then InternetCloseHandle (hOpen)
End Function

复制代码

zhgc · 发表于 2021-9-21 11:17:58

MSSERVER/MSSQL类型ERP的连接

<?php
/*
* 全飞鸿后台接口
* 本模块设计来处理erp数据
* 有任何问题请联系syant/13760216621
*/
require_once("adodb/adodb.inc.php"); //版本升级为5.20.14
class erp extends tapp{
var $db;
/*
* MES系统EPR数据处理接口 v1.0
* DGSC,AHXC, XCLK，OMKJ,XGLK
*/
function erp() {
parent::__construct(); //一定要添加这一句
$this->db = ADONewConnection('mssqlnative');
//$this->db->setConnectionParameter('ReturnDatesAsStrings',false);
$this->db->setConnectionParameter('characterSet','UTF-8');
$this->db->connect("10.0.10.0","Xc","2018","DGSC"); //AHXC
$this->db->SetFetchMode(ADODB_FETCH_NUM); //ADODB_FETCH_NUM ADODB_FETCH_ASSOC ADODB_FETCH_BOTH
}
}

复制代码

zhgc · 发表于 2021-9-21 11:23:07

本帖最后由 zhgc 于 2021-9-21 11:24 编辑

<?php
/*
* 全飞鸿后台接口
* 本模块设计来处理erp数据
* 有任何问题请联系syant/13760216621
*/
class BOM extends tpage{
function BOM($app) {
parent::__construct($app);
$app->setDebug(true);//开发期间打开debug
}

复制代码

function getRealLoc($loc){
$loc=str_replace(" ",",",$loc);
$loc=str_replace("；",",",$loc);
$loc=str_replace("，",",",$loc);
$loc=str_replace(";",",",$loc);
$loc=str_replace("；",",",$loc);
$loc=str_replace("\r\n",",",$loc);
$loc=str_replace("\n\r",",",$loc);
$loc=str_replace("\r",",",$loc);
$loc=str_replace("\n",",",$loc);
return $loc;
}

复制代码

function getBOMinfo_bydb($PNO,$ZT){
$data=array();
$sql="select CB001 主件品号,CB005 元件品号,CB021 位号 from {$ZT}.dbo.BOMCB where CB001 = '{$PNO}'";
//$sql="select t1.CB005 元件,t2.MB004 替代料,t2.MB002 主件品号,t1.CB021 位号 from DGSC.dbo.BOMCB t1,DGSC.dbo.BOMMB t2
//where t1.CB005=t2.MB001 and t1.CB001 = '8HC011CMM80' and t2.MB002 in ('8HC011CMM80','********************')";
$rs = $this->app->db->Execute($sql);
if($rs){
if(!$rs->EOF){
$this->app->saveLog("{$ZT}找到{$PNO}!");
$data["bom"]=$rs->GetRows();
//
$sql="select t1.CB005 元件,t2.MB004 替代料,t2.MB002 主件品号,t1.CB021 位号 from {$ZT}.dbo.BOMCB t1,{$ZT}.dbo.BOMMB t2 where t1.CB005=t2.MB001 and t1.CB001 = '{$PNO}' and t2.MB002 in ('{$PNO}')";
$rss = $this->app->db->Execute($sql);
$data["replace"]=$rss->GetRows();
//
$sql="select t1.CB005 元件,t2.MB004 替代料,t2.MB002 主件品号,t1.CB021 位号 from {$ZT}.dbo.BOMCB t1,{$ZT}.dbo.BOMMB t2 where t1.CB005=t2.MB001 and t1.CB001 = '{$PNO}' and t2.MB002 in ('********************')";
$rsss = $this->app->db->Execute($sql);
$data["common"]=$rsss->GetRows();
} else {
$this->app->saveLog("{$ZT}没有{$PNO}!");
}
} else {
$this->app->saveLog($db->errorMsg());
}
return $data;
}

复制代码

function getBOMInfo($PNO){
$data=array();
$ZT="DGSC";
$data=$this->getBOMinfo_bydb($PNO,$ZT);
if(empty($data)){
$this->app->saveLog("{$ZT}未找到{$PNO}!");
$ZT="AHXC";
$data=$this->getBOMinfo_bydb($PNO,$ZT);
}
if(empty($data)){
$this->app->saveLog("{$ZT}未找到{$PNO}!");
$ZT="XCLK";
$data=$this->getBOMinfo_bydb($PNO,$ZT);
}
if(empty($data)){
$this->app->saveLog("{$ZT}未找到{$PNO}!");
}
return $data;
}

复制代码

/*
* 确保与ERP是同步的数据
* [url]http://192.168.100.18/mesapi/index.php/erp/bom/checkbom/8HC011CMM80[/url] (在ERP中检查最新的BOM)
* [url]http://192.168.100.18/mesapi/index.php/erp/bom/checkbom/8HC011CMM80/save[/url] (将ERP中的BOM保存到MES)
*/
function checkbom(){
//http://192.168.100.18/mesapi/index.php/erp/bom/checkbom/8HC011CMM80/save
//$segment : Array\n(\n [0] => erp\n [1] => bom\n [2] => checkbom\n [3] => 8HC011CMM80\n [4] => save \n)\n"
//$uri : "erp\/bom\/checkbom\/8HC011CMM80\/save\/"
global $segment;
$pno="";
$bSave=true; //false to true
//1. 参数处理
if(count($segment)<4){
$this->app->FAIL("没有传机种名称！");
return;
} else {
$pno=$segment[3];
if(count($segment)>4){
if($segment[4]=="save"){
$bSave=true;
}
}
}
//2. 查询ERP数据库
$data=$this->getBOMInfo($pno);
if(!empty($data)){
$commom=$data["common"];
$data["common"]=array();
//
$sss=json_encode($data,JSON_UNESCAPED_UNICODE);
$ccc=json_encode($commom,JSON_UNESCAPED_UNICODE);
$sssmd5=md5($sss);
$cccmd5=md5($ccc);
$this->app->saveLog($sssmd5);
if($this->app->prepare_mes(false)){ //true表示用standby
//comm
if(bSave){
$this->savecommon($cccmd5,$commom);
}
//
$mysql="select count(1) from smt.c_bom where p_no='{$pno}' and version='01' and bom_code='{$sssmd5}' limit 1";
$icount=$this->app->mes->getone($mysql)*1;
$this->app->saveLog("MES有记录{$icount}");
if($icount==0){
if($bSave){
$this->savebom($pno,$sssmd5,$data);
return ;
} else {
//$this->app->jsdata["res"]="FAIL";
//$this->app->jsdata["msg"]="警告：MES系统监控到ERP已修改了{$pno}机型BOM数据！";
$this->app->FAIL("警告：MES系统监控到ERP已修改了{$pno}机型BOM数据！");
return ;
}
} else {
//$this->app->jsdata["res"]="OK"; //是OK的，未变化！
$this->app->OK();
return ;
}
} else{
//$this->app->jsdata["msg"]=$sssmd5;
};
//
} else {
//$this->app->jsdata["res"]="FAIL";
//$this->app->jsdata["msg"]="ERP中没有BOM";
$this->app->FAIL("ERP中没有机型{$pno}的BOM数据");
return ;
}
}

复制代码

function savecommon($md5,$data){
//$start=$this->app->getTickCount();
$PNO="MES_COMMON_KP";
$mysql="select count(1) from smt.c_bom where p_no='{$PNO}' and version='01' and bom_code='{$md5}' limit 1";
$icount=$this->app->mes->getone($mysql)*1;
if($icount>0){
return ;// OK
}
$sqls=array();
$sqls[]="update smt.c_bom set end_time=now() where p_no='{$PNO}' and version='01'";
$sqls[]="delete from smt.c_bom where p_no='{$PNO}' and version='01'";
$sqls[]="insert into smt.c_bom(bom_code,p_no,version,cust_code,update_time,side,flag) values ('{$md5}','{$PNO}','01','MI01',now(),'BT',1); ";
$sqls[]="delete from smt.c_bom_replace where BOM_CODE='{$PNO}'";
//分析替代料
foreach($data as $v){
//"33080101 ","33080100 ","********************","C25,C31"
$location=$this->getRealLoc($v[3]);
$kp=$v[0];
$rkp=$v[1];
$locs=explode(",",$location);
$icount=0;
foreach($locs as $loc){
if(strlen(trim($loc))>0){
$sql="insert into smt.c_bom_replace(BOM_CODE,CUST_KP_NO,REPLACE_NO,LOCATION,DOWNLOADTIME,EMP) values ('{$PNO}','{$kp}','{$rkp}','{$loc}',now(),'SYANT')";
$sqls[]=$sql;
$icount=$icount+1;
}
}
}

复制代码

            //开始做事务保存
            //$this->app->saveLog("do common sql:".print_r($sqls,true));
            if($this->app->prepare_mes(false)){
                     $this->app->mes->BeginTrans();
                     foreach($sqls as $sql){
                              $rst=$this->app->mes->Execute($sql); //
                              if($rst){
                                    //
                              }else{
                                    //$this->app->FAIL('execute error:  '.$this->app->mes->ErrorMsg()."  SQL:".$sql);
                                    $this->app->saveLog('Execute error:  '.$this->app->mes->ErrorMsg()."  SQL:".$sql);
                                    break;
                              }
                     }
                     if($rst){
                              $this->app->mes->CommitTrans();
                              $this->app->saveLog("通用替代料保存成功！");
                              //$this->app->OK("保存成功！");
                     }else{
                              $this->app->mes->RollbackTrans();
                     }
            } else {
                     $this->app->FAIL("MES数据库连接不可用！");
            }
      }

/*
* 保存BOM数据到MES中
*/
function savebom($PNO,$md5,$data){
$start=$this->app->getTickCount();
$sqls=array();
$sqls[]="update smt.c_bom set end_time=now() where p_no='{$PNO}' and version='01'";
$sqls[]="delete from smt.c_bom where p_no='{$PNO}' and version='01'";
$sqls[]="insert into smt.c_bom(bom_code,p_no,version,cust_code,update_time,side,flag) values ('{$md5}','{$PNO}','01','MI01',now(),'BT',1); ";
$sqls[]="delete from smt.c_bom_location where bom_code='{$md5}'";
$sqls[]="delete from smt.c_bom_kp where bom_code='{$md5}'";
$sqls[]="delete from smt.c_bom_replace where bom_code='{$md5}'";
//分析主料
foreach($data["bom"] as $v){
//$this->app->jsdata["msg"]=print_r($v,true);
//Array([0] => 8HC011CMM80 [1] => 11508534 [2] => U4,U6,U7)
//$location=$v[2];
$location=$this->getRealLoc($v[2]);
$kp=$v[1];
$locs=explode(",",$location);
$icount=0;
foreach($locs as $loc){
if(strlen(trim($loc))>0){
$sql="insert into smt.c_bom_location(bom_code,cust_kp_no,location,replace_flag) values ('{$md5}','{$kp}','{$loc}',1)";
$sqls[]=$sql;
$icount=$icount+1;
}
}
$sqls[]="insert into smt.c_bom_kp(bom_code,cust_kp_no,kp_desc,std_qty)
values ('{$md5}','{$kp}','{$kp}','{$icount}')";
}
//分析替代料
foreach($data["replace"] as $v){
//t1.CB005 元件,t2.MB004 替代料,t2.MB002 主件品号,t1.CB021 位号
//$location=$v[3];
$location=$this->getRealLoc($v[3]);
$kp=$v[0];
$rkp=$v[1];
$locs=explode(",",$location);
$icount=0;
foreach($locs as $loc){
if(strlen(trim($loc))>0){
$sql="insert into smt.c_bom_replace(BOM_CODE,CUST_KP_NO,REPLACE_NO,LOCATION,DOWNLOADTIME,EMP) values ('{$md5}','{$kp}','{$rkp}','{$loc}',now(),'SYANT')";
$sqls[]=$sql;
$icount=$icount+1;
}
}
}
//开始做事务保存
if($this->app->prepare_mes(false)){
$this->app->mes->BeginTrans();
foreach($sqls as $sql){
$rst=$this->app->mes->Execute($sql); //
if($rst){
//
}else{
$this->app->FAIL('execute error: '.$this->app->mes->ErrorMsg()." SQL:".$sql);
break;
}
}
if($rst){
$this->app->mes->CommitTrans();
$this->app->OK("保存成功！");
}else{
$this->app->mes->RollbackTrans();
}
} else {
$this->app->FAIL("MES数据库连接不可用！");
}
}

复制代码

}
?>

		自动登录	找回密码
密码			立即注册

php连接mssql的方法（ADODB）

浏览过的版块