杭州神话信息技术有限公司

标题: 怎样把记事狗从Mysql转到MSSQL2005 [打印本页]

作者: aison 时间: 2010-9-3 14:46:34 标题: 怎样把记事狗从Mysql转到MSSQL2005

[i=s] 本帖最后由 aison 于 2010-9-3 14:47 编辑 [/i]

这是程序里Mysql的连接代码怎么改到SQL2005啊？请达人帮忙

class MySqlHandler extends DatabaseHandler
{
var $TableName;  var $FieldList;

var $Charset='gbk';

function MySqlHandler($server_host, $server_port = '3306')
{
  $this->DatabaseHandler($server_host, $server_port);
}

function DoConnect($username, $password, $database, $persist = true,$setkey=1)
{
  $host = $this->ServerHost . ':' . $this->ServerPort;
  if($persist)
  {
@$db=mysql_pconnect($host, $username, $password,true);
  }
  else
  {
@$db=mysql_connect($host, $username, $password,true);
  }
  $db==false?exit(mysql_errno().":".mysql_error())this->setConnectionId($db);
  if($this->GetVersion() > '4.1')
  {
if($this->Charset)
{
@mysql_query("SET character_set_connection={$this->Charset},
      character_set_results={$this->Charset},
      character_set_client=binary",$db);
}

if($this->GetVersion() > '5.0.1')mysql_query("SET sql_mode=''",$db);
  }
  if(false == mysql_select_db($database, $this->GetConnectionId()))
  {
$this->setConnectionId(0);
  }
}

function Charset($charset)
{
  $this->Charset = $charset;
}

function Query($sql,$type='SKIP_ERROR')
{
  if(true===DEBUG)
  {
$debug_list = debug_backtrace();
foreach($debug_list as $key => $debug)
{
if($debug['file'] != __FILE__ and basename($debug['file']) != 'cache.db.php')
{
   if($debug['class'] == __CLASS__ or $debug['class'] == 'cachehandler')
   {
   $file = $debug['file'];
   $line = $debug['line'];
   }
}
}
$start = explode(" ", microtime());
$start = $start[1] + $start[0];
  }
  $func=$type==='UNBUFFERED'?'mysql_unbuffered_query':'mysql_query';
  $result = $func($sql, $this->GetConnectionId());
  if($result==false)
  {
if(in_array($this->GetLastErrorNo(), array(2006, 2013)) && substr($type, 0, 5) != 'RETRY') {
$this->CloseConnection();

include ROOT_PATH . 'setting/settings.php';
$this->MySqlHandler($config['db_host'],$config['db_port']);
$this->Charset($config['charset']);
$this->DoConnect($config['db_user'],$config['db_pass'],$config['db_name'],$config['db_persist'],0);

$result = $this->Query($sql, 'RETRY'.$type);
} elseif (in_array($this->GetLastErrorNo(), array(1040)) && substr($type,0,4) != "WAIT" && substr($type,0,5) < "WAIT3") {
usleep(100000 * max(1,min(6,2 * ((int) substr($type,4,1) + 1))));

$result = $this->Query($sql, 'WAIT'.++$WAITTIMES.$type);
} elseif ($type != 'SKIP_ERROR' && substr($type, 5) != 'SKIP_ERROR') {
die($this->GetLastError($sql, $file, $line));
} else {
return false;
}
  }

  return new MySqlIterator($result);
}

function SetTable($tableName,$skip_error=false)
{
  $this->TableName = $tableName;
  if(isset($this->Table[$tableName]))
  {
$this->FieldList = $this->Table[$tableName];
  }
  else
  {
if (($fieldList=cache("table/{$tableName}",-1))===false)
{
$sql = "SHOW \n\tCOLUMNS \nFROM \n\t`{$this->TableName}`";
$query = $this->Query($sql,$skip_error?"SKIP_ERROR":"");
if($query==false)return false;
$fieldList = array();
while($row = $query->GetRow())
{
   if($row['Extra'] === "auto_increment")
   {
   $fieldList[$row['Key']] = $row['Field'];
   }
   else
   {
   $fieldList[] = $row['Field'];
   }
}
cache($fieldList);
}
$this->FieldList = $fieldList;
$this->Table[$tableName] = $fieldList;
  }
  Return $this->FieldList;
}
      function Select($id = '', $condition = NULL, $fields = "*")
{
  if($condition === NULL)
  {
if($ids = $this->BuildIn($id))
{
$where = "\r\nWHERE \n\t" . $ids;
}
else
{
Return false;
}
  }
  else
  {
if(trim($condition) != "")
{
$where = "\r\nWHERE \n\t" . $condition;
}
  }
  $fieldNames = "\n\t*";
  $field_num=0;
  if($fields != "*")
  {
$fieldNames = "\n\t".$this->FieldList['PRI'];
if(is_string($fields) != false)
{
$field_list = explode(',', $fields);
}elseif(is_array($fields) != false)
{
$field_list = array_filter($fields, 'strlen');
}
$valid_field_list=array();
foreach($field_list as $key => $field)
{
if(in_array($field, $this->FieldList))
{
   $fieldNames .= ",\n\t`" . $field . '`';
   $valid_field_list[]=$field;
}
}
$field_num=count($valid_field_list);
$fieldNames = ($field_num>=1)?ltrim($fieldNames, ",\n\t"):"\n\t*";
  }
  $sql = "SELECT {$fieldNames} \nFROM \n\t`{$this->TableName}` {$where}";
  $query = $this->query($sql);
  $data_list = array();
  if($field_num==1)$field_name=implode('',$valid_field_list);
  if($query->GetNumRows() > 1)
  {
while($row = $query->GetRow())
{
$data_list[$row[$this->FieldList['PRI']]] =($field_num==1)?$row[$field_name]row;
}
  }
  else
  {
$row = $query->GetRow();
$data_list =($field_num==1)?$row[$field_name]row;
  }
  Return $data_list;
}
   function Replace($dataList)
{
  if($dataList == "")Return false;
  foreach($this->FieldList as $key => $field)
  {
if(isset($dataList[$field]))
{
$fieldNames .= ",\n\t`" . $field . '`';
$fieldValues .= ",\n\t\"" . $dataList[$field] . "\"";
}
  }
  $sql = sprintf("REPLACE INTO \n\t`%s`(%s) \nVALUES(%s)", $this->TableName, ltrim($fieldNames, ','), ltrim($fieldValues, ','));
  $this->query($sql);
  return $this->Insert_ID();
}
   function Insert($dataList,$continue_primary_key=true)
{
  if(($sql=$this->BuildInsert($this->TableName,$dataList,$continue_primary_key,true))=="")return false;
  $this->query($sql);
  return $this->Insert_ID();
}
      function Update($dataList, $condition = NULL)
{
  if(($sql=$this->BuildUpdate($this->TableName,$dataList,$condition,true))=="")return false;
  if ($this->query($sql))
  {
return $this->AffectedRows();
  }
  else
  {
return false;
  }
}
   function Delete($id = "", $condition = NULL)
{
  if($condition === NULL)
  {
if($ids = $this->BuildIn($id))
{
$where = "WHERE " . $ids;
}
else
{
Return false;
}
  }
  else
  {
if(trim($condition) != "")
{
$where = "\r\nWHERE \n\t" . $condition;
}
  }
  $sql = "DELETE FROM `{$this->TableName}` {$where}";
  if ($this->query($sql))
  {
return $this->AffectedRows();
  }
  else
  {
return false;
  }
}
function BuildField($mixed)
{
  if($mixed==false or trim($mixed)=="*")Return "*";
  $type=gettype($mixed);
  if($type=="string" or $type=="integer" or $type=="double")
  {
$mixed=trim($mixed,',');
$mixed=strpos($mixed,',')!==false?"'".str_replace(',',"`,`",$mixed)."'":"`$mixed`";
  }
  elseif($type=="array")
  {
$mixed="`".implode("`,`",$mixed)."`";
  }
  Return $mixed;
}

function BuildInsert($tableName,$dataList,$continue_primary_key=true,$filterValid=false)
{
  if(is_array($dataList) == false)Return '';
  if($filterValid===true)
  {
$this->SetTable($tableName);
foreach($this->FieldList as $key => $field)
{
if(strcmp($key, "PRI") === 0 and $continue_primary_key===true)
{
   continue;
}
if(isset($dataList[$field]))
{
   $fieldNames .= ",\n\t`" . $field . '`';
   $fieldValues .= ",\n\t\"" . $dataList[$field] . "\"";
}
}
if ($fieldNames=='' or $fieldValues=='')return '';
  }
  else
  {
foreach($dataList as $field=>$value)
{
$fieldNames .= ",\n\t`" . $field . '`';
$fieldValues .= ",\n\t\"" .$value. "\"";
}
$this->TableName=$tableName;
  }
  $sql = sprintf("INSERT INTO \n\t`%s`(%s) \nVALUES(%s)", $tableName, ltrim($fieldNames, ','), ltrim($fieldValues, ','));
  return $sql;
}

function BuildUpdate($tableName,$dataList,$condition=null,$filterValid=false)
{
  if(is_array($dataList) == false)Return '';
  if($filterValid===true)
  {
$this->SetTable($tableName);
foreach($this->FieldList as $key => $field)
{
if(isset($dataList[$field]))
{
   if($key === "PRI")
   {
   if($ids = $this->BuildIn($dataList[$field]))$where = "WHERE \n\t" . $ids;
   }
   else
   {
   $value=$dataList[$field];
   $fieldUpdate .=strpos($value, 'eval:') === 0?"\n\t`{$field}`=".substr($value, 5).",":"\n\t`{$field}`='{$value}',";
   }
}
}
  }
  else
  {
$this->TableName=$tableName;
foreach ($dataList as $field=>$value)
{
$fieldUpdate .= strpos($value, 'eval:') === 0?"\n\t`{$field}`=".substr($value, 5).",":"\n\t`{$field}`='{$value}',";
}
  }
  if($fieldUpdate == '')Return '';
  if($condition !== NULL)
  {
$where = (trim($condition) != "")?"WHERE \n\t" . $condition:"";
  }
  elseif($filterValid==true)
  {
if($dataList[$this->FieldList['PRI']] == "")Return '';
  }
  $sql = sprintf("UPDATE \n\t`%s` \t\nSET %s \n%s", $this->TableName, rtrim($fieldUpdate, ','), $where);
  return $sql;
}

楼下继续

作者: aison 时间: 2010-9-3 14:46:58

[i=s] 本帖最后由 aison 于 2010-9-3 14:48 编辑 [/i]

function BuildIn($mixed,$name=null)
{
  if($name === NULL)$name = $this->FieldList['PRI'];
  $type=gettype($mixed);
  if($type=="string" or $type=="integer" or $type=="double")
  {
$mixed=trim($mixed,',');
$mixed=strpos($mixed,',')!==false
   ?"'".str_replace(',',"','",$mixed)."'"
   :"'$mixed'";
  }
  elseif($type=="array")
  {
$mixed=!empty($mixed)?"'".implode("','",array_unique($mixed))."'":'null';
  }

  Return $name!=null?"$name IN ($mixed)"mixed;
}

function GetVersion()
{
  return mysql_get_server_info($this->GetConnectionId());
}

function GetLastError($sql, $file, $line)
{
  $error = mysql_error($this->GetConnectionId());
  return $error . $sql;
}
function GetLastErrorString()
{
  return mysql_error($this->GetConnectionId());
}
function GetLastErrorNo()
{
  return mysql_errno($this->GetConnectionId());
}

function Insert_ID()
{
  return mysql_insert_id($this->GetConnectionId());
}
function LastInsertId()
{
  $this->Insert_ID();
}

function AffectedRows()
{
  Return mysql_affected_rows($this->GetConnectionId());
}

function CloseConnection()
{
  return mysql_close($this->GetConnectionId());
}
}

class MySqlIterator
{

var $_resource_id;

var $_current_row;

var $_total_rows;

function MySqlIterator($resource_id)
{
  $this->_resource_id = $resource_id;
  $this->_total_rows = 0;
  $this->_current_row = 0;
}

function GetNumRows()
{
  $this->_total_rows = mysql_num_rows($this->GetResourceId());
  return $this->_total_rows;
}

function GetNumFields()
{
  return mysql_num_fields($this->GetResourceId());
}

function GetResourceId()
{
  return $this->_resource_id;
}

function GetCurrentRow()
{
  return $this->_current_row;
}

function isSuccess()
{
  return $this->GetResourceId() ? true : false;
}

function _freeResult()
{
  mysql_free_result($this->GetResourceId());
  return;
}

function GetRow($result_type = 'assoc')
{
  $this->_current_row++;
  switch($result_type)
  {
case 'row':
return mysql_fetch_row($this->GetResourceId());
break;
case 'assoc':
return mysql_fetch_assoc($this->GetResourceId());
break;
case 'both':
return mysql_fetch_array($this->GetResourceId());
break;
case 'object':
return mysql_fetch_object($this->GetResourceId());
break;
  }
}
function result($row)
{
  return mysql_result($this->GetResourceId(),$row);
}
function GetAll($result_type = 'assoc')
{
  $list = array();
  while($row = $this->GetRow($result_type))
  {
$list[] = $row;
  }
  Return $list;
}
}

作者: micwolf 时间: 2010-9-3 18:22:56

为了能让PHP连接MSSQL,系统需要安装MSSQL,PHP,且在PHP.ini中的配置中,将
   ;extension=php_mssql.dll前面的;去掉

   1.连接MSSQL
   $conn=mssql_connect("实例名或者服务器IP","用户名","密码");

   //测试连接
   if($conn)
   {
   echo "连接成功";
   }

   2.选择要连接的数据库
   mssql_select_db("dbname");

   3.执行查询
   $rs = mssql_query("select top 1 id,username from tbname",$conn);
   或者直接执行update,insert等语句,可以不用为返回结果赋值
   mssql_query("update tbname set username='niunv' where id=1");

   4.获取记录集行数
   echo mssql_num_rows($rs);

   5.获取记录集
   if($row=mssql_fetch_array($rs))
   {
   $id = $row[0];//获取ID字段值
   $username = $row[1];//获取username字段值
   }

   6.获取新增记录的ID
   将id字段设置为IDENTITY字段,执行insert语句以后,就会产生一个 @@IDENTITY 全局变量值,查询出来就是最后一条新增记录的ID了.
   mssql_query("insert into tbname(username) values ('nv')",$conn);
   $rs = mssql_query("select @@IDENTITY as id",$conn);
   if($row=mssql_fetch_array($rs))
   {
   echo $row[0];
   }

   7.释放记录集
   mssql_free_result($rs);

   8.关闭连接
   mssql_close($conn);

作者: aison 时间: 2010-9-6 14:43:04

回复 3# micwolf 额试着改了一下，满篇的错误啊！大哥能帮帮忙不？我不懂php

欢迎光临杭州神话信息技术有限公司 (http://cenwor.com/)