| [ Index ] |
PHP Cross Reference of Joomla 2.5.4 DE |
[Summary view] [Print] [Text view]
1 <?php 2 /** 3 * @package Joomla.Platform 4 * @subpackage Database 5 * 6 * @copyright Copyright (C) 2005 - 2012 Open Source Matters, Inc. All rights reserved. 7 * @license GNU General Public License version 2 or later; see LICENSE 8 */ 9 10 defined('JPATH_PLATFORM') or die; 11 12 JLoader::register('JDatabaseQuerySQLSrv', dirname(__FILE__) . '/sqlsrvquery.php'); 13 14 /** 15 * SQL Server database driver 16 * 17 * @package Joomla.Platform 18 * @subpackage Database 19 * @see http://msdn.microsoft.com/en-us/library/cc296152(SQL.90).aspx 20 * @since 11.1 21 */ 22 class JDatabaseSQLSrv extends JDatabase 23 { 24 /** 25 * The name of the database driver. 26 * 27 * @var string 28 * @since 11.1 29 */ 30 public $name = 'sqlsrv'; 31 32 /** 33 * The character(s) used to quote SQL statement names such as table names or field names, 34 * etc. The child classes should define this as necessary. If a single character string the 35 * same character is used for both sides of the quoted name, else the first character will be 36 * used for the opening quote and the second for the closing quote. 37 * 38 * @var string 39 * @since 11.1 40 */ 41 protected $nameQuote = '[]'; 42 43 /** 44 * The null or zero representation of a timestamp for the database driver. This should be 45 * defined in child classes to hold the appropriate value for the engine. 46 * 47 * @var string 48 * @since 11.1 49 */ 50 protected $nullDate = '1900-01-01 00:00:00'; 51 52 /** 53 * Test to see if the SQLSRV connector is available. 54 * 55 * @return boolean True on success, false otherwise. 56 * 57 * @since 11.1 58 */ 59 public static function test() 60 { 61 return (function_exists('sqlsrv_connect')); 62 } 63 64 /** 65 * Constructor. 66 * 67 * @param array $options List of options used to configure the connection 68 * 69 * @since 11.1 70 */ 71 protected function __construct($options) 72 { 73 // Get some basic values from the options. 74 $options['host'] = (isset($options['host'])) ? $options['host'] : 'localhost'; 75 $options['user'] = (isset($options['user'])) ? $options['user'] : ''; 76 $options['password'] = (isset($options['password'])) ? $options['password'] : ''; 77 $options['database'] = (isset($options['database'])) ? $options['database'] : ''; 78 $options['select'] = (isset($options['select'])) ? (bool) $options['select'] : true; 79 80 // Build the connection configuration array. 81 $config = array( 82 'Database' => $options['database'], 83 'uid' => $options['user'], 84 'pwd' => $options['password'], 85 'CharacterSet' => 'UTF-8', 86 'ReturnDatesAsStrings' => true); 87 88 // Make sure the SQLSRV extension for PHP is installed and enabled. 89 if (!function_exists('sqlsrv_connect')) 90 { 91 92 // Legacy error handling switch based on the JError::$legacy switch. 93 // @deprecated 12.1 94 if (JError::$legacy) 95 { 96 $this->errorNum = 1; 97 $this->errorMsg = JText::_('JLIB_DATABASE_ERROR_ADAPTER_SQLSRV'); 98 return; 99 } 100 else 101 { 102 throw new JDatabaseException(JText::_('JLIB_DATABASE_ERROR_ADAPTER_SQLSRV')); 103 } 104 } 105 106 // Attempt to connect to the server. 107 if (!($this->connection = @ sqlsrv_connect($options['host'], $config))) 108 { 109 110 // Legacy error handling switch based on the JError::$legacy switch. 111 // @deprecated 12.1 112 if (JError::$legacy) 113 { 114 $this->errorNum = 2; 115 $this->errorMsg = JText::_('JLIB_DATABASE_ERROR_CONNECT_SQLSRV'); 116 return; 117 } 118 else 119 { 120 throw new JDatabaseException(JText::_('JLIB_DATABASE_ERROR_CONNECT_SQLSRV')); 121 } 122 } 123 124 // Make sure that DB warnings are not returned as errors. 125 sqlsrv_configure('WarningsReturnAsErrors', 0); 126 127 // Finalize initialisation 128 parent::__construct($options); 129 130 // If auto-select is enabled select the given database. 131 if ($options['select'] && !empty($options['database'])) 132 { 133 $this->select($options['database']); 134 } 135 } 136 137 /** 138 * Destructor. 139 * 140 * @since 11.1 141 */ 142 public function __destruct() 143 { 144 if (is_resource($this->connection)) 145 { 146 sqlsrv_close($this->connection); 147 } 148 } 149 150 /** 151 * Get table constraints 152 * 153 * @param string $tableName The name of the database table. 154 * 155 * @return array Any constraints available for the table. 156 * 157 * @since 11.1 158 */ 159 protected function getTableConstraints($tableName) 160 { 161 $query = $this->getQuery(true); 162 163 $this->setQuery( 164 'SELECT CONSTRAINT_NAME FROM' . ' INFORMATION_SCHEMA.TABLE_CONSTRAINTS' . ' WHERE TABLE_NAME = ' . $query->quote($tableName) 165 ); 166 167 return $this->loadColumn(); 168 } 169 170 /** 171 * Rename constraints. 172 * 173 * @param array $constraints Array(strings) of table constraints 174 * @param string $prefix A string 175 * @param string $backup A string 176 * 177 * @return void 178 * 179 * @since 11.1 180 */ 181 protected function renameConstraints($constraints = array(), $prefix = null, $backup = null) 182 { 183 foreach ($constraints as $constraint) 184 { 185 $this->setQuery('sp_rename ' . $constraint . ',' . str_replace($prefix, $backup, $constraint)); 186 $this->query(); 187 } 188 } 189 190 /** 191 * Method to escape a string for usage in an SQL statement. 192 * 193 * The escaping for MSSQL isn't handled in the driver though that would be nice. Because of this we need 194 * to handle the escaping ourselves. 195 * 196 * @param string $text The string to be escaped. 197 * @param boolean $extra Optional parameter to provide extra escaping. 198 * 199 * @return string The escaped string. 200 * 201 * @since 11.1 202 */ 203 public function escape($text, $extra = false) 204 { 205 $result = addslashes($text); 206 $result = str_replace("\'", "''", $result); 207 $result = str_replace('\"', '"', $result); 208 $result = str_replace('\\\/', '/', $result); 209 $result = str_replace('\\\\', '\\', $result); 210 211 212 if ($extra) 213 { 214 // We need the below str_replace since the search in sql server doesn't recognize _ character. 215 $result = str_replace('_', '[_]', $result); 216 } 217 218 return $result; 219 } 220 221 /** 222 * Determines if the connection to the server is active. 223 * 224 * @return boolean True if connected to the database engine. 225 * 226 * @since 11.1 227 */ 228 public function connected() 229 { 230 // TODO: Run a blank query here 231 return true; 232 } 233 234 /** 235 * Drops a table from the database. 236 * 237 * @param string $tableName The name of the database table to drop. 238 * @param boolean $ifExists Optionally specify that the table must exist before it is dropped. 239 * 240 * @return JDatabaseSQLSrv Returns this object to support chaining. 241 * 242 * @since 11.1 243 * @throws JDatabaseException 244 */ 245 public function dropTable($tableName, $ifExists = true) 246 { 247 $query = $this->getQuery(true); 248 249 if ($ifExists) 250 { 251 $this->setQuery( 252 'IF EXISTS(SELECT TABLE_NAME FROM' . ' INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ' . $query->quote($tableName) . ') DROP TABLE ' . $tableName 253 ); 254 } 255 else 256 { 257 $this->setQuery('DROP TABLE ' . $tableName); 258 } 259 260 $this->query(); 261 262 return $this; 263 } 264 265 /** 266 * Get the number of affected rows for the previous executed SQL statement. 267 * 268 * @return integer The number of affected rows. 269 * 270 * @since 11.1 271 */ 272 public function getAffectedRows() 273 { 274 return sqlsrv_rows_affected($this->cursor); 275 } 276 277 /** 278 * Method to get the database collation in use by sampling a text field of a table in the database. 279 * 280 * @return mixed The collation in use by the database or boolean false if not supported. 281 * 282 * @since 11.1 283 */ 284 public function getCollation() 285 { 286 // TODO: Not fake this 287 return 'MSSQL UTF-8 (UCS2)'; 288 } 289 290 /** 291 * Gets an exporter class object. 292 * 293 * @return JDatabaseExporterSQLAzure An exporter object. 294 * 295 * @since 11.1 296 * @throws JDatabaseException 297 */ 298 public function getExporter() 299 { 300 // Make sure we have an exporter class for this driver. 301 if (!class_exists('JDatabaseExporterSQLAzure')) 302 { 303 throw new JDatabaseException(JText::_('JLIB_DATABASE_ERROR_MISSING_EXPORTER')); 304 } 305 306 $o = new JDatabaseExporterSQLAzure; 307 $o->setDbo($this); 308 309 return $o; 310 } 311 312 /** 313 * Gets an importer class object. 314 * 315 * @return JDatabaseImporterSQLAzure An importer object. 316 * 317 * @since 11.1 318 * @throws JDatabaseException 319 */ 320 public function getImporter() 321 { 322 // Make sure we have an importer class for this driver. 323 if (!class_exists('JDatabaseImporterSQLAzure')) 324 { 325 throw new JDatabaseException(JText::_('JLIB_DATABASE_ERROR_MISSING_IMPORTER')); 326 } 327 328 $o = new JDatabaseImporterSQLAzure; 329 $o->setDbo($this); 330 331 return $o; 332 } 333 334 /** 335 * Get the number of returned rows for the previous executed SQL statement. 336 * 337 * @param resource $cursor An optional database cursor resource to extract the row count from. 338 * 339 * @return integer The number of returned rows. 340 * 341 * @since 11.1 342 */ 343 public function getNumRows($cursor = null) 344 { 345 return sqlsrv_num_rows($cursor ? $cursor : $this->cursor); 346 } 347 348 /** 349 * Get the current or query, or new JDatabaseQuery object. 350 * 351 * @param boolean $new False to return the last query set, True to return a new JDatabaseQuery object. 352 * 353 * @return mixed The current value of the internal SQL variable or a new JDatabaseQuery object. 354 * 355 * @since 11.1 356 * @throws JDatabaseException 357 */ 358 public function getQuery($new = false) 359 { 360 if ($new) 361 { 362 // Make sure we have a query class for this driver. 363 if (!class_exists('JDatabaseQuerySQLSrv')) 364 { 365 throw new JDatabaseException(JText::_('JLIB_DATABASE_ERROR_MISSING_QUERY')); 366 } 367 return new JDatabaseQuerySQLSrv($this); 368 } 369 else 370 { 371 return $this->sql; 372 } 373 } 374 375 /** 376 * Retrieves field information about the given tables. 377 * 378 * @param mixed $table A table name 379 * @param boolean $typeOnly True to only return field types. 380 * 381 * @return array An array of fields. 382 * 383 * @since 11.1 384 * @throws JDatabaseException 385 */ 386 public function getTableColumns($table, $typeOnly = true) 387 { 388 // Initialise variables. 389 $result = array(); 390 391 $table_temp = $this->replacePrefix((string) $table); 392 // Set the query to get the table fields statement. 393 $this->setQuery( 394 'SELECT column_name as Field, data_type as Type, is_nullable as \'Null\', column_default as \'Default\'' . 395 ' FROM information_schema.columns' . ' WHERE table_name = ' . $this->quote($table_temp) 396 ); 397 $fields = $this->loadObjectList(); 398 // If we only want the type as the value add just that to the list. 399 if ($typeOnly) 400 { 401 foreach ($fields as $field) 402 { 403 $result[$field->Field] = preg_replace("/[(0-9)]/", '', $field->Type); 404 } 405 } 406 // If we want the whole field data object add that to the list. 407 else 408 { 409 foreach ($fields as $field) 410 { 411 $result[$field->Field] = $field; 412 } 413 } 414 415 return $result; 416 } 417 418 /** 419 * Shows the table CREATE statement that creates the given tables. 420 * 421 * This is unsupported by MSSQL. 422 * 423 * @param mixed $tables A table name or a list of table names. 424 * 425 * @return array A list of the create SQL for the tables. 426 * 427 * @since 11.1 428 * @throws JDatabaseException 429 */ 430 public function getTableCreate($tables) 431 { 432 return ''; 433 } 434 435 /** 436 * Get the details list of keys for a table. 437 * 438 * @param string $table The name of the table. 439 * 440 * @return array An array of the column specification for the table. 441 * 442 * @since 11.1 443 * @throws JDatabaseException 444 */ 445 public function getTableKeys($table) 446 { 447 // TODO To implement. 448 return array(); 449 } 450 451 /** 452 * Method to quote and optionally escape a string to database requirements for insertion into the database. 453 * 454 * @param string $text The string to quote. 455 * @param boolean $escape True to escape the string, false to leave it unchanged. 456 * 457 * @return string The quoted input string. 458 * 459 * @since 11.1 460 */ 461 public function quote($text, $escape = true) 462 { 463 return 'N' . '\'' . ($escape ? $this->escape($text) : $text) . '\''; 464 } 465 466 /** 467 * Method to get an array of all tables in the database. 468 * 469 * @return array An array of all the tables in the database. 470 * 471 * @since 11.1 472 * @throws JDatabaseException 473 */ 474 public function getTableList() 475 { 476 // Set the query to get the tables statement. 477 $this->setQuery('SELECT name FROM ' . $this->getDatabase() . '.sys.Tables WHERE type = \'U\';'); 478 $tables = $this->loadColumn(); 479 480 return $tables; 481 } 482 483 /** 484 * Get the version of the database connector. 485 * 486 * @return string The database connector version. 487 * 488 * @since 11.1 489 */ 490 public function getVersion() 491 { 492 //TODO: Don't hardcode this. 493 return '5.1.0'; 494 } 495 496 /** 497 * Determines if the database engine supports UTF-8 character encoding. 498 * 499 * @return boolean True if supported. 500 * 501 * @since 11.1 502 */ 503 public function hasUTF() 504 { 505 return true; 506 } 507 508 /** 509 * Inserts a row into a table based on an object's properties. 510 * 511 * @param string $table The name of the database table to insert into. 512 * @param object &$object A reference to an object whose public properties match the table fields. 513 * @param string $key The name of the primary key. If provided the object property is updated. 514 * 515 * @return boolean True on success. 516 * 517 * @since 11.1 518 * @throws JDatabaseException 519 */ 520 public function insertObject($table, &$object, $key = null) 521 { 522 $fields = array(); 523 $values = array(); 524 $statement = 'INSERT INTO ' . $this->quoteName($table) . ' (%s) VALUES (%s)'; 525 foreach (get_object_vars($object) as $k => $v) 526 { 527 if (is_array($v) or is_object($v)) 528 { 529 continue; 530 } 531 if (!$this->checkFieldExists($table, $k)) 532 { 533 continue; 534 } 535 if ($k[0] == '_') 536 { 537 // internal field 538 continue; 539 } 540 if ($k == $key && $key == 0) 541 { 542 continue; 543 } 544 $fields[] = $this->quoteName($k); 545 $values[] = $this->Quote($v); 546 } 547 // Set the query and execute the insert. 548 $this->setQuery(sprintf($statement, implode(',', $fields), implode(',', $values))); 549 if (!$this->query()) 550 { 551 return false; 552 } 553 $id = $this->insertid(); 554 if ($key && $id) 555 { 556 $object->$key = $id; 557 } 558 return true; 559 } 560 561 /** 562 * Method to get the auto-incremented value from the last INSERT statement. 563 * 564 * @return integer The value of the auto-increment field from the last inserted row. 565 * 566 * @since 11.1 567 */ 568 public function insertid() 569 { 570 // TODO: SELECT IDENTITY 571 $this->setQuery('SELECT @@IDENTITY'); 572 return (int) $this->loadResult(); 573 } 574 575 /** 576 * Method to get the first field of the first row of the result set from the database query. 577 * 578 * @return mixed The return value or null if the query failed. 579 * 580 * @since 11.1 581 * @throws JDatabaseException 582 */ 583 public function loadResult() 584 { 585 // Initialise variables. 586 $ret = null; 587 588 // Execute the query and get the result set cursor. 589 if (!($cursor = $this->query())) 590 { 591 return null; 592 } 593 594 // Get the first row from the result set as an array. 595 if ($row = sqlsrv_fetch_array($cursor, SQLSRV_FETCH_NUMERIC)) 596 { 597 $ret = $row[0]; 598 } 599 // Free up system resources and return. 600 $this->freeResult($cursor); 601 //For SQLServer - we need to strip slashes 602 $ret = stripslashes($ret); 603 604 return $ret; 605 } 606 607 /** 608 * Execute the SQL statement. 609 * 610 * @return mixed A database cursor resource on success, boolean false on failure. 611 * 612 * @since 11.1 613 * @throws JDatabaseException 614 */ 615 public function query() 616 { 617 if (!is_resource($this->connection)) 618 { 619 620 // Legacy error handling switch based on the JError::$legacy switch. 621 // @deprecated 12.1 622 if (JError::$legacy) 623 { 624 625 if ($this->debug) 626 { 627 JError::raiseError(500, 'JDatabaseDriverSQLAzure::query: ' . $this->errorNum . ' - ' . $this->errorMsg); 628 } 629 return false; 630 } 631 else 632 { 633 JLog::add(JText::sprintf('JLIB_DATABASE_QUERY_FAILED', $this->errorNum, $this->errorMsg), JLog::ERROR, 'database'); 634 throw new JDatabaseException($this->errorMsg, $this->errorNum); 635 } 636 } 637 638 // Take a local copy so that we don't modify the original query and cause issues later 639 $sql = $this->replacePrefix((string) $this->sql); 640 if ($this->limit > 0 || $this->offset > 0) 641 { 642 $sql = $this->limit($sql, $this->limit, $this->offset); 643 } 644 645 // If debugging is enabled then let's log the query. 646 if ($this->debug) 647 { 648 649 // Increment the query counter and add the query to the object queue. 650 $this->count++; 651 $this->log[] = $sql; 652 653 JLog::add($sql, JLog::DEBUG, 'databasequery'); 654 } 655 656 // Reset the error values. 657 $this->errorNum = 0; 658 $this->errorMsg = ''; 659 660 // sqlsrv_num_rows requires a static or keyset cursor. 661 if (strncmp(ltrim(strtoupper($sql)), 'SELECT', strlen('SELECT')) == 0) 662 { 663 $array = array('Scrollable' => SQLSRV_CURSOR_KEYSET); 664 } 665 else 666 { 667 $array = array(); 668 } 669 670 // Execute the query. 671 $this->cursor = sqlsrv_query($this->connection, $sql, array(), $array); 672 673 // If an error occurred handle it. 674 if (!$this->cursor) 675 { 676 677 // Populate the errors. 678 $errors = sqlsrv_errors(); 679 $this->errorNum = $errors[0]['SQLSTATE']; 680 $this->errorMsg = $errors[0]['message'] . 'SQL=' . $sql; 681 682 // Legacy error handling switch based on the JError::$legacy switch. 683 // @deprecated 12.1 684 if (JError::$legacy) 685 { 686 687 if ($this->debug) 688 { 689 JError::raiseError(500, 'JDatabaseDriverSQLAzure::query: ' . $this->errorNum . ' - ' . $this->errorMsg); 690 } 691 return false; 692 } 693 else 694 { 695 JLog::add(JText::sprintf('JLIB_DATABASE_QUERY_FAILED', $this->errorNum, $this->errorMsg), JLog::ERROR, 'databasequery'); 696 throw new JDatabaseException($this->errorMsg, $this->errorNum); 697 } 698 } 699 700 return $this->cursor; 701 } 702 /** 703 * This function replaces a string identifier <var>$prefix</var> with the string held is the 704 * <var>tablePrefix</var> class variable. 705 * 706 * @param string $sql The SQL statement to prepare. 707 * @param string $prefix The common table prefix. 708 * 709 * @return string The processed SQL statement. 710 * 711 * @since 11.1 712 */ 713 public function replacePrefix($sql, $prefix = '#__') 714 { 715 $tablePrefix = 'jos_'; 716 // Initialize variables. 717 $escaped = false; 718 $startPos = 0; 719 $quoteChar = ''; 720 $literal = ''; 721 722 $sql = trim($sql); 723 $n = strlen($sql); 724 725 while ($startPos < $n) 726 { 727 $ip = strpos($sql, $prefix, $startPos); 728 if ($ip === false) 729 { 730 break; 731 } 732 733 $j = strpos($sql, "N'", $startPos); 734 $k = strpos($sql, '"', $startPos); 735 if (($k !== false) && (($k < $j) || ($j === false))) 736 { 737 $quoteChar = '"'; 738 $j = $k; 739 } 740 else 741 { 742 $quoteChar = "'"; 743 } 744 745 if ($j === false) 746 { 747 $j = $n; 748 } 749 750 $literal .= str_replace($prefix, $this->tablePrefix, substr($sql, $startPos, $j - $startPos)); 751 $startPos = $j; 752 753 $j = $startPos + 1; 754 755 if ($j >= $n) 756 { 757 break; 758 } 759 760 // quote comes first, find end of quote 761 while (true) 762 { 763 $k = strpos($sql, $quoteChar, $j); 764 $escaped = false; 765 if ($k === false) 766 { 767 break; 768 } 769 $l = $k - 1; 770 while ($l >= 0 && $sql{$l} == '\\') 771 { 772 $l--; 773 $escaped = !$escaped; 774 } 775 if ($escaped) 776 { 777 $j = $k + 1; 778 continue; 779 } 780 break; 781 } 782 if ($k === false) 783 { 784 // error in the query - no end quote; ignore it 785 break; 786 } 787 $literal .= substr($sql, $startPos, $k - $startPos + 1); 788 $startPos = $k + 1; 789 } 790 if ($startPos < $n) 791 { 792 $literal .= substr($sql, $startPos, $n - $startPos); 793 } 794 795 return $literal; 796 } 797 798 /** 799 * Select a database for use. 800 * 801 * @param string $database The name of the database to select for use. 802 * 803 * @return boolean True if the database was successfully selected. 804 * 805 * @since 11.1 806 * @throws JDatabaseException 807 */ 808 public function select($database) 809 { 810 if (!$database) 811 { 812 return false; 813 } 814 815 if (!sqlsrv_query($this->connection, 'USE ' . $database, null, array('scrollable' => SQLSRV_CURSOR_STATIC))) 816 { 817 818 // Legacy error handling switch based on the JError::$legacy switch. 819 // @deprecated 12.1 820 if (JError::$legacy) 821 { 822 $this->errorNum = 3; 823 $this->errorMsg = JText::_('JLIB_DATABASE_ERROR_DATABASE_CONNECT'); 824 return false; 825 } 826 else 827 { 828 throw new JDatabaseException(JText::_('JLIB_DATABASE_ERROR_DATABASE_CONNECT')); 829 } 830 } 831 832 return true; 833 } 834 835 /** 836 * Set the connection to use UTF-8 character encoding. 837 * 838 * @return boolean True on success. 839 * 840 * @since 11.1 841 */ 842 public function setUTF() 843 { 844 // TODO: Remove this? 845 } 846 847 /** 848 * Method to commit a transaction. 849 * 850 * @return void 851 * 852 * @since 11.1 853 * @throws JDatabaseException 854 */ 855 public function transactionCommit() 856 { 857 $this->setQuery('COMMIT TRANSACTION'); 858 $this->query(); 859 } 860 861 /** 862 * Method to roll back a transaction. 863 * 864 * @return void 865 * 866 * @since 11.1 867 * @throws JDatabaseException 868 */ 869 public function transactionRollback() 870 { 871 $this->setQuery('ROLLBACK TRANSACTION'); 872 $this->query(); 873 } 874 875 /** 876 * Method to initialize a transaction. 877 * 878 * @return void 879 * 880 * @since 11.1 881 * @throws JDatabaseException 882 */ 883 public function transactionStart() 884 { 885 $this->setQuery('START TRANSACTION'); 886 $this->query(); 887 } 888 889 /** 890 * Method to fetch a row from the result set cursor as an array. 891 * 892 * @param mixed $cursor The optional result set cursor from which to fetch the row. 893 * 894 * @return mixed Either the next row from the result set or false if there are no more rows. 895 * 896 * @since 11.1 897 */ 898 protected function fetchArray($cursor = null) 899 { 900 return sqlsrv_fetch_array($cursor ? $cursor : $this->cursor, SQLSRV_FETCH_NUMERIC); 901 } 902 903 /** 904 * Method to fetch a row from the result set cursor as an associative array. 905 * 906 * @param mixed $cursor The optional result set cursor from which to fetch the row. 907 * 908 * @return mixed Either the next row from the result set or false if there are no more rows. 909 * 910 * @since 11.1 911 */ 912 protected function fetchAssoc($cursor = null) 913 { 914 return sqlsrv_fetch_array($cursor ? $cursor : $this->cursor, SQLSRV_FETCH_ASSOC); 915 } 916 917 /** 918 * Method to fetch a row from the result set cursor as an object. 919 * 920 * @param mixed $cursor The optional result set cursor from which to fetch the row. 921 * @param string $class The class name to use for the returned row object. 922 * 923 * @return mixed Either the next row from the result set or false if there are no more rows. 924 * 925 * @since 11.1 926 */ 927 protected function fetchObject($cursor = null, $class = 'stdClass') 928 { 929 return sqlsrv_fetch_object($cursor ? $cursor : $this->cursor, $class); 930 } 931 932 /** 933 * Method to free up the memory used for the result set. 934 * 935 * @param mixed $cursor The optional result set cursor from which to fetch the row. 936 * 937 * @return void 938 * 939 * @since 11.1 940 */ 941 protected function freeResult($cursor = null) 942 { 943 sqlsrv_free_stmt($cursor ? $cursor : $this->cursor); 944 } 945 946 /** 947 * Diagnostic method to return explain information for a query. 948 * 949 * @return string The explain output. 950 * 951 * @deprecated 12.1 952 * @see http://msdn.microsoft.com/en-us/library/aa259203%28SQL.80%29.aspx 953 * @since 11.1 954 */ 955 public function explain() 956 { 957 // Deprecation warning. 958 JLog::add('JDatabase::explain() is deprecated.', JLog::WARNING, 'deprecated'); 959 960 // Backup the current query so we can reset it later. 961 $backup = $this->sql; 962 963 // SET SHOWPLAN_ALL ON - will make sqlsrv to show some explain of query instead of run it 964 $this->setQuery('SET SHOWPLAN_ALL ON'); 965 $this->query(); 966 967 // Execute the query and get the result set cursor. 968 $this->setQuery($backup); 969 if (!($cursor = $this->query())) 970 { 971 return null; 972 } 973 974 // Build the HTML table. 975 $first = true; 976 $buffer = '<table id="explain-sql">'; 977 $buffer .= '<thead><tr><td colspan="99">' . $this->getQuery() . '</td></tr>'; 978 while ($row = $this->fetchAssoc($cursor)) 979 { 980 if ($first) 981 { 982 $buffer .= '<tr>'; 983 foreach ($row as $k => $v) 984 { 985 $buffer .= '<th>' . $k . '</th>'; 986 } 987 $buffer .= '</tr></thead>'; 988 $first = false; 989 } 990 $buffer .= '<tbody><tr>'; 991 foreach ($row as $k => $v) 992 { 993 $buffer .= '<td>' . $v . '</td>'; 994 } 995 $buffer .= '</tr>'; 996 } 997 $buffer .= '</tbody></table>'; 998 999 // Free up system resources and return. 1000 $this->freeResult($cursor); 1001 1002 // Remove the explain status. 1003 $this->setQuery('SET SHOWPLAN_ALL OFF'); 1004 $this->query(); 1005 1006 // Restore the original query to its state before we ran the explain. 1007 $this->sql = $backup; 1008 1009 return $buffer; 1010 } 1011 1012 /** 1013 * Execute a query batch. 1014 * 1015 * @param boolean $abortOnError Abort on error. 1016 * @param boolean $transactionSafe Transaction safe queries. 1017 * 1018 * @return mixed A database resource if successful, false if not. 1019 * 1020 * @since 11.1 1021 * @deprecated 12.1 1022 */ 1023 public function queryBatch($abortOnError = true, $transactionSafe = false) 1024 { 1025 // Deprecation warning. 1026 JLog::add('JDatabase::queryBatch() is deprecated.', JLog::WARNING, 'deprecated'); 1027 1028 $sql = $this->replacePrefix((string) $this->sql); 1029 $this->errorNum = 0; 1030 $this->errorMsg = ''; 1031 1032 // If the batch is meant to be transaction safe then we need to wrap it in a transaction. 1033 if ($transactionSafe) 1034 { 1035 $this->_sql = 'BEGIN TRANSACTION;' . $this->sql . '; COMMIT TRANSACTION;'; 1036 } 1037 1038 $queries = $this->splitSql($sql); 1039 $error = 0; 1040 foreach ($queries as $query) 1041 { 1042 $query = trim($query); 1043 1044 if ($query != '') 1045 { 1046 $this->cursor = sqlsrv_query($this->connection, $query, null, array('scrollable' => SQLSRV_CURSOR_STATIC)); 1047 if ($this->_debug) 1048 { 1049 $this->count++; 1050 $this->log[] = $query; 1051 } 1052 if (!$this->cursor) 1053 { 1054 $error = 1; 1055 $errors = sqlsrv_errors(); 1056 $this->errorNum = $errors[0]['sqlstate']; 1057 $this->errorMsg = $errors[0]['message']; 1058 1059 if ($abortOnError) 1060 { 1061 return $this->cursor; 1062 } 1063 } 1064 } 1065 } 1066 return $error ? false : true; 1067 } 1068 1069 /** 1070 * Method to check and see if a field exists in a table. 1071 * 1072 * @param string $table The table in which to verify the field. 1073 * @param string $field The field to verify. 1074 * 1075 * @return boolean True if the field exists in the table. 1076 * 1077 * @since 11.1 1078 */ 1079 protected function checkFieldExists($table, $field) 1080 { 1081 $table = $this->replacePrefix((string) $table); 1082 $sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS" . " WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'" . 1083 " ORDER BY ORDINAL_POSITION"; 1084 $this->setQuery($sql); 1085 1086 if ($this->loadResult()) 1087 { 1088 return true; 1089 } 1090 else 1091 { 1092 return false; 1093 } 1094 } 1095 1096 /** 1097 * Method to wrap an SQL statement to provide a LIMIT and OFFSET behavior for scrolling through a result set. 1098 * 1099 * @param string $sql The SQL statement to process. 1100 * @param integer $limit The maximum affected rows to set. 1101 * @param integer $offset The affected row offset to set. 1102 * 1103 * @return string The processed SQL statement. 1104 * 1105 * @since 11.1 1106 */ 1107 protected function limit($sql, $limit, $offset) 1108 { 1109 $orderBy = stristr($sql, 'ORDER BY'); 1110 if (is_null($orderBy) || empty($orderBy)) 1111 { 1112 $orderBy = 'ORDER BY (select 0)'; 1113 } 1114 $sql = str_ireplace($orderBy, '', $sql); 1115 1116 $rowNumberText = ',ROW_NUMBER() OVER (' . $orderBy . ') AS RowNumber FROM '; 1117 1118 $sql = preg_replace('/\\s+FROM/', '\\1 ' . $rowNumberText . ' ', $sql, 1); 1119 $sql = 'SELECT TOP ' . $this->limit . ' * FROM (' . $sql . ') _myResults WHERE RowNumber > ' . $this->offset; 1120 1121 return $sql; 1122 } 1123 1124 /** 1125 * Renames a table in the database. 1126 * 1127 * @param string $oldTable The name of the table to be renamed 1128 * @param string $newTable The new name for the table. 1129 * @param string $backup Table prefix 1130 * @param string $prefix For the table - used to rename constraints in non-mysql databases 1131 * 1132 * @return JDatabase Returns this object to support chaining. 1133 * 1134 * @since 11.4 1135 * @throws JDatabaseException 1136 */ 1137 public function renameTable($oldTable, $newTable, $backup = null, $prefix = null) 1138 { 1139 $constraints = array(); 1140 1141 if (!is_null($prefix) && !is_null($backup)) 1142 { 1143 $constraints = $this->getTableConstraints($oldTable); 1144 } 1145 if (!empty($constraints)) 1146 { 1147 $this->renameConstraints($constraints, $prefix, $backup); 1148 } 1149 1150 $this->setQuery("sp_rename '" . $oldTable . "', '" . $newTable . "'"); 1151 1152 return $this->query(); 1153 } 1154 1155 /** 1156 * Locks a table in the database. 1157 * 1158 * @param string $tableName The name of the table to lock. 1159 * 1160 * @return JDatabase Returns this object to support chaining. 1161 * 1162 * @since 11.4 1163 * @throws JDatabaseException 1164 */ 1165 public function lockTable($tableName) 1166 { 1167 return $this; 1168 } 1169 1170 /** 1171 * Unlocks tables in the database. 1172 * 1173 * @return JDatabase Returns this object to support chaining. 1174 * 1175 * @since 11.4 1176 * @throws JDatabaseException 1177 */ 1178 public function unlockTables() 1179 { 1180 return $this; 1181 } 1182 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Tue Apr 3 11:40:28 2012 | Cross-referenced by PHPXref 0.7.1 |