|
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 Adapter |
|
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 21885 2010-04-16 15:13:40Z juokaz $ |
|
21 */ |
|
22 |
|
23 /** |
|
24 * @see Zend_Db_Adapter_Abstract |
|
25 */ |
|
26 require_once 'Zend/Db/Adapter/Abstract.php'; |
|
27 |
|
28 /** |
|
29 * @see Zend_Db_Statement_Sqlsrv |
|
30 */ |
|
31 require_once 'Zend/Db/Statement/Sqlsrv.php'; |
|
32 |
|
33 /** |
|
34 * @category Zend |
|
35 * @package Zend_Db |
|
36 * @subpackage Adapter |
|
37 * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com) |
|
38 * @license http://framework.zend.com/license/new-bsd New BSD License |
|
39 */ |
|
40 class Zend_Db_Adapter_Sqlsrv extends Zend_Db_Adapter_Abstract |
|
41 { |
|
42 /** |
|
43 * User-provided configuration. |
|
44 * |
|
45 * Basic keys are: |
|
46 * |
|
47 * username => (string) Connect to the database as this username. |
|
48 * password => (string) Password associated with the username. |
|
49 * dbname => The name of the local SQL Server instance |
|
50 * |
|
51 * @var array |
|
52 */ |
|
53 protected $_config = array( |
|
54 'dbname' => null, |
|
55 'username' => null, |
|
56 'password' => null, |
|
57 ); |
|
58 |
|
59 /** |
|
60 * Last insert id from INSERT query |
|
61 * |
|
62 * @var int |
|
63 */ |
|
64 protected $_lastInsertId; |
|
65 |
|
66 /** |
|
67 * Query used to fetch last insert id |
|
68 * |
|
69 * @var string |
|
70 */ |
|
71 protected $_lastInsertSQL = 'SELECT SCOPE_IDENTITY() as Current_Identity'; |
|
72 |
|
73 /** |
|
74 * Keys are UPPERCASE SQL datatypes or the constants |
|
75 * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE. |
|
76 * |
|
77 * Values are: |
|
78 * 0 = 32-bit integer |
|
79 * 1 = 64-bit integer |
|
80 * 2 = float or decimal |
|
81 * |
|
82 * @var array Associative array of datatypes to values 0, 1, or 2. |
|
83 */ |
|
84 protected $_numericDataTypes = array( |
|
85 Zend_Db::INT_TYPE => Zend_Db::INT_TYPE, |
|
86 Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE, |
|
87 Zend_Db::FLOAT_TYPE => Zend_Db::FLOAT_TYPE, |
|
88 'INT' => Zend_Db::INT_TYPE, |
|
89 'SMALLINT' => Zend_Db::INT_TYPE, |
|
90 'TINYINT' => Zend_Db::INT_TYPE, |
|
91 'BIGINT' => Zend_Db::BIGINT_TYPE, |
|
92 'DECIMAL' => Zend_Db::FLOAT_TYPE, |
|
93 'FLOAT' => Zend_Db::FLOAT_TYPE, |
|
94 'MONEY' => Zend_Db::FLOAT_TYPE, |
|
95 'NUMERIC' => Zend_Db::FLOAT_TYPE, |
|
96 'REAL' => Zend_Db::FLOAT_TYPE, |
|
97 'SMALLMONEY' => Zend_Db::FLOAT_TYPE, |
|
98 ); |
|
99 |
|
100 /** |
|
101 * Default class name for a DB statement. |
|
102 * |
|
103 * @var string |
|
104 */ |
|
105 protected $_defaultStmtClass = 'Zend_Db_Statement_Sqlsrv'; |
|
106 |
|
107 /** |
|
108 * Creates a connection resource. |
|
109 * |
|
110 * @return void |
|
111 * @throws Zend_Db_Adapter_Sqlsrv_Exception |
|
112 */ |
|
113 protected function _connect() |
|
114 { |
|
115 if (is_resource($this->_connection)) { |
|
116 // connection already exists |
|
117 return; |
|
118 } |
|
119 |
|
120 if (!extension_loaded('sqlsrv')) { |
|
121 /** |
|
122 * @see Zend_Db_Adapter_Sqlsrv_Exception |
|
123 */ |
|
124 require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php'; |
|
125 throw new Zend_Db_Adapter_Sqlsrv_Exception('The Sqlsrv extension is required for this adapter but the extension is not loaded'); |
|
126 } |
|
127 |
|
128 $serverName = $this->_config['host']; |
|
129 if (isset($this->_config['port'])) { |
|
130 $port = (integer) $this->_config['port']; |
|
131 $serverName .= ', ' . $port; |
|
132 } |
|
133 |
|
134 $connectionInfo = array( |
|
135 'Database' => $this->_config['dbname'], |
|
136 ); |
|
137 |
|
138 if (isset($this->_config['username']) && isset($this->_config['password'])) |
|
139 { |
|
140 $connectionInfo += array( |
|
141 'UID' => $this->_config['username'], |
|
142 'PWD' => $this->_config['password'], |
|
143 ); |
|
144 } |
|
145 // else - windows authentication |
|
146 |
|
147 if (!empty($this->_config['driver_options'])) { |
|
148 foreach ($this->_config['driver_options'] as $option => $value) { |
|
149 // A value may be a constant. |
|
150 if (is_string($value)) { |
|
151 $constantName = strtoupper($value); |
|
152 if (defined($constantName)) { |
|
153 $connectionInfo[$option] = constant($constantName); |
|
154 } else { |
|
155 $connectionInfo[$option] = $value; |
|
156 } |
|
157 } |
|
158 } |
|
159 } |
|
160 |
|
161 $this->_connection = sqlsrv_connect($serverName, $connectionInfo); |
|
162 |
|
163 if (!$this->_connection) { |
|
164 /** |
|
165 * @see Zend_Db_Adapter_Sqlsrv_Exception |
|
166 */ |
|
167 require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php'; |
|
168 throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors()); |
|
169 } |
|
170 } |
|
171 |
|
172 /** |
|
173 * Check for config options that are mandatory. |
|
174 * Throw exceptions if any are missing. |
|
175 * |
|
176 * @param array $config |
|
177 * @throws Zend_Db_Adapter_Exception |
|
178 */ |
|
179 protected function _checkRequiredOptions(array $config) |
|
180 { |
|
181 // we need at least a dbname |
|
182 if (! array_key_exists('dbname', $config)) { |
|
183 /** @see Zend_Db_Adapter_Exception */ |
|
184 require_once 'Zend/Db/Adapter/Exception.php'; |
|
185 throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'dbname' that names the database instance"); |
|
186 } |
|
187 |
|
188 if (! array_key_exists('password', $config) && array_key_exists('username', $config)) { |
|
189 /** |
|
190 * @see Zend_Db_Adapter_Exception |
|
191 */ |
|
192 require_once 'Zend/Db/Adapter/Exception.php'; |
|
193 throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'password' for login credentials. |
|
194 If Windows Authentication is desired, both keys 'username' and 'password' should be ommited from config."); |
|
195 } |
|
196 |
|
197 if (array_key_exists('password', $config) && !array_key_exists('username', $config)) { |
|
198 /** |
|
199 * @see Zend_Db_Adapter_Exception |
|
200 */ |
|
201 require_once 'Zend/Db/Adapter/Exception.php'; |
|
202 throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'username' for login credentials. |
|
203 If Windows Authentication is desired, both keys 'username' and 'password' should be ommited from config."); |
|
204 } |
|
205 } |
|
206 |
|
207 /** |
|
208 * Set the transaction isoltion level. |
|
209 * |
|
210 * @param integer|null $level A fetch mode from SQLSRV_TXN_*. |
|
211 * @return true |
|
212 * @throws Zend_Db_Adapter_Sqlsrv_Exception |
|
213 */ |
|
214 public function setTransactionIsolationLevel($level = null) |
|
215 { |
|
216 $this->_connect(); |
|
217 $sql = null; |
|
218 |
|
219 // Default transaction level in sql server |
|
220 if ($level === null) |
|
221 { |
|
222 $level = SQLSRV_TXN_READ_COMMITTED; |
|
223 } |
|
224 |
|
225 switch ($level) { |
|
226 case SQLSRV_TXN_READ_UNCOMMITTED: |
|
227 $sql = "READ UNCOMMITTED"; |
|
228 break; |
|
229 case SQLSRV_TXN_READ_COMMITTED: |
|
230 $sql = "READ COMMITTED"; |
|
231 break; |
|
232 case SQLSRV_TXN_REPEATABLE_READ: |
|
233 $sql = "REPEATABLE READ"; |
|
234 break; |
|
235 case SQLSRV_TXN_SNAPSHOT: |
|
236 $sql = "SNAPSHOT"; |
|
237 break; |
|
238 case SQLSRV_TXN_SERIALIZABLE: |
|
239 $sql = "SERIALIZABLE"; |
|
240 break; |
|
241 default: |
|
242 require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php'; |
|
243 throw new Zend_Db_Adapter_Sqlsrv_Exception("Invalid transaction isolation level mode '$level' specified"); |
|
244 } |
|
245 |
|
246 if (!sqlsrv_query($this->_connection, "SET TRANSACTION ISOLATION LEVEL $sql;")) { |
|
247 require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php'; |
|
248 throw new Zend_Db_Adapter_Sqlsrv_Exception("Transaction cannot be changed to '$level'"); |
|
249 } |
|
250 |
|
251 return true; |
|
252 } |
|
253 |
|
254 /** |
|
255 * Test if a connection is active |
|
256 * |
|
257 * @return boolean |
|
258 */ |
|
259 public function isConnected() |
|
260 { |
|
261 return (is_resource($this->_connection) |
|
262 && (get_resource_type($this->_connection) == 'SQL Server Connection') |
|
263 ); |
|
264 } |
|
265 |
|
266 /** |
|
267 * Force the connection to close. |
|
268 * |
|
269 * @return void |
|
270 */ |
|
271 public function closeConnection() |
|
272 { |
|
273 if ($this->isConnected()) { |
|
274 sqlsrv_close($this->_connection); |
|
275 } |
|
276 $this->_connection = null; |
|
277 } |
|
278 |
|
279 /** |
|
280 * Returns an SQL statement for preparation. |
|
281 * |
|
282 * @param string $sql The SQL statement with placeholders. |
|
283 * @return Zend_Db_Statement_Sqlsrv |
|
284 */ |
|
285 public function prepare($sql) |
|
286 { |
|
287 $this->_connect(); |
|
288 $stmtClass = $this->_defaultStmtClass; |
|
289 |
|
290 if (!class_exists($stmtClass)) { |
|
291 /** |
|
292 * @see Zend_Loader |
|
293 */ |
|
294 require_once 'Zend/Loader.php'; |
|
295 Zend_Loader::loadClass($stmtClass); |
|
296 } |
|
297 |
|
298 $stmt = new $stmtClass($this, $sql); |
|
299 $stmt->setFetchMode($this->_fetchMode); |
|
300 return $stmt; |
|
301 } |
|
302 |
|
303 /** |
|
304 * Quote a raw string. |
|
305 * |
|
306 * @param string $value Raw string |
|
307 * @return string Quoted string |
|
308 */ |
|
309 protected function _quote($value) |
|
310 { |
|
311 if (is_int($value)) { |
|
312 return $value; |
|
313 } elseif (is_float($value)) { |
|
314 return sprintf('%F', $value); |
|
315 } |
|
316 |
|
317 return "'" . str_replace("'", "''", $value) . "'"; |
|
318 } |
|
319 |
|
320 /** |
|
321 * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column. |
|
322 * |
|
323 * As a convention, on RDBMS brands that support sequences |
|
324 * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence |
|
325 * from the arguments and returns the last id generated by that sequence. |
|
326 * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method |
|
327 * returns the last value generated for such a column, and the table name |
|
328 * argument is disregarded. |
|
329 * |
|
330 * @param string $tableName OPTIONAL Name of table. |
|
331 * @param string $primaryKey OPTIONAL Name of primary key column. |
|
332 * @return string |
|
333 */ |
|
334 public function lastInsertId($tableName = null, $primaryKey = null) |
|
335 { |
|
336 if ($tableName) { |
|
337 $tableName = $this->quote($tableName); |
|
338 $sql = 'SELECT IDENT_CURRENT (' . $tableName . ') as Current_Identity'; |
|
339 return (string) $this->fetchOne($sql); |
|
340 } |
|
341 |
|
342 if ($this->_lastInsertId > 0) { |
|
343 return (string) $this->_lastInsertId; |
|
344 } |
|
345 |
|
346 $sql = $this->_lastInsertSQL; |
|
347 return (string) $this->fetchOne($sql); |
|
348 } |
|
349 |
|
350 /** |
|
351 * Inserts a table row with specified data. |
|
352 * |
|
353 * @param mixed $table The table to insert data into. |
|
354 * @param array $bind Column-value pairs. |
|
355 * @return int The number of affected rows. |
|
356 */ |
|
357 public function insert($table, array $bind) |
|
358 { |
|
359 // extract and quote col names from the array keys |
|
360 $cols = array(); |
|
361 $vals = array(); |
|
362 foreach ($bind as $col => $val) { |
|
363 $cols[] = $this->quoteIdentifier($col, true); |
|
364 if ($val instanceof Zend_Db_Expr) { |
|
365 $vals[] = $val->__toString(); |
|
366 unset($bind[$col]); |
|
367 } else { |
|
368 $vals[] = '?'; |
|
369 } |
|
370 } |
|
371 |
|
372 // build the statement |
|
373 $sql = "INSERT INTO " |
|
374 . $this->quoteIdentifier($table, true) |
|
375 . ' (' . implode(', ', $cols) . ') ' |
|
376 . 'VALUES (' . implode(', ', $vals) . ')' |
|
377 . ' ' . $this->_lastInsertSQL; |
|
378 |
|
379 // execute the statement and return the number of affected rows |
|
380 $stmt = $this->query($sql, array_values($bind)); |
|
381 $result = $stmt->rowCount(); |
|
382 |
|
383 $stmt->nextRowset(); |
|
384 |
|
385 $this->_lastInsertId = $stmt->fetchColumn(); |
|
386 |
|
387 return $result; |
|
388 } |
|
389 |
|
390 /** |
|
391 * Returns a list of the tables in the database. |
|
392 * |
|
393 * @return array |
|
394 */ |
|
395 public function listTables() |
|
396 { |
|
397 $this->_connect(); |
|
398 $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name"; |
|
399 return $this->fetchCol($sql); |
|
400 } |
|
401 |
|
402 /** |
|
403 * Returns the column descriptions for a table. |
|
404 * |
|
405 * The return value is an associative array keyed by the column name, |
|
406 * as returned by the RDBMS. |
|
407 * |
|
408 * The value of each array element is an associative array |
|
409 * with the following keys: |
|
410 * |
|
411 * SCHEMA_NAME => string; name of schema |
|
412 * TABLE_NAME => string; |
|
413 * COLUMN_NAME => string; column name |
|
414 * COLUMN_POSITION => number; ordinal position of column in table |
|
415 * DATA_TYPE => string; SQL datatype name of column |
|
416 * DEFAULT => string; default expression of column, null if none |
|
417 * NULLABLE => boolean; true if column can have nulls |
|
418 * LENGTH => number; length of CHAR/VARCHAR |
|
419 * SCALE => number; scale of NUMERIC/DECIMAL |
|
420 * PRECISION => number; precision of NUMERIC/DECIMAL |
|
421 * UNSIGNED => boolean; unsigned property of an integer type |
|
422 * PRIMARY => boolean; true if column is part of the primary key |
|
423 * PRIMARY_POSITION => integer; position of column in primary key |
|
424 * IDENTITY => integer; true if column is auto-generated with unique values |
|
425 * |
|
426 * @todo Discover integer unsigned property. |
|
427 * |
|
428 * @param string $tableName |
|
429 * @param string $schemaName OPTIONAL |
|
430 * @return array |
|
431 */ |
|
432 public function describeTable($tableName, $schemaName = null) |
|
433 { |
|
434 /** |
|
435 * Discover metadata information about this table. |
|
436 */ |
|
437 $sql = "exec sp_columns @table_name = " . $this->quoteIdentifier($tableName, true); |
|
438 $stmt = $this->query($sql); |
|
439 $result = $stmt->fetchAll(Zend_Db::FETCH_NUM); |
|
440 |
|
441 // ZF-7698 |
|
442 $stmt->closeCursor(); |
|
443 |
|
444 if (count($result) == 0) { |
|
445 return array(); |
|
446 } |
|
447 |
|
448 $owner = 1; |
|
449 $table_name = 2; |
|
450 $column_name = 3; |
|
451 $type_name = 5; |
|
452 $precision = 6; |
|
453 $length = 7; |
|
454 $scale = 8; |
|
455 $nullable = 10; |
|
456 $column_def = 12; |
|
457 $column_position = 16; |
|
458 |
|
459 /** |
|
460 * Discover primary key column(s) for this table. |
|
461 */ |
|
462 $tableOwner = $result[0][$owner]; |
|
463 $sql = "exec sp_pkeys @table_owner = " . $tableOwner |
|
464 . ", @table_name = " . $this->quoteIdentifier($tableName, true); |
|
465 $stmt = $this->query($sql); |
|
466 |
|
467 $primaryKeysResult = $stmt->fetchAll(Zend_Db::FETCH_NUM); |
|
468 $primaryKeyColumn = array(); |
|
469 |
|
470 // Per http://msdn.microsoft.com/en-us/library/ms189813.aspx, |
|
471 // results from sp_keys stored procedure are: |
|
472 // 0=TABLE_QUALIFIER 1=TABLE_OWNER 2=TABLE_NAME 3=COLUMN_NAME 4=KEY_SEQ 5=PK_NAME |
|
473 |
|
474 $pkey_column_name = 3; |
|
475 $pkey_key_seq = 4; |
|
476 foreach ($primaryKeysResult as $pkeysRow) { |
|
477 $primaryKeyColumn[$pkeysRow[$pkey_column_name]] = $pkeysRow[$pkey_key_seq]; |
|
478 } |
|
479 |
|
480 $desc = array(); |
|
481 $p = 1; |
|
482 foreach ($result as $key => $row) { |
|
483 $identity = false; |
|
484 $words = explode(' ', $row[$type_name], 2); |
|
485 if (isset($words[0])) { |
|
486 $type = $words[0]; |
|
487 if (isset($words[1])) { |
|
488 $identity = (bool) preg_match('/identity/', $words[1]); |
|
489 } |
|
490 } |
|
491 |
|
492 $isPrimary = array_key_exists($row[$column_name], $primaryKeyColumn); |
|
493 if ($isPrimary) { |
|
494 $primaryPosition = $primaryKeyColumn[$row[$column_name]]; |
|
495 } else { |
|
496 $primaryPosition = null; |
|
497 } |
|
498 |
|
499 $desc[$this->foldCase($row[$column_name])] = array( |
|
500 'SCHEMA_NAME' => null, // @todo |
|
501 'TABLE_NAME' => $this->foldCase($row[$table_name]), |
|
502 'COLUMN_NAME' => $this->foldCase($row[$column_name]), |
|
503 'COLUMN_POSITION' => (int) $row[$column_position], |
|
504 'DATA_TYPE' => $type, |
|
505 'DEFAULT' => $row[$column_def], |
|
506 'NULLABLE' => (bool) $row[$nullable], |
|
507 'LENGTH' => $row[$length], |
|
508 'SCALE' => $row[$scale], |
|
509 'PRECISION' => $row[$precision], |
|
510 'UNSIGNED' => null, // @todo |
|
511 'PRIMARY' => $isPrimary, |
|
512 'PRIMARY_POSITION' => $primaryPosition, |
|
513 'IDENTITY' => $identity, |
|
514 ); |
|
515 } |
|
516 |
|
517 return $desc; |
|
518 } |
|
519 |
|
520 /** |
|
521 * Leave autocommit mode and begin a transaction. |
|
522 * |
|
523 * @return void |
|
524 * @throws Zend_Db_Adapter_Sqlsrv_Exception |
|
525 */ |
|
526 protected function _beginTransaction() |
|
527 { |
|
528 if (!sqlsrv_begin_transaction($this->_connection)) { |
|
529 require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php'; |
|
530 throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors()); |
|
531 } |
|
532 } |
|
533 |
|
534 /** |
|
535 * Commit a transaction and return to autocommit mode. |
|
536 * |
|
537 * @return void |
|
538 * @throws Zend_Db_Adapter_Sqlsrv_Exception |
|
539 */ |
|
540 protected function _commit() |
|
541 { |
|
542 if (!sqlsrv_commit($this->_connection)) { |
|
543 require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php'; |
|
544 throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors()); |
|
545 } |
|
546 } |
|
547 |
|
548 /** |
|
549 * Roll back a transaction and return to autocommit mode. |
|
550 * |
|
551 * @return void |
|
552 * @throws Zend_Db_Adapter_Sqlsrv_Exception |
|
553 */ |
|
554 protected function _rollBack() |
|
555 { |
|
556 if (!sqlsrv_rollback($this->_connection)) { |
|
557 require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php'; |
|
558 throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors()); |
|
559 } |
|
560 } |
|
561 |
|
562 /** |
|
563 * Set the fetch mode. |
|
564 * |
|
565 * @todo Support FETCH_CLASS and FETCH_INTO. |
|
566 * |
|
567 * @param integer $mode A fetch mode. |
|
568 * @return void |
|
569 * @throws Zend_Db_Adapter_Sqlsrv_Exception |
|
570 */ |
|
571 public function setFetchMode($mode) |
|
572 { |
|
573 switch ($mode) { |
|
574 case Zend_Db::FETCH_NUM: // seq array |
|
575 case Zend_Db::FETCH_ASSOC: // assoc array |
|
576 case Zend_Db::FETCH_BOTH: // seq+assoc array |
|
577 case Zend_Db::FETCH_OBJ: // object |
|
578 $this->_fetchMode = $mode; |
|
579 break; |
|
580 case Zend_Db::FETCH_BOUND: // bound to PHP variable |
|
581 require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php'; |
|
582 throw new Zend_Db_Adapter_Sqlsrv_Exception('FETCH_BOUND is not supported yet'); |
|
583 break; |
|
584 default: |
|
585 require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php'; |
|
586 throw new Zend_Db_Adapter_Sqlsrv_Exception("Invalid fetch mode '$mode' specified"); |
|
587 break; |
|
588 } |
|
589 } |
|
590 |
|
591 /** |
|
592 * Adds an adapter-specific LIMIT clause to the SELECT statement. |
|
593 * |
|
594 * @param string $sql |
|
595 * @param integer $count |
|
596 * @param integer $offset OPTIONAL |
|
597 * @return string |
|
598 * @throws Zend_Db_Adapter_Sqlsrv_Exception |
|
599 */ |
|
600 public function limit($sql, $count, $offset = 0) |
|
601 { |
|
602 $count = intval($count); |
|
603 if ($count <= 0) { |
|
604 require_once 'Zend/Db/Adapter/Exception.php'; |
|
605 throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid"); |
|
606 } |
|
607 |
|
608 $offset = intval($offset); |
|
609 if ($offset < 0) { |
|
610 /** @see Zend_Db_Adapter_Exception */ |
|
611 require_once 'Zend/Db/Adapter/Exception.php'; |
|
612 throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid"); |
|
613 } |
|
614 |
|
615 if ($offset == 0) { |
|
616 $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $count . ' ', $sql); |
|
617 } else { |
|
618 $orderby = stristr($sql, 'ORDER BY'); |
|
619 |
|
620 if (!$orderby) { |
|
621 $over = 'ORDER BY (SELECT 0)'; |
|
622 } else { |
|
623 $over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby); |
|
624 } |
|
625 |
|
626 // Remove ORDER BY clause from $sql |
|
627 $sql = preg_replace('/\s+ORDER BY(.*)/', '', $sql); |
|
628 |
|
629 // Add ORDER BY clause as an argument for ROW_NUMBER() |
|
630 $sql = "SELECT ROW_NUMBER() OVER ($over) AS \"ZEND_DB_ROWNUM\", * FROM ($sql) AS inner_tbl"; |
|
631 |
|
632 $start = $offset + 1; |
|
633 $end = $offset + $count; |
|
634 |
|
635 $sql = "WITH outer_tbl AS ($sql) SELECT * FROM outer_tbl WHERE \"ZEND_DB_ROWNUM\" BETWEEN $start AND $end"; |
|
636 } |
|
637 |
|
638 return $sql; |
|
639 } |
|
640 |
|
641 /** |
|
642 * Check if the adapter supports real SQL parameters. |
|
643 * |
|
644 * @param string $type 'positional' or 'named' |
|
645 * @return bool |
|
646 */ |
|
647 public function supportsParameters($type) |
|
648 { |
|
649 if ($type == 'positional') { |
|
650 return true; |
|
651 } |
|
652 |
|
653 // if its 'named' or anything else |
|
654 return false; |
|
655 } |
|
656 |
|
657 /** |
|
658 * Retrieve server version in PHP style |
|
659 * |
|
660 * @return string |
|
661 */ |
|
662 public function getServerVersion() |
|
663 { |
|
664 $this->_connect(); |
|
665 $serverInfo = sqlsrv_server_info($this->_connection); |
|
666 |
|
667 if ($serverInfo !== false) { |
|
668 return $serverInfo['SQLServerVersion']; |
|
669 } |
|
670 |
|
671 return null; |
|
672 } |
|
673 } |