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 23486 2010-12-10 04:05:30Z mjh_ca $
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: }
441: