library/Zend/Db/Statement/Sqlsrv.php

   1 <?php
   2 /**
   3  * Zend Framework
   4  *
   5  * LICENSE
   6  *
   7  * This source file is subject to the new BSD license that is bundled
   8  * with this package in the file LICENSE.txt.
   9  * It is also available through the world-wide-web at this URL:
  10  * http://framework.zend.com/license/new-bsd
  11  * If you did not receive a copy of the license and are unable to
  12  * obtain it through the world-wide-web, please send an email
  13  * to license@zend.com so we can send you a copy immediately.
  14  *
  15  * @category   Zend
  16  * @package    Zend_Db
  17  * @subpackage Statement
  18  * @copyright  Copyright (c) 2005-2009 Zend Technologies USA Inc. (http://www.zend.com)
  19  * @license    http://framework.zend.com/license/new-bsd     New BSD License
  20  */
  21
  22 /**
  23  * @see Zend_Db_Statement
  24  */
  25 require_once 'Zend/Db/Statement.php';
  26
  27 /**
  28  * Extends for Microsoft SQL Server Driver for PHP
  29  *
  30  * @category   Zend
  31  * @package    Zend_Db
  32  * @subpackage Statement
  33  * @copyright  Copyright (c) 2005-2009 Zend Technologies USA Inc. (http://www.zend.com)
  34  * @license    http://framework.zend.com/license/new-bsd     New BSD License
  35  */
  36 class Zend_Db_Statement_Sqlsrv extends Zend_Db_Statement
  37 {
  38     /**
  39      * The connection_stmt object.
  40      */
  41     protected $_stmt;
  42
  43     /**
  44      * The connection_stmt object original string.
  45      */
  46     protected $_originalSQL;
  47
  48     /**
  49      * Column names.
  50      */
  51     protected $_keys;
  52
  53     /**
  54      * Query executed
  55      */
  56     protected $_executed = false;
  57
  58     /**
  59      * Prepares statement handle
  60      *
  61      * @param string $sql
  62      * @return void
  63      * @throws Zend_Db_Statement_Sqlsrv_Exception
  64      */
  65     protected function _prepare($sql)
  66     {
  67         $connection = $this->_adapter->getConnection();
  68
  69         $this->_stmt = sqlsrv_prepare($connection, $sql);
  70
  71         if (!$this->_stmt) {
  72             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';
  73             throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());
  74         }
  75
  76         $this->_originalSQL = $sql;
  77     }
  78
  79     /**
  80      * Binds a parameter to the specified variable name.
  81      *
  82      * @param mixed $parameter Name the parameter, either integer or string.
  83      * @param mixed $variable  Reference to PHP variable containing the value.
  84      * @param mixed $type      OPTIONAL Datatype of SQL parameter.
  85      * @param mixed $length    OPTIONAL Length of SQL parameter.
  86      * @param mixed $options   OPTIONAL Other options.
  87      * @return bool
  88      * @throws Zend_Db_Statement_Exception
  89      */
  90     protected function _bindParam($parameter, &$variable, $type = null, $length = null, $options = null)
  91     {
  92         //Sql server doesn't support bind by name
  93         return true;
  94     }
  95
  96     /**
  97      * Closes the cursor, allowing the statement to be executed again.
  98      *
  99      * @return bool
 100      */
 101     public function closeCursor()
 102     {
 103         if (!$this->_stmt) {
 104             return false;
 105         }
 106
 107         sqlsrv_free_stmt($this->_stmt);
 108         $this->_stmt = false;
 109         return true;
 110     }
 111
 112     /**
 113      * Returns the number of columns in the result set.
 114      * Returns null if the statement has no result set metadata.
 115      *
 116      * @return int The number of columns.
 117      */
 118     public function columnCount()
 119     {
 120         if ($this->_stmt && $this->_executed) {
 121             return sqlsrv_num_fields($this->_stmt);
 122         }
 123
 124         return 0;
 125     }
 126
 127
 128     /**
 129      * Retrieves the error code, if any, associated with the last operation on
 130      * the statement handle.
 131      *
 132      * @return string error code.
 133      */
 134     public function errorCode()
 135     {
 136         if (!$this->_stmt) {
 137             return false;
 138         }
 139
 140         $error = sqlsrv_errors();
 141         if (!$error) {
 142             return false;
 143         }
 144
 145         return $error[0]['code'];
 146     }
 147
 148
 149     /**
 150      * Retrieves an array of error information, if any, associated with the
 151      * last operation on the statement handle.
 152      *
 153      * @return array
 154      */
 155     public function errorInfo()
 156     {
 157         if (!$this->_stmt) {
 158             return false;
 159         }
 160
 161         $error = sqlsrv_errors();
 162         if (!$error) {
 163             return false;
 164         }
 165
 166         return array(
 167             $error[0]['code'],
 168             $error[0]['message'],
 169         );
 170     }
 171
 172
 173     /**
 174      * Executes a prepared statement.
 175      *
 176      * @param array $params OPTIONAL Values to bind to parameter placeholders.
 177      * @return bool
 178      * @throws Zend_Db_Statement_Exception
 179      */
 180     public function _execute(array $params = null)
 181     {
 182         $connection = $this->_adapter->getConnection();
 183         if (!$this->_stmt) {
 184             return false;
 185         }
 186
 187         if ($params !== null) {
 188             if (!is_array($params)) {
 189                 $params = array($params);
 190             }
 191             $error = false;
 192
 193             // make all params passed by reference
 194             $params_ = array();
 195             $temp    = array();
 196             $i       = 1;
 197             foreach ($params as $param) {
 198                 $temp[$i]  = $param;
 199                 $params_[] = &$temp[$i];
 200                 $i++;
 201             }
 202             $params = $params_;
 203         }
 204
 205         $this->_stmt = sqlsrv_query($connection, $this->_originalSQL, $params);
 206
 207         if (!$this->_stmt) {
 208             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';
 209             throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());
 210         }
 211
 212         $this->_executed = true;
 213
 214         return (!$this->_stmt);
 215     }
 216
 217     /**
 218      * Fetches a row from the result set.
 219      *
 220      * @param  int $style  OPTIONAL Fetch mode for this fetch operation.
 221      * @param  int $cursor OPTIONAL Absolute, relative, or other.
 222      * @param  int $offset OPTIONAL Number for absolute or relative cursors.
 223      * @return mixed Array, object, or scalar depending on fetch mode.
 224      * @throws Zend_Db_Statement_Exception
 225      */
 226     public function fetch($style = null, $cursor = null, $offset = null)
 227     {
 228         if (!$this->_stmt) {
 229             return false;
 230         }
 231
 232         if (null === $style) {
 233             $style = $this->_fetchMode;
 234         }
 235
 236         $values = sqlsrv_fetch_array($this->_stmt, SQLSRV_FETCH_ASSOC);
 237
 238         if (!$values && (null !== $error = sqlsrv_errors())) {
 239             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';
 240             throw new Zend_Db_Statement_Sqlsrv_Exception($error);
 241         }
 242
 243         if (null === $values) {
 244             return null;
 245         }
 246
 247         if (!$this->_keys) {
 248             foreach ($values as $key => $value) {
 249                 $this->_keys[] = $this->_adapter->foldCase($key);
 250             }
 251         }
 252
 253         $values = array_values($values);
 254
 255         $row = false;
 256         switch ($style) {
 257             case Zend_Db::FETCH_NUM:
 258                 $row = $values;
 259                 break;
 260             case Zend_Db::FETCH_ASSOC:
 261                 $row = array_combine($this->_keys, $values);
 262                 break;
 263             case Zend_Db::FETCH_BOTH:
 264                 $assoc = array_combine($this->_keys, $values);
 265                 $row   = array_merge($values, $assoc);
 266                 break;
 267             case Zend_Db::FETCH_OBJ:
 268                 $row = (object) array_combine($this->_keys, $values);
 269                 break;
 270             case Zend_Db::FETCH_BOUND:
 271                 $assoc = array_combine($this->_keys, $values);
 272                 $row   = array_merge($values, $assoc);
 273                 $row   = $this->_fetchBound($row);
 274                 break;
 275             default:
 276                 require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';
 277                 throw new Zend_Db_Statement_Sqlsrv_Exception("Invalid fetch mode '$style' specified");
 278                 break;
 279         }
 280
 281         return $row;
 282     }
 283
 284     /**
 285      * Returns a single column from the next row of a result set.
 286      *
 287      * @param int $col OPTIONAL Position of the column to fetch.
 288      * @return string
 289      * @throws Zend_Db_Statement_Exception
 290      */
 291     public function fetchColumn($col = 0)
 292     {
 293         if (!$this->_stmt) {
 294             return false;
 295         }
 296
 297         if (!sqlsrv_fetch($this->_stmt)) {
 298             if (null !== $error = sqlsrv_errors()) {
 299                 require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';
 300                 throw new Zend_Db_Statement_Sqlsrv_Exception($error);
 301             }
 302
 303             // If no error, there is simply no record
 304             return false;
 305         }
 306
 307         $data = sqlsrv_get_field($this->_stmt, $col); //0-based
 308         if ($data === false) {
 309             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';
 310             throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());
 311         }
 312
 313         return $data;
 314     }
 315
 316     /**
 317      * Fetches the next row and returns it as an object.
 318      *
 319      * @param string $class  OPTIONAL Name of the class to create.
 320      * @param array  $config OPTIONAL Constructor arguments for the class.
 321      * @return mixed One object instance of the specified class.
 322      * @throws Zend_Db_Statement_Exception
 323      */
 324     public function fetchObject($class = 'stdClass', array $config = array())
 325     {
 326         if (!$this->_stmt) {
 327             return false;
 328         }
 329
 330         $obj = sqlsrv_fetch_object($this->_stmt);
 331
 332         if ($error = sqlsrv_errors()) {
 333             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';
 334             throw new Zend_Db_Statement_Sqlsrv_Exception($error);
 335         }
 336
 337         /* @todo XXX handle parameters */
 338
 339         if (null === $obj) {
 340             return false;
 341         }
 342
 343         return $obj;
 344     }
 345
 346     /**
 347      * Returns metadata for a column in a result set.
 348      *
 349      * @param int $column
 350      * @return mixed
 351      * @throws Zend_Db_Statement_Sqlsrv_Exception
 352      */
 353     public function getColumnMeta($column)
 354     {
 355         $fields = sqlsrv_field_metadata($this->_stmt);
 356
 357         if (!$fields) {
 358             throw new Zend_Db_Statement_Sqlsrv_Exception('Column metadata can not be fetched');
 359         }
 360
 361         if (!isset($fields[$column])) {
 362             throw new Zend_Db_Statement_Sqlsrv_Exception('Column index does not exist in statement');
 363         }
 364
 365         return $fields[$column];
 366     }
 367
 368     /**
 369      * Retrieves the next rowset (result set) for a SQL statement that has
 370      * multiple result sets.  An example is a stored procedure that returns
 371      * the results of multiple queries.
 372      *
 373      * @return bool
 374      * @throws Zend_Db_Statement_Exception
 375      */
 376     public function nextRowset()
 377     {
 378         if (sqlsrv_next_result($this->_stmt) === false) {
 379             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';
 380             throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());
 381         }
 382
 383         //else - moved to next (or there are no more rows)
 384     }
 385
 386     /**
 387      * Returns the number of rows affected by the execution of the
 388      * last INSERT, DELETE, or UPDATE statement executed by this
 389      * statement object.
 390      *
 391      * @return int     The number of rows affected.
 392      * @throws Zend_Db_Statement_Exception
 393      */
 394     public function rowCount()
 395     {
 396         if (!$this->_stmt) {
 397             return false;
 398         }
 399
 400         if (!$this->_executed) {
 401             return 0;
 402         }
 403
 404         $num_rows = sqlsrv_rows_affected($this->_stmt);
 405
 406         // Strict check is necessary; 0 is a valid return value
 407         if ($num_rows === false) {
 408             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';
 409             throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());
 410         }
 411
 412         return $num_rows;
 413     }
 414 }