PageRenderTime 899ms CodeModel.GetById 121ms app.highlight 650ms RepoModel.GetById 117ms app.codeStats 0ms

/concreteOLD/libraries/3rdparty/adodb/drivers/adodb-mssql.inc.php

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