PageRenderTime 191ms CodeModel.GetById 60ms app.highlight 73ms RepoModel.GetById 48ms app.codeStats 0ms

/libraries/joomla/database/database/sqlsrv.php

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