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