| [ Index ] |
|
Code source de vtiger CRM 5.0.2 |
1 <?php 2 /* 3 V4.90 8 June 2006 (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved. 4 Released under both BSD license and Lesser GPL library license. 5 Whenever there is any discrepancy between the two licenses, 6 the BSD license will take precedence. 7 Set tabs to 4 for best viewing. 8 9 Latest version is available at http://adodb.sourceforge.net 10 11 Native mssql driver. Requires mssql client. Works on Windows. 12 To configure for Unix, see 13 http://phpbuilder.com/columns/alberto20000919.php3 14 15 */ 16 17 // security - hide paths 18 if (!defined('ADODB_DIR')) die(); 19 20 //---------------------------------------------------------------- 21 // MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002 22 // and this causes tons of problems because localized versions of 23 // MSSQL will return the dates in dmy or mdy order; and also the 24 // month strings depends on what language has been configured. The 25 // following two variables allow you to control the localization 26 // settings - Ugh. 27 // 28 // MORE LOCALIZATION INFO 29 // ---------------------- 30 // To configure datetime, look for and modify sqlcommn.loc, 31 // typically found in c:\mssql\install 32 // Also read : 33 // http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918 34 // Alternatively use: 35 // CONVERT(char(12),datecol,120) 36 //---------------------------------------------------------------- 37 38 39 // has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc 40 if (ADODB_PHPVER >= 0x4300) { 41 // docs say 4.2.0, but testing shows only since 4.3.0 does it work! 42 ini_set('mssql.datetimeconvert',0); 43 } else { 44 global $ADODB_mssql_mths; // array, months must be upper-case 45 46 47 $ADODB_mssql_date_order = 'mdy'; 48 $ADODB_mssql_mths = array( 49 'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6, 50 'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12); 51 } 52 53 //--------------------------------------------------------------------------- 54 // Call this to autoset $ADODB_mssql_date_order at the beginning of your code, 55 // just after you connect to the database. Supports mdy and dmy only. 56 // Not required for PHP 4.2.0 and above. 57 function AutoDetect_MSSQL_Date_Order($conn) 58 { 59 global $ADODB_mssql_date_order; 60 $adate = $conn->GetOne('select getdate()'); 61 if ($adate) { 62 $anum = (int) $adate; 63 if ($anum > 0) { 64 if ($anum > 31) { 65 //ADOConnection::outp( "MSSQL: YYYY-MM-DD date format not supported currently"); 66 } else 67 $ADODB_mssql_date_order = 'dmy'; 68 } else 69 $ADODB_mssql_date_order = 'mdy'; 70 } 71 } 72 73 class ADODB_mssql extends ADOConnection { 74 var $databaseType = "mssql"; 75 var $dataProvider = "mssql"; 76 var $replaceQuote = "''"; // string to use to replace quotes 77 var $fmtDate = "'Y-m-d'"; 78 var $fmtTimeStamp = "'Y-m-d H:i:s'"; 79 var $hasInsertID = true; 80 var $substr = "substring"; 81 var $length = 'len'; 82 var $hasAffectedRows = true; 83 var $metaDatabasesSQL = "select name from sysdatabases where name <> 'master'"; 84 var $metaTablesSQL="select name,case when type='U' then 'T' else 'V' end from sysobjects where (type='U' or type='V') and (name not in ('sysallocations','syscolumns','syscomments','sysdepends','sysfilegroups','sysfiles','sysfiles1','sysforeignkeys','sysfulltextcatalogs','sysindexes','sysindexkeys','sysmembers','sysobjects','syspermissions','sysprotects','sysreferences','systypes','sysusers','sysalternates','sysconstraints','syssegments','REFERENTIAL_CONSTRAINTS','CHECK_CONSTRAINTS','CONSTRAINT_TABLE_USAGE','CONSTRAINT_COLUMN_USAGE','VIEWS','VIEW_TABLE_USAGE','VIEW_COLUMN_USAGE','SCHEMATA','TABLES','TABLE_CONSTRAINTS','TABLE_PRIVILEGES','COLUMNS','COLUMN_DOMAIN_USAGE','COLUMN_PRIVILEGES','DOMAINS','DOMAIN_CONSTRAINTS','KEY_COLUMN_USAGE','dtproperties'))"; 85 var $metaColumnsSQL = # xtype==61 is datetime 86 "select c.name,t.name,c.length, 87 (case when c.xusertype=61 then 0 else c.xprec end), 88 (case when c.xusertype=61 then 0 else c.xscale end) 89 from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'"; 90 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE 91 var $hasGenID = true; 92 var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)'; 93 var $sysTimeStamp = 'GetDate()'; 94 var $_has_mssql_init; 95 var $maxParameterLen = 4000; 96 var $arrayClass = 'ADORecordSet_array_mssql'; 97 var $uniqueSort = true; 98 var $leftOuter = '*='; 99 var $rightOuter = '=*'; 100 var $ansiOuter = true; // for mssql7 or later 101 var $poorAffectedRows = true; 102 var $identitySQL = 'select @@IDENTITY'; // 'select SCOPE_IDENTITY'; # for mssql 2000 103 var $uniqueOrderBy = true; 104 var $_bindInputArray = true; 105 106 function ADODB_mssql() 107 { 108 $this->_has_mssql_init = (strnatcmp(PHP_VERSION,'4.1.0')>=0); 109 } 110 111 function ServerInfo() 112 { 113 global $ADODB_FETCH_MODE; 114 115 $stmt = $this->PrepareSP('sp_server_info'); 116 $val = 2; 117 if ($this->fetchMode === false) { 118 $savem = $ADODB_FETCH_MODE; 119 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 120 } else 121 $savem = $this->SetFetchMode(ADODB_FETCH_NUM); 122 123 124 $this->Parameter($stmt,$val,'attribute_id'); 125 $row = $this->GetRow($stmt); 126 127 //$row = $this->GetRow("execute sp_server_info 2"); 128 129 130 if ($this->fetchMode === false) { 131 $ADODB_FETCH_MODE = $savem; 132 } else 133 $this->SetFetchMode($savem); 134 135 $arr['description'] = $row[2]; 136 $arr['version'] = ADOConnection::_findvers($arr['description']); 137 return $arr; 138 } 139 140 function IfNull( $field, $ifNull ) 141 { 142 return " ISNULL($field, $ifNull) "; // if MS SQL Server 143 } 144 145 function _insertid() 146 { 147 // SCOPE_IDENTITY() 148 // Returns the last IDENTITY value inserted into an IDENTITY column in 149 // the same scope. A scope is a module -- a stored procedure, trigger, 150 // function, or batch. Thus, two statements are in the same scope if 151 // they are in the same stored procedure, function, or batch. 152 return $this->GetOne($this->identitySQL); 153 } 154 155 function _affectedrows() 156 { 157 return $this->GetOne('select @@rowcount'); 158 } 159 160 var $_dropSeqSQL = "drop table %s"; 161 162 function CreateSequence($seq='adodbseq',$start=1) 163 { 164 165 $this->Execute('BEGIN TRANSACTION adodbseq'); 166 $start -= 1; 167 $this->Execute("create table $seq (id float(53))"); 168 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)"); 169 if (!$ok) { 170 $this->Execute('ROLLBACK TRANSACTION adodbseq'); 171 return false; 172 } 173 $this->Execute('COMMIT TRANSACTION adodbseq'); 174 return true; 175 } 176 177 function GenID($seq='adodbseq',$start=1) 178 { 179 //$this->debug=1; 180 $this->Execute('BEGIN TRANSACTION adodbseq'); 181 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1"); 182 if (!$ok) { 183 $this->Execute("create table $seq (id float(53))"); 184 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)"); 185 if (!$ok) { 186 $this->Execute('ROLLBACK TRANSACTION adodbseq'); 187 return false; 188 } 189 $this->Execute('COMMIT TRANSACTION adodbseq'); 190 return $start; 191 } 192 $num = $this->GetOne("select id from $seq"); 193 $this->Execute('COMMIT TRANSACTION adodbseq'); 194 return $num; 195 196 // in old implementation, pre 1.90, we returned GUID... 197 //return $this->GetOne("SELECT CONVERT(varchar(255), NEWID()) AS 'Char'"); 198 } 199 200 201 function &SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0) 202 { 203 if ($nrows > 0 && $offset <= 0) { 204 $sql = preg_replace( 205 '/(^\s*select\s+(distinctrow|distinct)?)/i','\\1 '.$this->hasTop." $nrows ",$sql); 206 $rs =& $this->Execute($sql,$inputarr); 207 } else 208 $rs =& ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache); 209 210 return $rs; 211 } 212 213 214 // Format date column in sql string given an input format that understands Y M D 215 function SQLDate($fmt, $col=false) 216 { 217 if (!$col) $col = $this->sysTimeStamp; 218 $s = ''; 219 220 $len = strlen($fmt); 221 for ($i=0; $i < $len; $i++) { 222 if ($s) $s .= '+'; 223 $ch = $fmt[$i]; 224 switch($ch) { 225 case 'Y': 226 case 'y': 227 $s .= "datename(yyyy,$col)"; 228 break; 229 case 'M': 230 $s .= "convert(char(3),$col,0)"; 231 break; 232 case 'm': 233 $s .= "replace(str(month($col),2),' ','0')"; 234 break; 235 case 'Q': 236 case 'q': 237 $s .= "datename(quarter,$col)"; 238 break; 239 case 'D': 240 case 'd': 241 $s .= "replace(str(day($col),2),' ','0')"; 242 break; 243 case 'h': 244 $s .= "substring(convert(char(14),$col,0),13,2)"; 245 break; 246 247 case 'H': 248 $s .= "replace(str(datepart(hh,$col),2),' ','0')"; 249 break; 250 251 case 'i': 252 $s .= "replace(str(datepart(mi,$col),2),' ','0')"; 253 break; 254 case 's': 255 $s .= "replace(str(datepart(ss,$col),2),' ','0')"; 256 break; 257 case 'a': 258 case 'A': 259 $s .= "substring(convert(char(19),$col,0),18,2)"; 260 break; 261 262 default: 263 if ($ch == '\\') { 264 $i++; 265 $ch = substr($fmt,$i,1); 266 } 267 $s .= $this->qstr($ch); 268 break; 269 } 270 } 271 return $s; 272 } 273 274 275 function BeginTrans() 276 { 277 if ($this->transOff) return true; 278 $this->transCnt += 1; 279 $this->Execute('BEGIN TRAN'); 280 return true; 281 } 282 283 function CommitTrans($ok=true) 284 { 285 if ($this->transOff) return true; 286 if (!$ok) return $this->RollbackTrans(); 287 if ($this->transCnt) $this->transCnt -= 1; 288 $this->Execute('COMMIT TRAN'); 289 return true; 290 } 291 function RollbackTrans() 292 { 293 if ($this->transOff) return true; 294 if ($this->transCnt) $this->transCnt -= 1; 295 $this->Execute('ROLLBACK TRAN'); 296 return true; 297 } 298 299 function SetTransactionMode( $transaction_mode ) 300 { 301 $this->_transmode = $transaction_mode; 302 if (empty($transaction_mode)) { 303 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED'); 304 return; 305 } 306 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode; 307 $this->Execute("SET TRANSACTION ".$transaction_mode); 308 } 309 310 /* 311 Usage: 312 313 $this->BeginTrans(); 314 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables 315 316 # some operation on both tables table1 and table2 317 318 $this->CommitTrans(); 319 320 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp 321 */ 322 function RowLock($tables,$where,$flds='top 1 null as ignore') 323 { 324 if (!$this->transCnt) $this->BeginTrans(); 325 return $this->GetOne("select $flds from $tables with (ROWLOCK,HOLDLOCK) where $where"); 326 } 327 328 329 function &MetaIndexes($table,$primary=false) 330 { 331 $table = $this->qstr($table); 332 333 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno, 334 CASE WHEN I.indid BETWEEN 1 AND 254 AND (I.status & 2048 = 2048 OR I.Status = 16402 AND O.XType = 'V') THEN 1 ELSE 0 END AS IsPK, 335 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique 336 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id 337 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid 338 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid 339 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table 340 ORDER BY O.name, I.Name, K.keyno"; 341 342 global $ADODB_FETCH_MODE; 343 $save = $ADODB_FETCH_MODE; 344 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 345 if ($this->fetchMode !== FALSE) { 346 $savem = $this->SetFetchMode(FALSE); 347 } 348 349 $rs = $this->Execute($sql); 350 if (isset($savem)) { 351 $this->SetFetchMode($savem); 352 } 353 $ADODB_FETCH_MODE = $save; 354 355 if (!is_object($rs)) { 356 return FALSE; 357 } 358 359 $indexes = array(); 360 while ($row = $rs->FetchRow()) { 361 if (!$primary && $row[5]) continue; 362 363 $indexes[$row[0]]['unique'] = $row[6]; 364 $indexes[$row[0]]['columns'][] = $row[1]; 365 } 366 return $indexes; 367 } 368 369 function MetaForeignKeys($table, $owner=false, $upper=false) 370 { 371 global $ADODB_FETCH_MODE; 372 373 $save = $ADODB_FETCH_MODE; 374 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 375 $table = $this->qstr(strtoupper($table)); 376 377 $sql = 378 "select object_name(constid) as constraint_name, 379 col_name(fkeyid, fkey) as column_name, 380 object_name(rkeyid) as referenced_table_name, 381 col_name(rkeyid, rkey) as referenced_column_name 382 from sysforeignkeys 383 where upper(object_name(fkeyid)) = $table 384 order by constraint_name, referenced_table_name, keyno"; 385 386 $constraints =& $this->GetArray($sql); 387 388 $ADODB_FETCH_MODE = $save; 389 390 $arr = false; 391 foreach($constraints as $constr) { 392 //print_r($constr); 393 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3]; 394 } 395 if (!$arr) return false; 396 397 $arr2 = false; 398 399 foreach($arr as $k => $v) { 400 foreach($v as $a => $b) { 401 if ($upper) $a = strtoupper($a); 402 $arr2[$a] = $b; 403 } 404 } 405 return $arr2; 406 } 407 408 //From: Fernando Moreira <FMoreira@imediata.pt> 409 function MetaDatabases() 410 { 411 if(@mssql_select_db("master")) { 412 $qry=$this->metaDatabasesSQL; 413 if($rs=@mssql_query($qry)){ 414 $tmpAr=$ar=array(); 415 while($tmpAr=@mssql_fetch_row($rs)) 416 $ar[]=$tmpAr[0]; 417 @mssql_select_db($this->database); 418 if(sizeof($ar)) 419 return($ar); 420 else 421 return(false); 422 } else { 423 @mssql_select_db($this->database); 424 return(false); 425 } 426 } 427 return(false); 428 } 429 430 // "Stein-Aksel Basma" <basma@accelero.no> 431 // tested with MSSQL 2000 432 function &MetaPrimaryKeys($table) 433 { 434 global $ADODB_FETCH_MODE; 435 436 $schema = ''; 437 $this->_findschema($table,$schema); 438 if (!$schema) $schema = $this->database; 439 if ($schema) $schema = "and k.table_catalog like '$schema%'"; 440 441 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k, 442 information_schema.table_constraints tc 443 where tc.constraint_name = k.constraint_name and tc.constraint_type = 444 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position "; 445 446 $savem = $ADODB_FETCH_MODE; 447 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 448 $a = $this->GetCol($sql); 449 $ADODB_FETCH_MODE = $savem; 450 451 if ($a && sizeof($a)>0) return $a; 452 $false = false; 453 return $false; 454 } 455 456 457 function &MetaTables($ttype=false,$showSchema=false,$mask=false) 458 { 459 if ($mask) { 460 $save = $this->metaTablesSQL; 461 $mask = $this->qstr(($mask)); 462 $this->metaTablesSQL .= " AND name like $mask"; 463 } 464 $ret =& ADOConnection::MetaTables($ttype,$showSchema); 465 466 if ($mask) { 467 $this->metaTablesSQL = $save; 468 } 469 return $ret; 470 } 471 472 function SelectDB($dbName) 473 { 474 $this->database = $dbName; 475 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions 476 if ($this->_connectionID) { 477 return @mssql_select_db($dbName); 478 } 479 else return false; 480 } 481 482 function ErrorMsg() 483 { 484 if (empty($this->_errorMsg)){ 485 $this->_errorMsg = mssql_get_last_message(); 486 } 487 return $this->_errorMsg; 488 } 489 490 function ErrorNo() 491 { 492 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode; 493 if (empty($this->_errorMsg)) { 494 $this->_errorMsg = mssql_get_last_message(); 495 } 496 $id = @mssql_query("select @@ERROR",$this->_connectionID); 497 if (!$id) return false; 498 $arr = mssql_fetch_array($id); 499 @mssql_free_result($id); 500 if (is_array($arr)) return $arr[0]; 501 else return -1; 502 } 503 504 // returns true or false 505 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename) 506 { 507 if (!function_exists('mssql_pconnect')) return null; 508 $this->_connectionID = mssql_connect($argHostname,$argUsername,$argPassword); 509 if ($this->_connectionID === false) return false; 510 if ($argDatabasename) return $this->SelectDB($argDatabasename); 511 return true; 512 } 513 514 515 // returns true or false 516 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) 517 { 518 if (!function_exists('mssql_pconnect')) return null; 519 $this->_connectionID = mssql_pconnect($argHostname,$argUsername,$argPassword); 520 if ($this->_connectionID === false) return false; 521 522 // persistent connections can forget to rollback on crash, so we do it here. 523 if ($this->autoRollback) { 524 $cnt = $this->GetOne('select @@TRANCOUNT'); 525 while (--$cnt >= 0) $this->Execute('ROLLBACK TRAN'); 526 } 527 if ($argDatabasename) return $this->SelectDB($argDatabasename); 528 return true; 529 } 530 531 function Prepare($sql) 532 { 533 $sqlarr = explode('?',$sql); 534 if (sizeof($sqlarr) <= 1) return $sql; 535 $sql2 = $sqlarr[0]; 536 for ($i = 1, $max = sizeof($sqlarr); $i < $max; $i++) { 537 $sql2 .= '@P'.($i-1) . $sqlarr[$i]; 538 } 539 return array($sql,$this->qstr($sql2),$max); 540 } 541 542 function PrepareSP($sql) 543 { 544 if (!$this->_has_mssql_init) { 545 ADOConnection::outp( "PrepareSP: mssql_init only available since PHP 4.1.0"); 546 return $sql; 547 } 548 $stmt = mssql_init($sql,$this->_connectionID); 549 if (!$stmt) return $sql; 550 return array($sql,$stmt); 551 } 552 553 // returns concatenated string 554 // MSSQL requires integers to be cast as strings 555 // automatically cast every datatype to VARCHAR(255) 556 // @author David Rogers (introspectshun) 557 function Concat() 558 { 559 $s = ""; 560 $arr = func_get_args(); 561 562 // Split single record on commas, if possible 563 if (sizeof($arr) == 1) { 564 foreach ($arr as $arg) { 565 $args = explode(',', $arg); 566 } 567 $arr = $args; 568 } 569 570 array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";')); 571 $s = implode('+',$arr); 572 if (sizeof($arr) > 0) return "$s"; 573 574 return ''; 575 } 576 577 /* 578 Usage: 579 $stmt = $db->PrepareSP('SP_RUNSOMETHING'); -- takes 2 params, @myid and @group 580 581 # note that the parameter does not have @ in front! 582 $db->Parameter($stmt,$id,'myid'); 583 $db->Parameter($stmt,$group,'group',false,64); 584 $db->Execute($stmt); 585 586 @param $stmt Statement returned by Prepare() or PrepareSP(). 587 @param $var PHP variable to bind to. Can set to null (for isNull support). 588 @param $name Name of stored procedure variable name to bind to. 589 @param [$isOutput] Indicates direction of parameter 0/false=IN 1=OUT 2= IN/OUT. This is ignored in oci8. 590 @param [$maxLen] Holds an maximum length of the variable. 591 @param [$type] The data type of $var. Legal values depend on driver. 592 593 See mssql_bind documentation at php.net. 594 */ 595 function Parameter(&$stmt, &$var, $name, $isOutput=false, $maxLen=4000, $type=false) 596 { 597 if (!$this->_has_mssql_init) { 598 ADOConnection::outp( "Parameter: mssql_bind only available since PHP 4.1.0"); 599 return false; 600 } 601 602 $isNull = is_null($var); // php 4.0.4 and above... 603 604 if ($type === false) 605 switch(gettype($var)) { 606 default: 607 case 'string': $type = SQLCHAR; break; 608 case 'double': $type = SQLFLT8; break; 609 case 'integer': $type = SQLINT4; break; 610 case 'boolean': $type = SQLINT1; break; # SQLBIT not supported in 4.1.0 611 } 612 613 if ($this->debug) { 614 $prefix = ($isOutput) ? 'Out' : 'In'; 615 $ztype = (empty($type)) ? 'false' : $type; 616 ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);"); 617 } 618 /* 619 See http://phplens.com/lens/lensforum/msgs.php?id=7231 620 621 RETVAL is HARD CODED into php_mssql extension: 622 The return value (a long integer value) is treated like a special OUTPUT parameter, 623 called "RETVAL" (without the @). See the example at mssql_execute to 624 see how it works. - type: one of this new supported PHP constants. 625 SQLTEXT, SQLVARCHAR,SQLCHAR, SQLINT1,SQLINT2, SQLINT4, SQLBIT,SQLFLT8 626 */ 627 if ($name !== 'RETVAL') $name = '@'.$name; 628 return mssql_bind($stmt[1], $name, $var, $type, $isOutput, $isNull, $maxLen); 629 } 630 631 /* 632 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars 633 So all your blobs must be of type "image". 634 635 Remember to set in php.ini the following... 636 637 ; Valid range 0 - 2147483647. Default = 4096. 638 mssql.textlimit = 0 ; zero to pass through 639 640 ; Valid range 0 - 2147483647. Default = 4096. 641 mssql.textsize = 0 ; zero to pass through 642 */ 643 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB') 644 { 645 646 if (strtoupper($blobtype) == 'CLOB') { 647 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where"; 648 return $this->Execute($sql) != false; 649 } 650 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where"; 651 return $this->Execute($sql) != false; 652 } 653 654 // returns query ID if successful, otherwise false 655 function _query($sql,$inputarr) 656 { 657 $this->_errorMsg = false; 658 if (is_array($inputarr)) { 659 660 # bind input params with sp_executesql: 661 # see http://www.quest-pipelines.com/newsletter-v3/0402_F.htm 662 # works only with sql server 7 and newer 663 if (!is_array($sql)) $sql = $this->Prepare($sql); 664 $params = ''; 665 $decl = ''; 666 $i = 0; 667 foreach($inputarr as $v) { 668 if ($decl) { 669 $decl .= ', '; 670 $params .= ', '; 671 } 672 if (is_string($v)) { 673 $len = strlen($v); 674 if ($len == 0) $len = 1; 675 676 if ($len > 4000 ) { 677 // NVARCHAR is max 4000 chars. Let's use NTEXT 678 $decl .= "@P$i NTEXT"; 679 } else { 680 $decl .= "@P$i NVARCHAR($len)"; 681 } 682 683 $params .= "@P$i=N". (strncmp($v,"'",1)==0? $v : $this->qstr($v)); 684 } else if (is_integer($v)) { 685 $decl .= "@P$i INT"; 686 $params .= "@P$i=".$v; 687 } else if (is_float($v)) { 688 $decl .= "@P$i FLOAT"; 689 $params .= "@P$i=".$v; 690 } else if (is_bool($v)) { 691 $decl .= "@P$i INT"; # Used INT just in case BIT in not supported on the user's MSSQL version. It will cast appropriately. 692 $params .= "@P$i=".(($v)?'1':'0'); # True == 1 in MSSQL BIT fields and acceptable for storing logical true in an int field 693 } else { 694 $decl .= "@P$i CHAR"; # Used char because a type is required even when the value is to be NULL. 695 $params .= "@P$i=NULL"; 696 } 697 $i += 1; 698 } 699 $decl = $this->qstr($decl); 700 if ($this->debug) ADOConnection::outp("<font size=-1>sp_executesql N{$sql[1]},N$decl,$params</font>"); 701 $rez = mssql_query("sp_executesql N{$sql[1]},N$decl,$params"); 702 703 } else if (is_array($sql)) { 704 # PrepareSP() 705 $rez = mssql_execute($sql[1]); 706 707 } else { 708 $rez = mssql_query($sql,$this->_connectionID); 709 } 710 return $rez; 711 } 712 713 // returns true or false 714 function _close() 715 { 716 if ($this->transCnt) $this->RollbackTrans(); 717 $rez = @mssql_close($this->_connectionID); 718 $this->_connectionID = false; 719 return $rez; 720 } 721 722 // mssql uses a default date like Dec 30 2000 12:00AM 723 function UnixDate($v) 724 { 725 return ADORecordSet_array_mssql::UnixDate($v); 726 } 727 728 function UnixTimeStamp($v) 729 { 730 return ADORecordSet_array_mssql::UnixTimeStamp($v); 731 } 732 } 733 734 /*-------------------------------------------------------------------------------------- 735 Class Name: Recordset 736 --------------------------------------------------------------------------------------*/ 737 738 class ADORecordset_mssql extends ADORecordSet { 739 740 var $databaseType = "mssql"; 741 var $canSeek = true; 742 var $hasFetchAssoc; // see http://phplens.com/lens/lensforum/msgs.php?id=6083 743 // _mths works only in non-localised system 744 745 function ADORecordset_mssql($id,$mode=false) 746 { 747 // freedts check... 748 $this->hasFetchAssoc = function_exists('mssql_fetch_assoc'); 749 750 if ($mode === false) { 751 global $ADODB_FETCH_MODE; 752 $mode = $ADODB_FETCH_MODE; 753 754 } 755 $this->fetchMode = $mode; 756 return $this->ADORecordSet($id,$mode); 757 } 758 759 760 function _initrs() 761 { 762 GLOBAL $ADODB_COUNTRECS; 763 $this->_numOfRows = ($ADODB_COUNTRECS)? @mssql_num_rows($this->_queryID):-1; 764 $this->_numOfFields = @mssql_num_fields($this->_queryID); 765 } 766 767 768 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se> 769 // get next resultset - requires PHP 4.0.5 or later 770 function NextRecordSet() 771 { 772 if (!mssql_next_result($this->_queryID)) return false; 773 $this->_inited = false; 774 $this->bind = false; 775 $this->_currentRow = -1; 776 $this->Init(); 777 return true; 778 } 779 780 /* Use associative array to get fields array */ 781 function Fields($colname) 782 { 783 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname]; 784 if (!$this->bind) { 785 $this->bind = array(); 786 for ($i=0; $i < $this->_numOfFields; $i++) { 787 $o = $this->FetchField($i); 788 $this->bind[strtoupper($o->name)] = $i; 789 } 790 } 791 792 return $this->fields[$this->bind[strtoupper($colname)]]; 793 } 794 795 /* Returns: an object containing field information. 796 Get column information in the Recordset object. fetchField() can be used in order to obtain information about 797 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by 798 fetchField() is retrieved. */ 799 800 function &FetchField($fieldOffset = -1) 801 { 802 if ($fieldOffset != -1) { 803 $f = @mssql_fetch_field($this->_queryID, $fieldOffset); 804 } 805 else if ($fieldOffset == -1) { /* The $fieldOffset argument is not provided thus its -1 */ 806 $f = @mssql_fetch_field($this->_queryID); 807 } 808 $false = false; 809 if (empty($f)) return $false; 810 return $f; 811 } 812 813 function _seek($row) 814 { 815 return @mssql_data_seek($this->_queryID, $row); 816 } 817 818 // speedup 819 function MoveNext() 820 { 821 if ($this->EOF) return false; 822 823 $this->_currentRow++; 824 825 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 826 if ($this->fetchMode & ADODB_FETCH_NUM) { 827 //ADODB_FETCH_BOTH mode 828 $this->fields = @mssql_fetch_array($this->_queryID); 829 } 830 else { 831 if ($this->hasFetchAssoc) {// only for PHP 4.2.0 or later 832 $this->fields = @mssql_fetch_assoc($this->_queryID); 833 } else { 834 $flds = @mssql_fetch_array($this->_queryID); 835 if (is_array($flds)) { 836 $fassoc = array(); 837 foreach($flds as $k => $v) { 838 if (is_numeric($k)) continue; 839 $fassoc[$k] = $v; 840 } 841 $this->fields = $fassoc; 842 } else 843 $this->fields = false; 844 } 845 } 846 847 if (is_array($this->fields)) { 848 if (ADODB_ASSOC_CASE == 0) { 849 foreach($this->fields as $k=>$v) { 850 $this->fields[strtolower($k)] = $v; 851 } 852 } else if (ADODB_ASSOC_CASE == 1) { 853 foreach($this->fields as $k=>$v) { 854 $this->fields[strtoupper($k)] = $v; 855 } 856 } 857 } 858 } else { 859 $this->fields = @mssql_fetch_row($this->_queryID); 860 } 861 if ($this->fields) return true; 862 $this->EOF = true; 863 864 return false; 865 } 866 867 868 // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4 869 // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot! 870 function _fetch($ignore_fields=false) 871 { 872 if ($this->fetchMode & ADODB_FETCH_ASSOC) { 873 if ($this->fetchMode & ADODB_FETCH_NUM) { 874 //ADODB_FETCH_BOTH mode 875 $this->fields = @mssql_fetch_array($this->_queryID); 876 } else { 877 if ($this->hasFetchAssoc) // only for PHP 4.2.0 or later 878 $this->fields = @mssql_fetch_assoc($this->_queryID); 879 else { 880 $this->fields = @mssql_fetch_array($this->_queryID); 881 if (@is_array($$this->fields)) { 882 $fassoc = array(); 883 foreach($$this->fields as $k => $v) { 884 if (is_integer($k)) continue; 885 $fassoc[$k] = $v; 886 } 887 $this->fields = $fassoc; 888 } 889 } 890 } 891 892 if (!$this->fields) { 893 } else if (ADODB_ASSOC_CASE == 0) { 894 foreach($this->fields as $k=>$v) { 895 $this->fields[strtolower($k)] = $v; 896 } 897 } else if (ADODB_ASSOC_CASE == 1) { 898 foreach($this->fields as $k=>$v) { 899 $this->fields[strtoupper($k)] = $v; 900 } 901 } 902 } else { 903 $this->fields = @mssql_fetch_row($this->_queryID); 904 } 905 return $this->fields; 906 } 907 908 /* close() only needs to be called if you are worried about using too much memory while your script 909 is running. All associated result memory for the specified result identifier will automatically be freed. */ 910 911 function _close() 912 { 913 $rez = mssql_free_result($this->_queryID); 914 $this->_queryID = false; 915 return $rez; 916 } 917 // mssql uses a default date like Dec 30 2000 12:00AM 918 function UnixDate($v) 919 { 920 return ADORecordSet_array_mssql::UnixDate($v); 921 } 922 923 function UnixTimeStamp($v) 924 { 925 return ADORecordSet_array_mssql::UnixTimeStamp($v); 926 } 927 928 } 929 930 931 class ADORecordSet_array_mssql extends ADORecordSet_array { 932 function ADORecordSet_array_mssql($id=-1,$mode=false) 933 { 934 $this->ADORecordSet_array($id,$mode); 935 } 936 937 // mssql uses a default date like Dec 30 2000 12:00AM 938 function UnixDate($v) 939 { 940 941 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v); 942 943 global $ADODB_mssql_mths,$ADODB_mssql_date_order; 944 945 //Dec 30 2000 12:00AM 946 if ($ADODB_mssql_date_order == 'dmy') { 947 if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) { 948 return parent::UnixDate($v); 949 } 950 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 951 952 $theday = $rr[1]; 953 $themth = substr(strtoupper($rr[2]),0,3); 954 } else { 955 if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) { 956 return parent::UnixDate($v); 957 } 958 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 959 960 $theday = $rr[2]; 961 $themth = substr(strtoupper($rr[1]),0,3); 962 } 963 $themth = $ADODB_mssql_mths[$themth]; 964 if ($themth <= 0) return false; 965 // h-m-s-MM-DD-YY 966 return mktime(0,0,0,$themth,$theday,$rr[3]); 967 } 968 969 function UnixTimeStamp($v) 970 { 971 972 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v); 973 974 global $ADODB_mssql_mths,$ADODB_mssql_date_order; 975 976 //Dec 30 2000 12:00AM 977 if ($ADODB_mssql_date_order == 'dmy') { 978 if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4}) +([0-9]{1,2}):([0-9]{1,2}) *([apAP]{0,1})|" 979 ,$v, $rr)) return parent::UnixTimeStamp($v); 980 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 981 982 $theday = $rr[1]; 983 $themth = substr(strtoupper($rr[2]),0,3); 984 } else { 985 if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4}) +([0-9]{1,2}):([0-9]{1,2}) *([apAP]{0,1})|" 986 ,$v, $rr)) return parent::UnixTimeStamp($v); 987 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0; 988 989 $theday = $rr[2]; 990 $themth = substr(strtoupper($rr[1]),0,3); 991 } 992 993 $themth = $ADODB_mssql_mths[$themth]; 994 if ($themth <= 0) return false; 995 996 switch (strtoupper($rr[6])) { 997 case 'P': 998 if ($rr[4]<12) $rr[4] += 12; 999 break; 1000 case 'A': 1001 if ($rr[4]==12) $rr[4] = 0; 1002 break; 1003 default: 1004 break; 1005 } 1006 // h-m-s-MM-DD-YY 1007 return mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]); 1008 } 1009 } 1010 1011 /* 1012 Code Example 1: 1013 1014 select object_name(constid) as constraint_name, 1015 object_name(fkeyid) as table_name, 1016 col_name(fkeyid, fkey) as column_name, 1017 object_name(rkeyid) as referenced_table_name, 1018 col_name(rkeyid, rkey) as referenced_column_name 1019 from sysforeignkeys 1020 where object_name(fkeyid) = x 1021 order by constraint_name, table_name, referenced_table_name, keyno 1022 1023 Code Example 2: 1024 select constraint_name, 1025 column_name, 1026 ordinal_position 1027 from information_schema.key_column_usage 1028 where constraint_catalog = db_name() 1029 and table_name = x 1030 order by constraint_name, ordinal_position 1031 1032 http://www.databasejournal.com/scripts/article.php/1440551 1033 */ 1034 1035 ?>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Sun Feb 25 10:22:19 2007 | par Balluche grâce à PHPXref 0.7 |