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-2010 Zend Technologies USA Inc. (http://www.zend.com)

    19  * @license    http://framework.zend.com/license/new-bsd     New BSD License

    20  * @version    $Id: Sqlsrv.php 21887 2010-04-16 18:28:10Z juokaz $

    21  */

    22 

    23 /**

    24  * @see Zend_Db_Statement

    25  */

    26 require_once 'Zend/Db/Statement.php';

    27 

    28 /**

    29  * Extends for Microsoft SQL Server Driver for PHP

    30  *

    31  * @category   Zend

    32  * @package    Zend_Db

    33  * @subpackage Statement

    34  * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)

    35  * @license    http://framework.zend.com/license/new-bsd     New BSD License

    36  */

    37 class Zend_Db_Statement_Sqlsrv extends Zend_Db_Statement

    38 {

    39 

    40     /**

    41      * The connection_stmt object original string.

    42      */

    43     protected $_originalSQL;

    44 

    45     /**

    46      * Column names.

    47      */

    48     protected $_keys;

    49 

    50     /**

    51      * Query executed

    52      */

    53     protected $_executed = false;

    54 

    55     /**

    56      * Prepares statement handle

    57      *

    58      * @param string $sql

    59      * @return void

    60      * @throws Zend_Db_Statement_Sqlsrv_Exception

    61      */

    62     protected function _prepare($sql)

    63     {

    64         $connection = $this->_adapter->getConnection();

    65 

    66         $this->_stmt = sqlsrv_prepare($connection, $sql);

    67 

    68         if (!$this->_stmt) {

    69             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';

    70             throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());

    71         }

    72 

    73         $this->_originalSQL = $sql;

    74     }

    75 

    76     /**

    77      * Binds a parameter to the specified variable name.

    78      *

    79      * @param mixed $parameter Name the parameter, either integer or string.

    80      * @param mixed $variable  Reference to PHP variable containing the value.

    81      * @param mixed $type      OPTIONAL Datatype of SQL parameter.

    82      * @param mixed $length    OPTIONAL Length of SQL parameter.

    83      * @param mixed $options   OPTIONAL Other options.

    84      * @return bool

    85      * @throws Zend_Db_Statement_Exception

    86      */

    87     protected function _bindParam($parameter, &$variable, $type = null, $length = null, $options = null)

    88     {

    89         //Sql server doesn't support bind by name

    90         return true;

    91     }

    92 

    93     /**

    94      * Closes the cursor, allowing the statement to be executed again.

    95      *

    96      * @return bool

    97      */

    98     public function closeCursor()

    99     {

   100         if (!$this->_stmt) {

   101             return false;

   102         }

   103 

   104         sqlsrv_free_stmt($this->_stmt);

   105         $this->_stmt = false;

   106         return true;

   107     }

   108 

   109     /**

   110      * Returns the number of columns in the result set.

   111      * Returns null if the statement has no result set metadata.

   112      *

   113      * @return int The number of columns.

   114      */

   115     public function columnCount()

   116     {

   117         if ($this->_stmt && $this->_executed) {

   118             return sqlsrv_num_fields($this->_stmt);

   119         }

   120 

   121         return 0;

   122     }

   123 

   124 

   125     /**

   126      * Retrieves the error code, if any, associated with the last operation on

   127      * the statement handle.

   128      *

   129      * @return string error code.

   130      */

   131     public function errorCode()

   132     {

   133         if (!$this->_stmt) {

   134             return false;

   135         }

   136 

   137         $error = sqlsrv_errors();

   138         if (!$error) {

   139             return false;

   140         }

   141 

   142         return $error[0]['code'];

   143     }

   144 

   145 

   146     /**

   147      * Retrieves an array of error information, if any, associated with the

   148      * last operation on the statement handle.

   149      *

   150      * @return array

   151      */

   152     public function errorInfo()

   153     {

   154         if (!$this->_stmt) {

   155             return false;

   156         }

   157 

   158         $error = sqlsrv_errors();

   159         if (!$error) {

   160             return false;

   161         }

   162 

   163         return array(

   164             $error[0]['code'],

   165             $error[0]['message'],

   166         );

   167     }

   168 

   169 

   170     /**

   171      * Executes a prepared statement.

   172      *

   173      * @param array $params OPTIONAL Values to bind to parameter placeholders.

   174      * @return bool

   175      * @throws Zend_Db_Statement_Exception

   176      */

   177     public function _execute(array $params = null)

   178     {

   179         $connection = $this->_adapter->getConnection();

   180         if (!$this->_stmt) {

   181             return false;

   182         }

   183 

   184         if ($params !== null) {

   185             if (!is_array($params)) {

   186                 $params = array($params);

   187             }

   188             $error = false;

   189 

   190             // make all params passed by reference

   191             $params_ = array();

   192             $temp    = array();

   193             $i       = 1;

   194             foreach ($params as $param) {

   195                 $temp[$i]  = $param;

   196                 $params_[] = &$temp[$i];

   197                 $i++;

   198             }

   199             $params = $params_;

   200         }

   201 

   202         $this->_stmt = sqlsrv_query($connection, $this->_originalSQL, $params);

   203 

   204         if (!$this->_stmt) {

   205             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';

   206             throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());

   207         }

   208 

   209         $this->_executed = true;

   210 

   211         return (!$this->_stmt);

   212     }

   213 

   214     /**

   215      * Fetches a row from the result set.

   216      *

   217      * @param  int $style  OPTIONAL Fetch mode for this fetch operation.

   218      * @param  int $cursor OPTIONAL Absolute, relative, or other.

   219      * @param  int $offset OPTIONAL Number for absolute or relative cursors.

   220      * @return mixed Array, object, or scalar depending on fetch mode.

   221      * @throws Zend_Db_Statement_Exception

   222      */

   223     public function fetch($style = null, $cursor = null, $offset = null)

   224     {

   225         if (!$this->_stmt) {

   226             return false;

   227         }

   228 

   229         if (null === $style) {

   230             $style = $this->_fetchMode;

   231         }

   232 

   233         $values = sqlsrv_fetch_array($this->_stmt, SQLSRV_FETCH_ASSOC);

   234 

   235         if (!$values && (null !== $error = sqlsrv_errors())) {

   236             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';

   237             throw new Zend_Db_Statement_Sqlsrv_Exception($error);

   238         }

   239 

   240         if (null === $values) {

   241             return null;

   242         }

   243 

   244         if (!$this->_keys) {

   245             foreach ($values as $key => $value) {

   246                 $this->_keys[] = $this->_adapter->foldCase($key);

   247             }

   248         }

   249 

   250         $values = array_values($values);

   251 

   252         $row = false;

   253         switch ($style) {

   254             case Zend_Db::FETCH_NUM:

   255                 $row = $values;

   256                 break;

   257             case Zend_Db::FETCH_ASSOC:

   258                 $row = array_combine($this->_keys, $values);

   259                 break;

   260             case Zend_Db::FETCH_BOTH:

   261                 $assoc = array_combine($this->_keys, $values);

   262                 $row   = array_merge($values, $assoc);

   263                 break;

   264             case Zend_Db::FETCH_OBJ:

   265                 $row = (object) array_combine($this->_keys, $values);

   266                 break;

   267             case Zend_Db::FETCH_BOUND:

   268                 $assoc = array_combine($this->_keys, $values);

   269                 $row   = array_merge($values, $assoc);

   270                 $row   = $this->_fetchBound($row);

   271                 break;

   272             default:

   273                 require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';

   274                 throw new Zend_Db_Statement_Sqlsrv_Exception("Invalid fetch mode '$style' specified");

   275                 break;

   276         }

   277 

   278         return $row;

   279     }

   280 

   281     /**

   282      * Returns a single column from the next row of a result set.

   283      *

   284      * @param int $col OPTIONAL Position of the column to fetch.

   285      * @return string

   286      * @throws Zend_Db_Statement_Exception

   287      */

   288     public function fetchColumn($col = 0)

   289     {

   290         if (!$this->_stmt) {

   291             return false;

   292         }

   293 

   294         if (!sqlsrv_fetch($this->_stmt)) {

   295             if (null !== $error = sqlsrv_errors()) {

   296                 require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';

   297                 throw new Zend_Db_Statement_Sqlsrv_Exception($error);

   298             }

   299 

   300             // If no error, there is simply no record

   301             return false;

   302         }

   303 

   304         $data = sqlsrv_get_field($this->_stmt, $col); //0-based

   305         if ($data === false) {

   306             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';

   307             throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());

   308         }

   309 

   310         return $data;

   311     }

   312 

   313     /**

   314      * Fetches the next row and returns it as an object.

   315      *

   316      * @param string $class  OPTIONAL Name of the class to create.

   317      * @param array  $config OPTIONAL Constructor arguments for the class.

   318      * @return mixed One object instance of the specified class.

   319      * @throws Zend_Db_Statement_Exception

   320      */

   321     public function fetchObject($class = 'stdClass', array $config = array())

   322     {

   323         if (!$this->_stmt) {

   324             return false;

   325         }

   326 

   327         $obj = sqlsrv_fetch_object($this->_stmt);

   328 

   329         if ($error = sqlsrv_errors()) {

   330             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';

   331             throw new Zend_Db_Statement_Sqlsrv_Exception($error);

   332         }

   333 

   334         /* @todo XXX handle parameters */

   335 

   336         if (null === $obj) {

   337             return false;

   338         }

   339 

   340         return $obj;

   341     }

   342 

   343     /**

   344      * Returns metadata for a column in a result set.

   345      *

   346      * @param int $column

   347      * @return mixed

   348      * @throws Zend_Db_Statement_Sqlsrv_Exception

   349      */

   350     public function getColumnMeta($column)

   351     {

   352         $fields = sqlsrv_field_metadata($this->_stmt);

   353 

   354         if (!$fields) {

   355             throw new Zend_Db_Statement_Sqlsrv_Exception('Column metadata can not be fetched');

   356         }

   357 

   358         if (!isset($fields[$column])) {

   359             throw new Zend_Db_Statement_Sqlsrv_Exception('Column index does not exist in statement');

   360         }

   361 

   362         return $fields[$column];

   363     }

   364 

   365     /**

   366      * Retrieves the next rowset (result set) for a SQL statement that has

   367      * multiple result sets.  An example is a stored procedure that returns

   368      * the results of multiple queries.

   369      *

   370      * @return bool

   371      * @throws Zend_Db_Statement_Exception

   372      */

   373     public function nextRowset()

   374     {

   375         if (sqlsrv_next_result($this->_stmt) === false) {

   376             require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';

   377             throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());

   378         }

   379 		

   380 		// reset column keys

   381 		$this->_keys = null;

   382 

   383 		return true;

   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 	

   415 	/**

   416      * Returns an array containing all of the result set rows.

   417      *

   418      * @param int $style OPTIONAL Fetch mode.

   419      * @param int $col   OPTIONAL Column number, if fetch mode is by column.

   420      * @return array Collection of rows, each in a format by the fetch mode.

   421      *

   422      * Behaves like parent, but if limit()

   423      * is used, the final result removes the extra column

   424      * 'zend_db_rownum'

   425      */

   426     public function fetchAll($style = null, $col = null)

   427     {

   428         $data = parent::fetchAll($style, $col);

   429         $results = array();

   430         $remove = $this->_adapter->foldCase('ZEND_DB_ROWNUM');

   431 

   432         foreach ($data as $row) {

   433             if (is_array($row) && array_key_exists($remove, $row)) {

   434                 unset($row[$remove]);

   435             }

   436             $results[] = $row;

   437         }

   438         return $results;

   439     }

   440 }