1<?php 2 /** 3 * Database class for MS SQL 4 * @author Cameron Taggart <cameront@wolfenet.com 5 * @author Dan Kuykendall, Dave Hall and others 6 * @copyright Copyright (C) 1998 Cameron Taggart 7 * @copyright Portions Copyright (C) 2001-2004 Free Software Foundation, Inc. http://www.fsf.org/ 8 * @license http://www.fsf.org/licenses/lgpl.html GNU Lesser General Public License 9 * @link http://www.sanisoft.com/phplib/manual/DB_sql.php 10 * @package phpgwapi 11 * @subpackage database 12 * @version $Id: class.db_mssql.inc.php 16521 2006-03-15 13:40:33Z sigurdne $ 13 */ 14 15 /** 16 * Database class for MS SQL 17 * 18 * @package phpgwapi 19 * @subpackage database 20 * @ignore 21 */ 22 class db 23 { 24 var $VEOF = -1; 25 var $Transaction = false; 26 27 function connect($Database = '', $Host = '', $User = '', $Password = '') 28 { 29 /* Handle defaults */ 30 if ($Database == '') 31 { 32 $Database = $this->Database; 33 } 34 if ($Host == '') 35 { 36 $Host = $this->Host; 37 } 38 if ($User == '') 39 { 40 $User = $this->User; 41 } 42 if ($Password == '') 43 { 44 $Password = $this->Password; 45 } 46 47 if (! $this->Link_ID ) 48 { 49 if ($GLOBALS['phpgw_info']['server']['db_persistent']) 50 { 51 $this->Link_ID=mssql_pconnect($Host, $User, $Password); 52 } 53 else 54 { 55 $this->Link_ID=mssql_connect($Host, $User, $Password); 56 } 57 if (!$this->Link_ID) 58 { 59 $this->halt('Link-ID == false, mssql_'.($GLOBALS['phpgw_info']['server']['db_persistent']?'p':'').'connect failed'); 60 } 61 else 62 { 63 mssql_select_db($Database, $this->Link_ID); 64 } 65 } 66 } 67 68 function disconnect() 69 { 70 mssql_close($this->Link_ID); 71 } 72 73 function db_addslashes($str) 74 { 75 if (!IsSet($str) || $str == '') 76 { 77 return ''; 78 } 79 return str_replace("'", "''", $str); 80 } 81 82 function free_result() 83 { 84 if ($this->Query_ID) 85 { 86 mssql_free_result($this->Query_ID); 87 } 88 $this->Query_ID = 0; 89 $this->VEOF = -1; 90 } 91 92 function query($Query_String, $line = '', $file = '') 93 { 94 $this->VEOF = -1; 95 96 if (!$this->Link_ID) 97 { 98 $this->connect(); 99 } 100 101 $this->Query_ID = mssql_query($Query_String, $this->Link_ID); 102 $this->Row = 0; 103 if (!$this->Query_ID) 104 { 105 $this->halt("Invalid SQL: " . $Query_String, $line, $file); 106 } 107 return $this->Query_ID; 108 } 109 110 // I don't have access to M$-SQL, can someone finish these 2 functions ? (jengo) 111 function to_timestamp($epoch) 112 { 113 return date('Y-m-d H:i:s', $epoch); 114 } 115 116 function from_timestamp($timestamp) 117 { 118 return strtotime($timestamp); 119 } 120 121 // public: perform a query with limited result set 122 function limit_query($Query_String, $offset, $line = '', $file = '', $num_rows = '') 123 { 124 if (! $num_rows) 125 { 126 $num_rows = $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs']; 127 } 128 129 $Query_String = str_replace('SELECT ', 'SELECT TOP ', $Query_String); 130 $Query_String = str_replace('SELECT TOP DISTINCT', 'SELECT DISTINCT TOP ', $Query_String); 131 $Query_String = str_replace('TOP ', 'TOP ' . ($offset + $num_rows) . ' ', $Query_String); 132 133 if ($this->Debug) 134 { 135 printf("Debug: limit_query = %s<br />offset=%d, num_rows=%d<br />\n", $Query_String, $offset, $num_rows); 136 } 137 138 $this->query($Query_String, $line, $file); 139 if ($this->Query_ID) 140 { 141 $this->Row = $offset; 142 // Push cursor to appropriate row in case next_record() is used 143 if ($offset > 0) 144 { 145 @mssql_data_seek($this->Query_ID, $offset); 146 } 147 $this->VEOF = $offset + $num_rows - 1; 148 } 149 150 return $this->Query_ID; 151 } 152 153 function next_record() 154 { 155 if (!$this->Query_ID) 156 { 157 $this->halt("next_record called with no query pending."); 158 return 0; 159 } 160 161 if ($this->VEOF == -1 || ($this->Row++ <= $this->VEOF)) 162 { 163 // Work around for buggy mssql_fetch_array 164 $rec = @mssql_fetch_row($this->Query_ID); 165 if ($rec) 166 { 167 $this->Record = array(); 168 for ($i = 0; $i < count($rec); $i++) 169 { 170 $this->Record[$i] = $rec[$i]; 171 $o = mssql_fetch_field($this->Query_ID, $i); 172 $this->Record[$o->name] = $rec[$i]; 173 } 174 } 175 else 176 { 177 $this->Record = NULL; 178 } 179 } 180 else 181 { 182 $this->Record = NULL; 183 } 184 185 $stat = is_array($this->Record); 186 if (!$stat && $this->Auto_Free) 187 { 188 $this->free(); 189 } 190 191 return $stat; 192 } 193 194 function transaction_begin() 195 { 196 $this->Transaction = !!mssql_query('BEGIN TRAN', $this->Link_ID); 197 return $this->Transaction; 198 } 199 200 function transaction_commit() 201 { 202 if (!$this->Errno && $this->Transaction) 203 { 204 $this->Transaction = false; 205 return !!mssql_query('COMMIT TRAN', $this->Link_ID); 206 } 207 208 return False; 209 } 210 211 function transaction_abort() 212 { 213 if ($this->Transaction) 214 { 215 $this->Transaction = false; 216 return !!mssql_query('ROLLBACK TRAN', $this->Link_ID); 217 } 218 219 return false; 220 } 221 222 function seek($pos) 223 { 224 mssql_data_seek($this->Query_ID,$pos); 225 $this->Row = $pos; 226 } 227 228 function metadata($table) 229 { 230 $count = 0; 231 $id = 0; 232 $res = array(); 233 234 $this->connect(); 235 $id = mssql_query("exec sp_columns $table", $this->Link_ID); 236 if (!$id) 237 { 238 $this->halt('Metadata query failed.'); 239 } 240 241 $count = mssql_num_rows($id); 242 243 for ($i=0; $i<$count; $i++) 244 { 245 $res[$i]['table'] = $table; 246 $res[$i]['name'] = mssql_result($id, $i, 'COLUMN_NAME'); 247 $res[$i]['type'] = mssql_result($id, $i, 'TYPE_NAME'); 248 $res[$i]['len'] = mssql_result($id, $i, 'LENGTH'); 249 $res[$i]['flags'] = mssql_result($id, $i, 'REMARKS'); 250 } 251 $this->free_result(); 252 return $res; 253 } 254 255 function affected_rows() 256 { 257 $rsRows = mssql_query('SELECT @@rowcount as rows'); 258 return mssql_result($rsRows, 0, "rows"); 259 } 260 261 function num_rows() 262 { 263 if($this->Query_ID) 264 { 265 return $this->affected_rows(); 266 } 267 else 268 { 269 return 0; 270 } 271 } 272 273 function num_fields() 274 { 275 return mssql_num_fields($this->Query_ID); 276 } 277 278 function f($Field_Name) 279 { 280 if ($strip_slashes || ($this->auto_stripslashes && ! $strip_slashes)) 281 { 282 return str_replace("''", "'", $this->Record[$Field_Name]); 283 } 284 else 285 { 286 return $this->Record[$Field_Name]; 287 } 288 } 289 290 function get_last_insert_id($table, $field) 291 { 292 if (!isset($table) || $table == '' || !isset($field) || $field == '') 293 { 294 return -1; 295 } 296 297 $result = @mssql_query("select @@identity", $this->Link_ID); 298 if (!$result) 299 { 300 return -1; 301 } 302 return mssql_result($result, 0, 0); 303 } 304 305 function lock($table, $mode="write") 306 { 307 // /me really, really, really hates locks - transactions serve just fine 308 return $this->transaction_begin(); 309 } 310 311 function unlock() 312 { 313 return $this->transaction_commit(); 314 } 315 316 function halt($msg, $line = '', $file = '') 317 { 318 $this->transaction_abort(); 319 320 $this->Errno = 1; 321 $this->Error = mssql_get_last_message(); 322 if ($this->Error == '') 323 { 324 $this->Error = "General Error (The MS-SQL interface did not return a detailed error message)."; 325 } 326 327 if ($this->Halt_On_Error == "no") 328 { 329 return; 330 } 331 332 $this->haltmsg($msg); 333 334 if ($file) 335 { 336 printf("<br /><b>File:</b> %s",$file); 337 } 338 339 if ($line) 340 { 341 printf("<br /><b>Line:</b> %s",$line); 342 } 343 344 if ($this->Halt_On_Error != "report") 345 { 346 echo "<p><b>Session halted.</b>"; 347 $GLOBALS['phpgw']->common->phpgw_exit(True); 348 } 349 } 350 351 function haltmsg($msg) 352 { 353 printf("<b>Database error:</b> %s<br />\n", $msg); 354 if ($this->Errno != "0" && $this->Error != "()") 355 { 356 printf("<b>MS-SQL Error</b>: %s (%s)<br />\n", $this->Errno, $this->Error); 357 } 358 } 359 360 function table_names() 361 { 362 $this->query("select name from sysobjects where type='u' and name != 'dtproperties'"); 363 $i = 0; 364 while ($info = @mssql_fetch_row($this->Query_ID)) 365 { 366 $return[$i]['table_name'] = $info[0]; 367 $return[$i]['tablespace_name'] = $this->Database; 368 $return[$i]['database'] = $this->Database; 369 $i++; 370 } 371 return $return; 372 } 373 } 374?> 375