2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
3 /*********************************************************************************
4 * SugarCRM is a customer relationship management program developed by
5 * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
7 * This program is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU Affero General Public License version 3 as published by the
9 * Free Software Foundation with the addition of the following permission added
10 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
11 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
12 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
14 * This program is distributed in the hope that it will be useful, but WITHOUT
15 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
16 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
19 * You should have received a copy of the GNU Affero General Public License along with
20 * this program; if not, see http://www.gnu.org/licenses or write to the Free
21 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
24 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
25 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
27 * The interactive user interfaces in modified source and object code versions
28 * of this program must display Appropriate Legal Notices, as required under
29 * Section 5 of the GNU Affero General Public License version 3.
31 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
32 * these Appropriate Legal Notices must retain the display of the "Powered by
33 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
34 * technical reasons, the Appropriate Legal Notices must display the words
35 * "Powered by SugarCRM".
36 ********************************************************************************/
38 /*********************************************************************************
40 * Description: This file handles the Data base functionality for the application.
41 * It acts as the DB abstraction layer for the application. It depends on helper classes
42 * which generate the necessary SQL. This sql is then passed to PEAR DB classes.
43 * The helper class is chosen in DBManagerFactory, which is driven by 'db_type' in 'dbconfig' under config.php.
45 * All the functions in this class will work with any bean which implements the meta interface.
46 * The passed bean is passed to helper class which uses these functions to generate correct sql.
48 * The meta interface has the following functions:
49 * getTableName() Returns table name of the object.
50 * getFieldDefinitions() Returns a collection of field definitions in order.
51 * getFieldDefintion(name) Return field definition for the field.
52 * getFieldValue(name) Returns the value of the field identified by name.
53 * If the field is not set, the function will return boolean FALSE.
54 * getPrimaryFieldDefinition() Returns the field definition for primary key
56 * The field definition is an array with the following keys:
58 * name This represents name of the field. This is a required field.
59 * type This represents type of the field. This is a required field and valid values are:
73 * length This is used only when the type is varchar and denotes the length of the string.
74 * The max value is 255.
75 * enumvals This is a list of valid values for an enum separated by "|".
76 * It is used only if the type is ?enum?;
77 * required This field dictates whether it is a required value.
78 * The default value is ?FALSE?.
79 * isPrimary This field identifies the primary key of the table.
80 * If none of the fields have this flag set to ?TRUE?,
81 * the first field definition is assume to be the primary key.
82 * Default value for this field is ?FALSE?.
83 * default This field sets the default value for the field definition.
86 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
87 * All Rights Reserved.
88 * Contributor(s): ______________________________________..
89 ********************************************************************************/
91 include_once('include/database/MssqlManager.php');
93 class SqlsrvManager extends MssqlManager
96 * @see DBManager::$backendFunctions
98 protected $backendFunctions = array(
99 'free_result' => 'sqlsrv_free_stmt',
100 'close' => 'sqlsrv_close',
104 * cache of the results sets as they are fetched
106 protected $_resultsCache;
109 * cache of the results sets as they are fetched
111 protected $_lastResultsCacheKey = 0;
114 public function __construct()
116 parent::__construct();
117 $this->_resultsCache = new ArrayObject;
121 * @see DBManager::connect()
123 public function connect(
124 array $configOptions = null,
128 global $sugar_config;
130 if (is_null($configOptions))
131 $configOptions = $sugar_config['dbconfig'];
133 //set the connections parameters
135 $configOptions['db_host_instance'] = trim($configOptions['db_host_instance']);
136 if (empty($configOptions['db_host_instance']))
137 $connect_param = $configOptions['db_host_name'];
139 $connect_param = $configOptions['db_host_name']."\\".$configOptions['db_host_instance'];
142 * Don't try to specifically use a persistent connection
143 * since the driver will handle that for us
145 $this->database = sqlsrv_connect(
148 "UID" => $configOptions['db_user_name'],
149 "PWD" => $configOptions['db_password'],
150 "Database" => $configOptions['db_name'],
151 "CharacterSet" => "UTF-8",
152 "ReturnDatesAsStrings" => true,
153 "MultipleActiveResultSets" => false,
156 if(empty($this->database)) {
157 $GLOBALS['log']->fatal("Could not connect to server ".$configOptions['db_host_name']." as ".$configOptions['db_user_name'].".");
158 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
161 if($this->checkError('Could Not Connect:', $dieOnError))
162 $GLOBALS['log']->info("connected to db");
164 $GLOBALS['log']->info("Connect:".$this->database);
168 * @see DBManager::checkError()
170 public function checkError(
175 if (DBManager::checkError($msg, $dieOnError))
178 $sqlmsg = $this->_getLastErrorMessages();
179 $sqlpos = strpos($sqlmsg, 'Changed database context to');
180 if ( $sqlpos !== false )
181 $sqlmsg = ''; // empty out sqlmsg if its 'Changed database context to'
184 //ERR_MSSQL_DB_CONTEXT: localized version of 'Changed database context to' message
185 if (empty($app_strings)
186 or !isset($app_strings['ERR_MSSQL_DB_CONTEXT'])
187 or !isset($app_strings['ERR_MSSQL_WARNING']) ) {
188 //ignore the message from sql-server if $app_strings array is empty. This will happen
189 //only if connection if made before languge is set.
190 $GLOBALS['log']->debug("Ignoring this database message: " . $sqlmsg);
194 $sqlpos = strpos($sqlmsg, $app_strings['ERR_MSSQL_DB_CONTEXT']);
195 $sqlpos2 = strpos($sqlmsg, $app_strings['ERR_MSSQL_WARNING']);
196 if ( $sqlpos !== false || $sqlpos2 !== false)
201 if ( strlen($sqlmsg) > 2 ) {
202 $GLOBALS['log']->fatal("SQL Server error: " . $sqlmsg);
210 * @see DBManager::query()
212 public function query(
221 // Flag if there are odd number of single quotes
222 if ((substr_count($sql, "'") & 1))
223 $GLOBALS['log']->error("SQL statement[" . $sql . "] has odd number of single quotes.");
225 $sql = $this->_appendN($sql);
227 $this->countQuery($sql);
228 $GLOBALS['log']->info('Query:' . $sql);
229 $this->checkConnection();
230 $this->query_time = microtime(true);
235 $result = @sqlsrv_query($this->database, $sql);
237 // the sqlsrv driver will sometimes return false from sqlsrv_query()
238 // on delete queries, so we'll also check to see if we get an error
240 // see this forum post for more info
241 // http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3685918&SiteID=1
242 if (!$result && ( $this->_getLastErrorMessages() != '' ) ) {
243 // awu Bug 10657: ignoring mssql error message 'Changed database context to' - an intermittent
244 // and difficult to reproduce error. The message is only a warning, and does
245 // not affect the functionality of the query
247 $sqlmsg = $this->_getLastErrorMessages();
248 $sqlpos = strpos($sqlmsg, 'Changed database context to');
249 $sqlpos2 = strpos($sqlmsg, 'Warning:');
251 if ($sqlpos !== false || $sqlpos2 !== false) // if sqlmsg has 'Changed database context to', just log it
252 $GLOBALS['log']->debug($sqlmsg . ": " . $sql );
254 $GLOBALS['log']->fatal($sqlmsg . ": " . $sql );
256 sugar_die('SQL Error : ' . $sqlmsg);
258 echo 'SQL Error : ' . $sqlmsg;
261 $this->lastmysqlrow = -1;
263 $this->query_time = microtime(true) - $this->query_time;
264 $GLOBALS['log']->info('Query Execution Time:'.$this->query_time);
267 $this->checkError($msg.' Query Failed:' . $sql . '::', $dieOnError);
269 // fetch all the returned rows into an the resultsCache
270 if ( is_resource($result) ) {
272 while ( $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC) )
273 $this->_resultsCache[$this->_lastResultsCacheKey][$i++] = $row;
275 sqlsrv_free_stmt($result);
277 return $this->_lastResultsCacheKey++;
284 * @see DBManager::getFieldsArray()
286 public function getFieldsArray(
288 $make_lower_case = false
291 $field_array = array();
293 if ( !is_int($result) || !isset($this->_resultsCache[$result]) )
296 foreach ( $this->_resultsCache[$result][0] as $key => $value ) {
297 if($make_lower_case==true)
298 $key = strtolower($key);
300 $field_array[] = $key;
307 * @see DBManager::fetchByAssoc()
309 public function fetchByAssoc(
315 static $last_returned_result = array();
317 if ( !is_int($result) || !isset($this->_resultsCache[$result]) )
320 if ( !isset($last_returned_result[$result]) )
321 $last_returned_result[$result] = 0;
323 if ( !isset($this->_resultsCache[$result][$last_returned_result[$result]]) ) {
324 $this->_resultsCache[$result] = null;
325 unset($this->_resultsCache[$result]);
329 $row = $this->_resultsCache[$result][$last_returned_result[$result]];
330 if ( $last_returned_result[$result] >= count($this->_resultsCache[$result]) ) {
331 $this->_resultsCache[$result] = null;
332 unset($this->_resultsCache[$result]);
334 $last_returned_result[$result]++;
335 //MSSQL returns a space " " when a varchar column is empty ("") and not null.
336 //We need to iterate through the returned row array and strip empty spaces
338 foreach($row as $key => $column) {
339 //notice we only strip if one space is returned. we do not want to strip
340 //strings with intentional spaces (" foo ")
341 if (!empty($column) && $column ==" ") {
347 if($encode && $this->encode&& is_array($row))
348 return array_map('to_html', $row);
354 * @see DBManager::getRowCount()
356 public function getRowCount(
360 return $this->getOne('SELECT @@ROWCOUNT');
364 * Have this function always return true, since the result is already freed
366 * @see DBManager::freeResult()
368 protected function freeResult(
372 if ( is_int($result) && isset($this->_resultsCache[$result]) )
373 unset($this->_resultsCache[$result]);
379 * Emulates old mssql_get_last_message() behavior, giving us any error messages from the previous
382 * @return string error message(s)
384 private function _getLastErrorMessages()
388 if ( ($errors = sqlsrv_errors()) != null)
389 foreach ( $errors as $error )
390 $message .= $error['message'] . '. ';
396 * @see DBManager::convert()
398 public function convert(
401 array $additional_parameters = array(),
402 array $additional_parameters_oracle_only = array()
405 if ( $type == 'datetime')
406 return "CONVERT(varchar(25)," . $string . ",120)";
408 return parent::convert($string, $type, $additional_parameters, $additional_parameters_oracle_only);
412 * This is a utility function to prepend the "N" character in front of SQL values that are
413 * surrounded by single quotes.
415 * @param $sql string SQL statement
416 * @return string SQL statement with single quote values prepended with "N" character for nvarchar columns
418 private function _appendN(
422 // If there are no single quotes, don't bother, will just assume there is no character data
423 if (strpos($sql, "'") === false)
426 // Flag if there are odd number of single quotes, just continue w/o trying to append N
427 if ((substr_count($sql, "'") & 1)) {
428 $GLOBALS['log']->error("SQL statement[" . $sql . "] has odd number of single quotes.");
432 //The only location of three subsequent ' will be at the begning or end of a value.
433 $sql = preg_replace('/(?<!\')(\'{3})(?!\')/', "'<@#@#@PAIR@#@#@>", $sql);
435 // Remove any remaining '' and do not parse... replace later (hopefully we don't even have any)
437 $regexp = '/(\'{2})/';
438 $pair_matches = array();
439 preg_match_all($regexp, $sql, $pair_matches);
441 foreach (array_unique($pair_matches[0]) as $key=>$value) {
442 $pairs['<@PAIR-'.$key.'@>'] = $value;
444 if (!empty($pairs)) {
445 $sql = str_replace($pairs, array_keys($pairs), $sql);
449 $regexp = "/(N?\'.+?\')/is";
451 preg_match_all($regexp, $sql, $matches);
453 if (!empty($matches)) {
454 foreach ($matches[0] as $key=>$value) {
455 // We are assuming that all nvarchar columns are no more than 200 characters in length
456 // One problem we face is the image column type in reports which cannot accept nvarchar data
457 if (!empty($value) && !is_numeric(trim(str_replace(array("'", ","), "", $value))) && !preg_match('/^\'[\,]\'$/', $value)) {
458 $replace[$value] = 'N' . trim($value, "N");
463 if (!empty($replace))
464 $sql = str_replace(array_keys($replace), $replace, $sql);
467 $sql = str_replace(array_keys($pairs), $pairs, $sql);
469 if(strpos($sql, "<@#@#@PAIR@#@#@>"))
470 $sql = str_replace(array('<@#@#@PAIR@#@#@>'), array("''"), $sql);
476 * Compares two vardefs. Overriding 39098 due to bug: 39098 . IN 6.0 we changed the id columns to dbType = 'id'
477 * for example emails_beans. In 554 the field email_id was nvarchar but in 6.0 since it id dbType = 'id' we would want to alter
478 * it to varchar. This code will prevent it.
480 * @param array $fielddef1
481 * @param array $fielddef2
482 * @return bool true if they match, false if they don't
484 public function compareVarDefs($fielddef1,$fielddef2)
486 if((isset($fielddef2['dbType']) && $fielddef2['dbType'] == 'id') || preg_match('/(_id$|^id$)/', $fielddef2['name'])){
487 if(isset($fielddef1['type']) && isset($fielddef2['type'])){
488 $fielddef2['type'] = $fielddef1['type'];
491 return parent::compareVarDefs($fielddef1, $fielddef2);