Location: PHPKode > scripts > Yet Another Pager > yet-another-pager/CYap_Mssql.php
<?PHP
/**
 *
 * MSSQL interface module
 *
 * @copyright CYap_Mssql.php is part of Yap project {@link http://www.andrioli.com/en/yap.html} and it is LGPL
 * @author    Andrioli Darvin <darvin (inside) andrioli (dot) com>
 * @version   $Header: d:\cvs/classistd/yap/CYap_Mssql.php,v 1.8 2008/03/10 12:15:46 darvin Exp $
 */
/*
 * +-------------------------------------------------------------------------+
 * | Yap                                                                     |
 * +-------------------------------------------------------------------------+
 * | Copyright (c) 2003-2008 Andrioli Darvin                                 |
 * | Email         <darvin (inside) andrioli (dot) com>                      |
 * | Web           http://www.andrioli.com/en/yap.html                       |
 * | Download      http://www.phpclasses.org/browse.html/package/1391.html   |
 * |                                                                         |
 * +-------------------------------------------------------------------------+
 * | This library is free software; you can redistribute it and/or modify    |
 * | it under the terms of the GNU Lesser General Public License as          |
 * | published by the Free Software Foundation; either version 2 of the      |
 * | License, or (at your option) any later version.                         |
 * |                                                                         |
 * | This library is distributed in the hope that it will be useful, but     |
 * | WITHOUT ANY WARRANTY; without even the implied warranty of              |
 * | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU        |
 * | Lesser General Public License for more details.                         |
 * |                                                                         |
 * | You should have received a copy of the GNU Lesser General Public        |
 * | License along with this library; if not, write to the Free Software     |
 * | Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA |
 * +-------------------------------------------------------------------------+
 */
/**
 * @package CYap
 */
class CYap_MsSQL extends CYapDB {
/**
 * Tracks if the last query is a query with limit. Used to mimic the mysql's limit
 * statement
 * @var boolean
 */
var $UseLimit;
/**
 * If UseLimit set to true, this var counts down how many rows left to
 * the limit end
 * @var integer
 */
var $LimitToEnd;
/**
 * If UseLimit set to true, this var 'remember' the start limit
 * @var integer
 */
var $LimitStart;
/**
 * If UseLimit set to true, this var 'remember' the end limit
 * @var integer
 */
var $LimitEnd;
/**
 * Memorize the last row retruned for each result.
 * Used to track the columns type and number for query
 * @var array
 */
var $LastRow;
/**
 *
 * @see SelectDb()
 */
var $SaveUser;
/**
 *
 * @see SelectDb()
 */
var $SavePasswd;

function CYap_MsSQL()
{
CYapDB::CYapDB();
$this->UseLimit=false;
$this->LimitStart=0;
$this->LastRow=array();
$this->CurrentRow=array();
}
/**
 * Open the database connection
 * @param string $server database server
 * @param string $User username
 * @param string $Password User password
 * @return bool true on success, false on failure
 */
function OpenDb($DBSystem,$DBUser="",$DBPasswd="")
{
if($this->Db_Open)
   {
   $this->txtError='Database already open';
   return(false);
   }
$this->SaveUser=$DBUser;
$this->SavePasswd=$DBPasswd;
if(($this->DbConn=mssql_connect($DBSystem,$DBUser,$DBPasswd))===FALSE)
  {
  // Errore di select del db
  $this->txtError="Error during connection to the server ".$DBSystem;
  return(false);
  }
$this->Db_Open=true;
return(true);
}
/**
 * database selection. Always true, the ODBC function don't allow to
 * select any database
 * @param string $DBname database name
 * @return bool true on success, false on failure
 */
function SelectDb($DBname)
{
if(!$this->Db_Open)
   {
   $this->txtError='No database open';
   return(false);
   }
if(!mssql_select_db($DBname,$this->DbConn))
  {
  // Errore di select del db
  $this->txtError="I cannot select the db ".$DBname;
  return(false);
  }
return(true);
}
/**
 * Execute the given query
 * @param string $sql sql statement
 * @return mixed the result id, false on failure
 */
function DbExecSql($sql)
{
$this->UseLimit=FALSE;
$this->LimitStart=0;
if(!$this->Db_Open)
   {
   $this->txtError='No database open';
   return(false);
   }
if(($this->DbResult=mssql_query($sql,$this->DbConn))===false)
  {
  // Errore di select del db
  $this->txtError="Error into sql statement. Sql string: ".$sql.".Err:". mssql_get_last_message();
  return(false);
  }
$this->CurrentRow[$this->DbResult]=0;
$this->LastQuery[$this->DbResult]=$sql;
return($this->DbResult);
}

/**
 * Perform a select statement using range to limits the
 * rows to return
 * @param string $select select statement
 * @param integer $start no. rows to start
 * @param integer $many how many rows sholud be returned
 * @return bool true on success, false on failure
 */
function QueryLimit($select,$start=0,$many=-1)
{
// If I got an error, exit. The error text is set by DbExecSql
// and the caller should know what to do.
if(($res=$this->DbExecSql($select))===FALSE)
   return($res);
$this->UseLimit=TRUE;
// yap start limi is zero based, the odbc start from 1
$this->LimitStart=$start;
if($many!=-1)
   {
   $this->LimitToEnd=$many;
   }
else
   $this->LimitToEnd=999999;  // I hope that no one perform a select that returns 1000000 rows!
$this->LimitEnd=$this->LimitToEnd;
$this->CurrentRow[$res]=$this->LimitStart;
return($res);
}
/**
 * Retrieve the number of rows retrieved by the given result
 * @param integer $result
 * @return integer
 */
function DbGetNumRow($result)
{
$nRows=mssql_num_rows($result);
if($this->UseLimit)
  {
  $newRows=$nRows-$this->LimitStart;
  $nRows=($newRows>$this->LimitEnd)?$this->LimitEnd:$newRows;
  }
// var_dump($nRows);
return($nRows);
}

/**
 * Retrieve the number of fields retrieved by the given result
 * @param integer $result
 * @return integer
 */
function DbGetNumFields($result)
{
return(mssql_num_fields($result));
}
/**
 * Get column information from a result and return as an object
 *
 * @param integer $result
 * @param integer $FieldNo
 * @return object DbMeta
 */
function DbFetchField($result,$FieldNo)
{
$meta=new DbMeta();
$meta->name=mssql_field_name($result,$FieldNo);
$type=mssql_field_type($result,$FieldNo);
$meta->max_length=mssql_field_length($result,$FieldNo);
switch($type)
    {
    case 'LONGBINARY': // Ole object
                    $meta->type='IGNORE';  // Ignore this field
                    $meta->numeric=0;
                    break;
    case 'DATETIME':  // Date format
    case 'LONGCHAR':  // memo fields
    case 'CHAR' :
    case 'VARCHAR' :
                    $meta->type=$type;
                    $meta->numeric=0;
                    break;
    case 'BIT':     // Boolean
                    $meta->type='BOOL';
                    $meta->numeric=0;
                    break;
    case 'BYTE' :
                    $meta->type='BIT';
                    $meta->numeric=0;
                    break;
    case 'CURRENCY' :
                    $meta->type='DOUBLE';
                    $meta->numeric=1;
                    break;
    case 'SMALLINT' :
    case 'INTEGER' :
    case 'DOUBLE' :
                    $meta->type=$type;
                    $meta->numeric=1;
                    break;
    }
$meta->table='';
return($meta);
}
/**
 * Get the flags associated with the specified field in a result
 * @param resource $result
 * @param integer $FieldNo
 * @return string
 */
function DbFieldFlags($result,$FieldNo)
{
$type=mssql_field_type($result,$FieldNo);
if($type=='COUNTER')
  $txt='auto_increment';
else
  $txt='';
return($txt);
}


/**
 * Free the given result
 * @param resource $result
 * @return bool true on success, false on failure
 */

function DbFreeResult($result)
{
if(!($es=mssql_free_result($result)))
   $this->txtError=mssql_get_last_message();
$this->LastRow[$result]=array();
return($es);
}
/**
 * Return the current row as associative array
 * @param resource $result
 * @return array row data or false on failure
 */
function DbGetValue($result)
{
if(!$this->Db_Open)
   {
   $this->txtError='No database open';
   return(false);
   }
// var_dump($this->UseLimit);
// var_dump($this->LimitToEnd);
if($this->UseLimit&&!$this->LimitToEnd)
   return(FALSE);
if($this->UseLimit)
   $this->LimitToEnd--;
// var_dump($this->CurrentRow[$result]);
if(@mssql_data_seek($result,$this->CurrentRow[$result]))
  {
  $this->LastRow[$result]=mssql_fetch_array($result);
  $this->CurrentRow[$result]++;
  }
else
  $this->LastRow[$result]=false;
return($this->LastRow[$result]);
}
/**
 * Close the connection to the database
 * @return bool true
 */
function Db_Close()
{
if(!$this->Db_Open)
   {
   $this->txtError='No database open';
   return(false);
   }
mssql_close($this->DbConn);
$this->Db_Open=false;
return(true);
}


/**
 * From an array of values, make the sql for adding the row
 *
 * @param array array of values. The key is the field name
 * @param array type of each fields
 * @param string name of the key field
 * @access public
 */
function DbaddSql($row,$fieldsType,$KeyField)
{
$Count=0;
$fName='';
$fValue='';
foreach($row as $Key => $Value)
   {
   // I Can't update the row key
   if($Key!=$KeyField)
     {
     if($Count)
       {
       $fName.=', ';
       $fValue.=', ';
       }
     $fName.='['.$Key.']';
     $apice=$fieldsType[$Key]['apice'];
     $fValue.=$apice.$this->DBEscape($Value).$apice;
     $Count++;
     }
   }
$OutText='('.$fName.') VALUES ('.$fValue.') ';
return($OutText);
}

/**
 *  From an array of values, made the sql for modify the row
 *
 * @param array array of values. The key is the field name
 * @param array type of each fields
 * @access public
 */
function DbmodifySql($row,$fieldsType)
{
$OutText=' set ';
$c=0;
foreach($row as $Key => $Value)
   {
   if($c) $OutText.=', ';
   $apice=$fieldsType[$Key]['apice'];
   $OutText.='['.$Key.'] = '.$apice.$this->DBEscape($Value).$apice.' ';
   $c++;
   }
return($OutText);
}
/**
 * Build the condition string for the $FieldName using the pattern matching
 * syntax
 * @access public
 * @param string
 * @param string
 * @return string
 */
function DbRegexCond($FieldName,$pattern)
{
return($FieldName.' like \'%'.$pattern.'%\'');
}

/**
 * Escape the string using the db functions, if provided
 * It don't apply the escape string if magic_quotes_gpc is enabled
 *
 * @param string $value sql statement
 * @access public
 * @return string the escaped string
 */
function DbEscape($value)
{
$quote=(bool)ini_get('magic_quotes_gpc');
if(!$quote)
   $ret=str_replace("'","''",$value);
else
   $ret=str_replace("\\","'",$value);
return($ret);
}
/**
 * Build the condition statement for $FieldName using the 'between' expression
 *
 * @param string $FieldName
 * @param mixed $ValueFrom
 * @param mixed $ValueTo
 * @param string $ValueType
 * @return string
 */
function DbBetweenCond($FieldName,$ValueFrom,$ValueTo,$ValueType)
{
if($ValueType=='DB_NUMBER')
  return($FieldName.' between '.$ValueFrom.' and '.$ValueTo);
else
  return($FieldName.' between \''.$ValueFrom.'\' and \''.$ValueTo.'\'');
}
}
?>
Return current item: Yet Another Pager