"TYPO3 4.0.1: typo3_src-4.0.1/typo3/sysext/adodb/adodb/drivers/adodb-mssql.inc.php Source File", "datetime" => "Sat Dec 2 19:22:25 2006", "date" => "2 Dec 2006", "doxygenversion" => "1.4.6", "projectname" => "TYPO3 4.0.1", "projectnumber" => "4.0.1" ); get_header($doxygen_vars); ?>

adodb-mssql.inc.php

00001 <?php
00002 /* 
00003 V4.90 8 June 2006  (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
00004   Released under both BSD license and Lesser GPL library license. 
00005   Whenever there is any discrepancy between the two licenses, 
00006   the BSD license will take precedence. 
00007 Set tabs to 4 for best viewing.
00008   
00009   Latest version is available at http://adodb.sourceforge.net
00010   
00011   Native mssql driver. Requires mssql client. Works on Windows. 
00012   To configure for Unix, see 
00013         http://phpbuilder.com/columns/alberto20000919.php3
00014         
00015 */
00016 
00017 // security - hide paths
00018 if (!defined('ADODB_DIR')) die();
00019 
00020 //----------------------------------------------------------------
00021 // MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002
00022 // and this causes tons of problems because localized versions of 
00023 // MSSQL will return the dates in dmy or  mdy order; and also the 
00024 // month strings depends on what language has been configured. The 
00025 // following two variables allow you to control the localization
00026 // settings - Ugh.
00027 //
00028 // MORE LOCALIZATION INFO
00029 // ----------------------
00030 // To configure datetime, look for and modify sqlcommn.loc, 
00031 //      typically found in c:\mssql\install
00032 // Also read :
00033 //       http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918
00034 // Alternatively use:
00035 //         CONVERT(char(12),datecol,120)
00036 //----------------------------------------------------------------
00037 
00038 
00039 // has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc
00040 if (ADODB_PHPVER >= 0x4300) {
00041 // docs say 4.2.0, but testing shows only since 4.3.0 does it work!
00042         ini_set('mssql.datetimeconvert',0); 
00043 } else {
00044 global $ADODB_mssql_mths;               // array, months must be upper-case
00045 
00046 
00047         $ADODB_mssql_date_order = 'mdy'; 
00048         $ADODB_mssql_mths = array(
00049                 'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6,
00050                 'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12);
00051 }
00052 
00053 //---------------------------------------------------------------------------
00054 // Call this to autoset $ADODB_mssql_date_order at the beginning of your code,
00055 // just after you connect to the database. Supports mdy and dmy only.
00056 // Not required for PHP 4.2.0 and above.
00057 function AutoDetect_MSSQL_Date_Order($conn)
00058 {
00059 global $ADODB_mssql_date_order;
00060         $adate = $conn->GetOne('select getdate()');
00061         if ($adate) {
00062                 $anum = (int) $adate;
00063                 if ($anum > 0) {
00064                         if ($anum > 31) {
00065                                 //ADOConnection::outp( "MSSQL: YYYY-MM-DD date format not supported currently");
00066                         } else
00067                                 $ADODB_mssql_date_order = 'dmy';
00068                 } else
00069                         $ADODB_mssql_date_order = 'mdy';
00070         }
00071 }
00072 
00073 class ADODB_mssql extends ADOConnection {
00074         var $databaseType = "mssql";    
00075         var $dataProvider = "mssql";
00076         var $replaceQuote = "''"; // string to use to replace quotes
00077         var $fmtDate = "'Y-m-d'";
00078         var $fmtTimeStamp = "'Y-m-d H:i:s'";
00079         var $hasInsertID = true;
00080         var $substr = "substring";
00081         var $length = 'len';
00082         var $hasAffectedRows = true;
00083         var $metaDatabasesSQL = "select name from sysdatabases where name <> 'master'";
00084         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'))";
00085         var $metaColumnsSQL = # xtype==61 is datetime
00086 "select c.name,t.name,c.length,
00087         (case when c.xusertype=61 then 0 else c.xprec end),
00088         (case when c.xusertype=61 then 0 else c.xscale end) 
00089         from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'";
00090         var $hasTop = 'top';            // support mssql SELECT TOP 10 * FROM TABLE
00091         var $hasGenID = true;
00092         var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)';
00093         var $sysTimeStamp = 'GetDate()';
00094         var $_has_mssql_init;
00095         var $maxParameterLen = 4000;
00096         var $arrayClass = 'ADORecordSet_array_mssql';
00097         var $uniqueSort = true;
00098         var $leftOuter = '*=';
00099         var $rightOuter = '=*';
00100         var $ansiOuter = true; // for mssql7 or later
00101         var $poorAffectedRows = true;
00102         var $identitySQL = 'select @@IDENTITY'; // 'select SCOPE_IDENTITY'; # for mssql 2000
00103         var $uniqueOrderBy = true;
00104         var $_bindInputArray = true;
00105         
00106         function ADODB_mssql() 
00107         {               
00108                 $this->_has_mssql_init = (strnatcmp(PHP_VERSION,'4.1.0')>=0);
00109         }
00110 
00111         function ServerInfo()
00112         {
00113         global $ADODB_FETCH_MODE;
00114         
00115                 $stmt = $this->PrepareSP('sp_server_info');
00116                 $val = 2;
00117                 if ($this->fetchMode === false) {
00118                         $savem = $ADODB_FETCH_MODE;
00119                         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00120                 } else 
00121                         $savem = $this->SetFetchMode(ADODB_FETCH_NUM);
00122                 
00123                 
00124                 $this->Parameter($stmt,$val,'attribute_id');
00125                 $row = $this->GetRow($stmt);
00126                 
00127                 //$row = $this->GetRow("execute sp_server_info 2");
00128                 
00129                 
00130                 if ($this->fetchMode === false) {
00131                         $ADODB_FETCH_MODE = $savem;
00132                 } else
00133                         $this->SetFetchMode($savem);
00134                 
00135                 $arr['description'] = $row[2];
00136                 $arr['version'] = ADOConnection::_findvers($arr['description']);
00137                 return $arr;
00138         }
00139         
00140         function IfNull( $field, $ifNull ) 
00141         {
00142                 return " ISNULL($field, $ifNull) "; // if MS SQL Server
00143         }
00144         
00145         function _insertid()
00146         {
00147         // SCOPE_IDENTITY()
00148         // Returns the last IDENTITY value inserted into an IDENTITY column in 
00149         // the same scope. A scope is a module -- a stored procedure, trigger, 
00150         // function, or batch. Thus, two statements are in the same scope if 
00151         // they are in the same stored procedure, function, or batch.
00152                         return $this->GetOne($this->identitySQL);
00153         }
00154 
00155         function _affectedrows()
00156         {
00157                 return $this->GetOne('select @@rowcount');
00158         }
00159 
00160         var $_dropSeqSQL = "drop table %s";
00161         
00162         function CreateSequence($seq='adodbseq',$start=1)
00163         {
00164                 
00165                 $this->Execute('BEGIN TRANSACTION adodbseq');
00166                 $start -= 1;
00167                 $this->Execute("create table $seq (id float(53))");
00168                 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
00169                 if (!$ok) {
00170                                 $this->Execute('ROLLBACK TRANSACTION adodbseq');
00171                                 return false;
00172                 }
00173                 $this->Execute('COMMIT TRANSACTION adodbseq'); 
00174                 return true;
00175         }
00176 
00177         function GenID($seq='adodbseq',$start=1)
00178         {
00179                 //$this->debug=1;
00180                 $this->Execute('BEGIN TRANSACTION adodbseq');
00181                 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
00182                 if (!$ok) {
00183                         $this->Execute("create table $seq (id float(53))");
00184                         $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
00185                         if (!$ok) {
00186                                 $this->Execute('ROLLBACK TRANSACTION adodbseq');
00187                                 return false;
00188                         }
00189                         $this->Execute('COMMIT TRANSACTION adodbseq'); 
00190                         return $start;
00191                 }
00192                 $num = $this->GetOne("select id from $seq");
00193                 $this->Execute('COMMIT TRANSACTION adodbseq'); 
00194                 return $num;
00195                 
00196                 // in old implementation, pre 1.90, we returned GUID...
00197                 //return $this->GetOne("SELECT CONVERT(varchar(255), NEWID()) AS 'Char'");
00198         }
00199         
00200 
00201         function &SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
00202         {
00203                 if ($nrows > 0 && $offset <= 0) {
00204                         $sql = preg_replace(
00205                                 '/(^\s*select\s+(distinctrow|distinct)?)/i','\\1 '.$this->hasTop." $nrows ",$sql);
00206                         $rs =& $this->Execute($sql,$inputarr);
00207                 } else
00208                         $rs =& ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
00209         
00210                 return $rs;
00211         }
00212         
00213         
00214         // Format date column in sql string given an input format that understands Y M D
00215         function SQLDate($fmt, $col=false)
00216         {       
00217                 if (!$col) $col = $this->sysTimeStamp;
00218                 $s = '';
00219                 
00220                 $len = strlen($fmt);
00221                 for ($i=0; $i < $len; $i++) {
00222                         if ($s) $s .= '+';
00223                         $ch = $fmt[$i];
00224                         switch($ch) {
00225                         case 'Y':
00226                         case 'y':
00227                                 $s .= "datename(yyyy,$col)";
00228                                 break;
00229                         case 'M':
00230                                 $s .= "convert(char(3),$col,0)";
00231                                 break;
00232                         case 'm':
00233                                 $s .= "replace(str(month($col),2),' ','0')";
00234                                 break;
00235                         case 'Q':
00236                         case 'q':
00237                                 $s .= "datename(quarter,$col)";
00238                                 break;
00239                         case 'D':
00240                         case 'd':
00241                                 $s .= "replace(str(day($col),2),' ','0')";
00242                                 break;
00243                         case 'h':
00244                                 $s .= "substring(convert(char(14),$col,0),13,2)";
00245                                 break;
00246                         
00247                         case 'H':
00248                                 $s .= "replace(str(datepart(hh,$col),2),' ','0')";
00249                                 break;
00250                                 
00251                         case 'i':
00252                                 $s .= "replace(str(datepart(mi,$col),2),' ','0')";
00253                                 break;
00254                         case 's':
00255                                 $s .= "replace(str(datepart(ss,$col),2),' ','0')";
00256                                 break;
00257                         case 'a':
00258                         case 'A':
00259                                 $s .= "substring(convert(char(19),$col,0),18,2)";
00260                                 break;
00261                                 
00262                         default:
00263                                 if ($ch == '\\') {
00264                                         $i++;
00265                                         $ch = substr($fmt,$i,1);
00266                                 }
00267                                 $s .= $this->qstr($ch);
00268                                 break;
00269                         }
00270                 }
00271                 return $s;
00272         }
00273 
00274         
00275         function BeginTrans()
00276         {
00277                 if ($this->transOff) return true; 
00278                 $this->transCnt += 1;
00279                 $this->Execute('BEGIN TRAN');
00280                 return true;
00281         }
00282                 
00283         function CommitTrans($ok=true) 
00284         { 
00285                 if ($this->transOff) return true; 
00286                 if (!$ok) return $this->RollbackTrans();
00287                 if ($this->transCnt) $this->transCnt -= 1;
00288                 $this->Execute('COMMIT TRAN');
00289                 return true;
00290         }
00291         function RollbackTrans()
00292         {
00293                 if ($this->transOff) return true; 
00294                 if ($this->transCnt) $this->transCnt -= 1;
00295                 $this->Execute('ROLLBACK TRAN');
00296                 return true;
00297         }
00298         
00299         function SetTransactionMode( $transaction_mode ) 
00300         {
00301                 $this->_transmode  = $transaction_mode;
00302                 if (empty($transaction_mode)) {
00303                         $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
00304                         return;
00305                 }
00306                 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
00307                 $this->Execute("SET TRANSACTION ".$transaction_mode);
00308         }
00309         
00310         /*
00311                 Usage:
00312                 
00313                 $this->BeginTrans();
00314                 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
00315                 
00316                 # some operation on both tables table1 and table2
00317                 
00318                 $this->CommitTrans();
00319                 
00320                 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
00321         */
00322         function RowLock($tables,$where,$flds='top 1 null as ignore') 
00323         {
00324                 if (!$this->transCnt) $this->BeginTrans();
00325                 return $this->GetOne("select $flds from $tables with (ROWLOCK,HOLDLOCK) where $where");
00326         }
00327         
00328         
00329         function &MetaIndexes($table,$primary=false)
00330         {
00331                 $table = $this->qstr($table);
00332 
00333                 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno, 
00334                         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,
00335                         CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
00336                         FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id 
00337                         INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid 
00338                         INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
00339                         WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
00340                         ORDER BY O.name, I.Name, K.keyno";
00341 
00342                 global $ADODB_FETCH_MODE;
00343                 $save = $ADODB_FETCH_MODE;
00344         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00345         if ($this->fetchMode !== FALSE) {
00346                 $savem = $this->SetFetchMode(FALSE);
00347         }
00348         
00349         $rs = $this->Execute($sql);
00350         if (isset($savem)) {
00351                 $this->SetFetchMode($savem);
00352         }
00353         $ADODB_FETCH_MODE = $save;
00354 
00355         if (!is_object($rs)) {
00356                 return FALSE;
00357         }
00358 
00359                 $indexes = array();
00360                 while ($row = $rs->FetchRow()) {
00361                         if (!$primary && $row[5]) continue;
00362                         
00363             $indexes[$row[0]]['unique'] = $row[6];
00364             $indexes[$row[0]]['columns'][] = $row[1];
00365         }
00366         return $indexes;
00367         }
00368         
00369         function MetaForeignKeys($table, $owner=false, $upper=false)
00370         {
00371         global $ADODB_FETCH_MODE;
00372         
00373                 $save = $ADODB_FETCH_MODE;
00374                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00375                 $table = $this->qstr(strtoupper($table));
00376                 
00377                 $sql = 
00378 "select object_name(constid) as constraint_name,
00379         col_name(fkeyid, fkey) as column_name,
00380         object_name(rkeyid) as referenced_table_name,
00381         col_name(rkeyid, rkey) as referenced_column_name
00382 from sysforeignkeys
00383 where upper(object_name(fkeyid)) = $table
00384 order by constraint_name, referenced_table_name, keyno";
00385                 
00386                 $constraints =& $this->GetArray($sql);
00387                 
00388                 $ADODB_FETCH_MODE = $save;
00389                 
00390                 $arr = false;
00391                 foreach($constraints as $constr) {
00392                         //print_r($constr);
00393                         $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3]; 
00394                 }
00395                 if (!$arr) return false;
00396                 
00397                 $arr2 = false;
00398                 
00399                 foreach($arr as $k => $v) {
00400                         foreach($v as $a => $b) {
00401                                 if ($upper) $a = strtoupper($a);
00402                                 $arr2[$a] = $b;
00403                         }
00404                 }
00405                 return $arr2;
00406         }
00407 
00408         //From: Fernando Moreira <FMoreira@imediata.pt>
00409         function MetaDatabases() 
00410         { 
00411                 if(@mssql_select_db("master")) { 
00412                                  $qry=$this->metaDatabasesSQL; 
00413                                  if($rs=@mssql_query($qry)){ 
00414                                                  $tmpAr=$ar=array(); 
00415                                                  while($tmpAr=@mssql_fetch_row($rs)) 
00416                                                                  $ar[]=$tmpAr[0]; 
00417                                                 @mssql_select_db($this->database); 
00418                                                  if(sizeof($ar)) 
00419                                                                  return($ar); 
00420                                                  else 
00421                                                                  return(false); 
00422                                  } else { 
00423                                                  @mssql_select_db($this->database); 
00424                                                  return(false); 
00425                                  } 
00426                  } 
00427                  return(false); 
00428         } 
00429 
00430         // "Stein-Aksel Basma" <basma@accelero.no>
00431         // tested with MSSQL 2000
00432         function &MetaPrimaryKeys($table)
00433         {
00434         global $ADODB_FETCH_MODE;
00435         
00436                 $schema = '';
00437                 $this->_findschema($table,$schema);
00438                 if (!$schema) $schema = $this->database;
00439                 if ($schema) $schema = "and k.table_catalog like '$schema%'"; 
00440 
00441                 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
00442                 information_schema.table_constraints tc 
00443                 where tc.constraint_name = k.constraint_name and tc.constraint_type =
00444                 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
00445                 
00446                 $savem = $ADODB_FETCH_MODE;
00447                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00448                 $a = $this->GetCol($sql);
00449                 $ADODB_FETCH_MODE = $savem;
00450                 
00451                 if ($a && sizeof($a)>0) return $a;
00452                 $false = false;
00453                 return $false;    
00454         }
00455 
00456         
00457         function &MetaTables($ttype=false,$showSchema=false,$mask=false) 
00458         {
00459                 if ($mask) {
00460                         $save = $this->metaTablesSQL;
00461                         $mask = $this->qstr(($mask));
00462                         $this->metaTablesSQL .= " AND name like $mask";
00463                 }
00464                 $ret =& ADOConnection::MetaTables($ttype,$showSchema);
00465                 
00466                 if ($mask) {
00467                         $this->metaTablesSQL = $save;
00468                 }
00469                 return $ret;
00470         }
00471  
00472         function SelectDB($dbName) 
00473         {
00474                 $this->database = $dbName;
00475                 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
00476                 if ($this->_connectionID) {
00477                         return @mssql_select_db($dbName);               
00478                 }
00479                 else return false;      
00480         }
00481         
00482         function ErrorMsg() 
00483         {
00484                 if (empty($this->_errorMsg)){
00485                         $this->_errorMsg = mssql_get_last_message();
00486                 }
00487                 return $this->_errorMsg;
00488         }
00489         
00490         function ErrorNo() 
00491         {
00492                 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
00493                 if (empty($this->_errorMsg)) {
00494                         $this->_errorMsg = mssql_get_last_message();
00495                 }
00496                 $id = @mssql_query("select @@ERROR",$this->_connectionID);
00497                 if (!$id) return false;
00498                 $arr = mssql_fetch_array($id);
00499                 @mssql_free_result($id);
00500                 if (is_array($arr)) return $arr[0];
00501            else return -1;
00502         }
00503         
00504         // returns true or false
00505         function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
00506         {
00507                 if (!function_exists('mssql_pconnect')) return null;
00508                 $this->_connectionID = mssql_connect($argHostname,$argUsername,$argPassword);
00509                 if ($this->_connectionID === false) return false;
00510                 if ($argDatabasename) return $this->SelectDB($argDatabasename);
00511                 return true;    
00512         }
00513         
00514         
00515         // returns true or false
00516         function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
00517         {
00518                 if (!function_exists('mssql_pconnect')) return null;
00519                 $this->_connectionID = mssql_pconnect($argHostname,$argUsername,$argPassword);
00520                 if ($this->_connectionID === false) return false;
00521                 
00522                 // persistent connections can forget to rollback on crash, so we do it here.
00523                 if ($this->autoRollback) {
00524                         $cnt = $this->GetOne('select @@TRANCOUNT');
00525                         while (--$cnt >= 0) $this->Execute('ROLLBACK TRAN'); 
00526                 }
00527                 if ($argDatabasename) return $this->SelectDB($argDatabasename);
00528                 return true;    
00529         }
00530         
00531         function Prepare($sql)
00532         {
00533                 $sqlarr = explode('?',$sql);
00534                 if (sizeof($sqlarr) <= 1) return $sql;
00535                 $sql2 = $sqlarr[0];
00536                 for ($i = 1, $max = sizeof($sqlarr); $i < $max; $i++) {
00537                         $sql2 .=  '@P'.($i-1) . $sqlarr[$i];
00538                 } 
00539                 return array($sql,$this->qstr($sql2),$max);
00540         }
00541         
00542         function PrepareSP($sql)
00543         {
00544                 if (!$this->_has_mssql_init) {
00545                         ADOConnection::outp( "PrepareSP: mssql_init only available since PHP 4.1.0");
00546                         return $sql;
00547                 }
00548                 $stmt = mssql_init($sql,$this->_connectionID);
00549                 if (!$stmt)  return $sql;
00550                 return array($sql,$stmt);
00551         }
00552         
00553         // returns concatenated string
00554     // MSSQL requires integers to be cast as strings
00555     // automatically cast every datatype to VARCHAR(255)
00556     // @author David Rogers (introspectshun)
00557     function Concat()
00558     {
00559             $s = "";
00560             $arr = func_get_args();
00561 
00562             // Split single record on commas, if possible
00563             if (sizeof($arr) == 1) {
00564                 foreach ($arr as $arg) {
00565                     $args = explode(',', $arg);
00566                 }
00567                 $arr = $args;
00568             }
00569 
00570             array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
00571             $s = implode('+',$arr);
00572             if (sizeof($arr) > 0) return "$s";
00573             
00574                         return '';
00575     }
00576         
00577         /* 
00578         Usage:
00579                 $stmt = $db->PrepareSP('SP_RUNSOMETHING'); -- takes 2 params, @myid and @group
00580                 
00581                 # note that the parameter does not have @ in front!
00582                 $db->Parameter($stmt,$id,'myid');
00583                 $db->Parameter($stmt,$group,'group',false,64);
00584                 $db->Execute($stmt);
00585                 
00586                 @param $stmt Statement returned by Prepare() or PrepareSP().
00587                 @param $var PHP variable to bind to. Can set to null (for isNull support).
00588                 @param $name Name of stored procedure variable name to bind to.
00589                 @param [$isOutput] Indicates direction of parameter 0/false=IN  1=OUT  2= IN/OUT. This is ignored in oci8.
00590                 @param [$maxLen] Holds an maximum length of the variable.
00591                 @param [$type] The data type of $var. Legal values depend on driver.
00592                 
00593                 See mssql_bind documentation at php.net.
00594         */
00595         function Parameter(&$stmt, &$var, $name, $isOutput=false, $maxLen=4000, $type=false)
00596         {
00597                 if (!$this->_has_mssql_init) {
00598                         ADOConnection::outp( "Parameter: mssql_bind only available since PHP 4.1.0");
00599                         return false;
00600                 }
00601 
00602                 $isNull = is_null($var); // php 4.0.4 and above...
00603                         
00604                 if ($type === false) 
00605                         switch(gettype($var)) {
00606                         default:
00607                         case 'string': $type = SQLCHAR; break;
00608                         case 'double': $type = SQLFLT8; break;
00609                         case 'integer': $type = SQLINT4; break;
00610                         case 'boolean': $type = SQLINT1; break; # SQLBIT not supported in 4.1.0
00611                         }
00612                 
00613                 if  ($this->debug) {
00614                         $prefix = ($isOutput) ? 'Out' : 'In';
00615                         $ztype = (empty($type)) ? 'false' : $type;
00616                         ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);");
00617                 }
00618                 /*
00619                         See http://phplens.com/lens/lensforum/msgs.php?id=7231
00620                         
00621                         RETVAL is HARD CODED into php_mssql extension:
00622                         The return value (a long integer value) is treated like a special OUTPUT parameter, 
00623                         called "RETVAL" (without the @). See the example at mssql_execute to 
00624                         see how it works. - type: one of this new supported PHP constants. 
00625                                 SQLTEXT, SQLVARCHAR,SQLCHAR, SQLINT1,SQLINT2, SQLINT4, SQLBIT,SQLFLT8 
00626                 */
00627                 if ($name !== 'RETVAL') $name = '@'.$name;
00628                 return mssql_bind($stmt[1], $name, $var, $type, $isOutput, $isNull, $maxLen);
00629         }
00630         
00631         /* 
00632                 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
00633                 So all your blobs must be of type "image".
00634                 
00635                 Remember to set in php.ini the following...
00636                 
00637                 ; Valid range 0 - 2147483647. Default = 4096. 
00638                 mssql.textlimit = 0 ; zero to pass through 
00639 
00640                 ; Valid range 0 - 2147483647. Default = 4096. 
00641                 mssql.textsize = 0 ; zero to pass through 
00642         */
00643         function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
00644         {
00645         
00646                 if (strtoupper($blobtype) == 'CLOB') {
00647                         $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
00648                         return $this->Execute($sql) != false;
00649                 }
00650                 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
00651                 return $this->Execute($sql) != false;
00652         }
00653         
00654         // returns query ID if successful, otherwise false
00655         function _query($sql,$inputarr)
00656         {
00657                 $this->_errorMsg = false;
00658                 if (is_array($inputarr)) {
00659                         
00660                         # bind input params with sp_executesql: 
00661                         # see http://www.quest-pipelines.com/newsletter-v3/0402_F.htm
00662                         # works only with sql server 7 and newer
00663                         if (!is_array($sql)) $sql = $this->Prepare($sql);
00664                         $params = '';
00665                         $decl = '';
00666                         $i = 0;
00667                         foreach($inputarr as $v) {
00668                                 if ($decl) {
00669                                         $decl .= ', ';
00670                                         $params .= ', ';
00671                                 }       
00672                                 if (is_string($v)) {
00673                                         $len = strlen($v);
00674                                         if ($len == 0) $len = 1;
00675                                         
00676                                         if ($len > 4000 ) {
00677                                                 // NVARCHAR is max 4000 chars. Let's use NTEXT
00678                                                 $decl .= "@P$i NTEXT";
00679                                         } else {
00680                                                 $decl .= "@P$i NVARCHAR($len)";
00681                                         }
00682 
00683                                         $params .= "@P$i=N". (strncmp($v,"'",1)==0? $v : $this->qstr($v));
00684                                 } else if (is_integer($v)) {
00685                                         $decl .= "@P$i INT";
00686                                         $params .= "@P$i=".$v;
00687                                 } else if (is_float($v)) {
00688                                         $decl .= "@P$i FLOAT";
00689                                         $params .= "@P$i=".$v;
00690                                 } else if (is_bool($v)) {
00691                                         $decl .= "@P$i INT"; # Used INT just in case BIT in not supported on the user's MSSQL version. It will cast appropriately.
00692                                         $params .= "@P$i=".(($v)?'1':'0'); # True == 1 in MSSQL BIT fields and acceptable for storing logical true in an int field
00693                                 } else {
00694                                         $decl .= "@P$i CHAR"; # Used char because a type is required even when the value is to be NULL.
00695                                         $params .= "@P$i=NULL";
00696                                         }
00697                                 $i += 1;
00698                         }
00699                         $decl = $this->qstr($decl);
00700                         if ($this->debug) ADOConnection::outp("<font size=-1>sp_executesql N{$sql[1]},N$decl,$params</font>");
00701                         $rez = mssql_query("sp_executesql N{$sql[1]},N$decl,$params");
00702                         
00703                 } else if (is_array($sql)) {
00704                         # PrepareSP()
00705                         $rez = mssql_execute($sql[1]);
00706                         
00707                 } else {
00708                         $rez = mssql_query($sql,$this->_connectionID);
00709                 }
00710                 return $rez;
00711         }
00712         
00713         // returns true or false
00714         function _close()
00715         { 
00716                 if ($this->transCnt) $this->RollbackTrans();
00717                 $rez = @mssql_close($this->_connectionID);
00718                 $this->_connectionID = false;
00719                 return $rez;
00720         }
00721         
00722         // mssql uses a default date like Dec 30 2000 12:00AM
00723         function UnixDate($v)
00724         {
00725                 return ADORecordSet_array_mssql::UnixDate($v);
00726         }
00727         
00728         function UnixTimeStamp($v)
00729         {
00730                 return ADORecordSet_array_mssql::UnixTimeStamp($v);
00731         }       
00732 }
00733         
00734 /*--------------------------------------------------------------------------------------
00735          Class Name: Recordset
00736 --------------------------------------------------------------------------------------*/
00737 
00738 class ADORecordset_mssql extends ADORecordSet { 
00739 
00740         var $databaseType = "mssql";
00741         var $canSeek = true;
00742         var $hasFetchAssoc; // see http://phplens.com/lens/lensforum/msgs.php?id=6083
00743         // _mths works only in non-localised system
00744         
00745         function ADORecordset_mssql($id,$mode=false)
00746         {
00747                 // freedts check...
00748                 $this->hasFetchAssoc = function_exists('mssql_fetch_assoc');
00749 
00750                 if ($mode === false) { 
00751                         global $ADODB_FETCH_MODE;
00752                         $mode = $ADODB_FETCH_MODE;
00753 
00754                 }
00755                 $this->fetchMode = $mode;
00756                 return $this->ADORecordSet($id,$mode);
00757         }
00758         
00759         
00760         function _initrs()
00761         {
00762         GLOBAL $ADODB_COUNTRECS;        
00763                 $this->_numOfRows = ($ADODB_COUNTRECS)? @mssql_num_rows($this->_queryID):-1;
00764                 $this->_numOfFields = @mssql_num_fields($this->_queryID);
00765         }
00766         
00767 
00768         //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
00769         // get next resultset - requires PHP 4.0.5 or later
00770         function NextRecordSet()
00771         {
00772                 if (!mssql_next_result($this->_queryID)) return false;
00773                 $this->_inited = false;
00774                 $this->bind = false;
00775                 $this->_currentRow = -1;
00776                 $this->Init();
00777                 return true;
00778         }
00779 
00780         /* Use associative array to get fields array */
00781         function Fields($colname)
00782         {
00783                 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
00784                 if (!$this->bind) {
00785                         $this->bind = array();
00786                         for ($i=0; $i < $this->_numOfFields; $i++) {
00787                                 $o = $this->FetchField($i);
00788                                 $this->bind[strtoupper($o->name)] = $i;
00789                         }
00790                 }
00791                 
00792                  return $this->fields[$this->bind[strtoupper($colname)]];
00793         }
00794         
00795         /*      Returns: an object containing field information. 
00796                 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
00797                 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
00798                 fetchField() is retrieved.      */
00799 
00800         function &FetchField($fieldOffset = -1) 
00801         {
00802                 if ($fieldOffset != -1) {
00803                         $f = @mssql_fetch_field($this->_queryID, $fieldOffset);
00804                 }
00805                 else if ($fieldOffset == -1) {  /*      The $fieldOffset argument is not provided thus its -1   */
00806                         $f = @mssql_fetch_field($this->_queryID);
00807                 }
00808                 $false = false;
00809                 if (empty($f)) return $false;
00810                 return $f;
00811         }
00812         
00813         function _seek($row) 
00814         {
00815                 return @mssql_data_seek($this->_queryID, $row);
00816         }
00817 
00818         // speedup
00819         function MoveNext() 
00820         {
00821                 if ($this->EOF) return false;
00822                 
00823                 $this->_currentRow++;
00824                 
00825                 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
00826                         if ($this->fetchMode & ADODB_FETCH_NUM) {
00827                                 //ADODB_FETCH_BOTH mode
00828                                 $this->fields = @mssql_fetch_array($this->_queryID);
00829                         }
00830                         else {
00831                                 if ($this->hasFetchAssoc) {// only for PHP 4.2.0 or later
00832                                          $this->fields = @mssql_fetch_assoc($this->_queryID);
00833                                 } else {
00834                                         $flds = @mssql_fetch_array($this->_queryID);
00835                                         if (is_array($flds)) {
00836                                                 $fassoc = array();
00837                                                 foreach($flds as $k => $v) {
00838                                                         if (is_numeric($k)) continue;
00839                                                         $fassoc[$k] = $v;
00840                                                 }
00841                                                 $this->fields = $fassoc;
00842                                         } else
00843                                                 $this->fields = false;
00844                                 }
00845                         }
00846                         
00847                         if (is_array($this->fields)) {
00848                                 if (ADODB_ASSOC_CASE == 0) {
00849                                         foreach($this->fields as $k=>$v) {
00850                                                 $this->fields[strtolower($k)] = $v;
00851                                         }
00852                                 } else if (ADODB_ASSOC_CASE == 1) {
00853                                         foreach($this->fields as $k=>$v) {
00854                                                 $this->fields[strtoupper($k)] = $v;
00855                                         }
00856                                 }
00857                         }
00858                 } else {
00859                         $this->fields = @mssql_fetch_row($this->_queryID);
00860                 }
00861                 if ($this->fields) return true;
00862                 $this->EOF = true;
00863                 
00864                 return false;
00865         }
00866 
00867         
00868         // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
00869         // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
00870         function _fetch($ignore_fields=false) 
00871         {
00872                 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
00873                         if ($this->fetchMode & ADODB_FETCH_NUM) {
00874                                 //ADODB_FETCH_BOTH mode
00875                                 $this->fields = @mssql_fetch_array($this->_queryID);
00876                         } else {
00877                                 if ($this->hasFetchAssoc) // only for PHP 4.2.0 or later
00878                                         $this->fields = @mssql_fetch_assoc($this->_queryID);
00879                                 else {
00880                                         $this->fields = @mssql_fetch_array($this->_queryID);
00881                                         if (@is_array($$this->fields)) {
00882                                                 $fassoc = array();
00883                                                 foreach($$this->fields as $k => $v) {
00884                                                         if (is_integer($k)) continue;
00885                                                         $fassoc[$k] = $v;
00886                                                 }
00887                                                 $this->fields = $fassoc;
00888                                         }
00889                                 }
00890                         }
00891                         
00892                         if (!$this->fields) {
00893                         } else if (ADODB_ASSOC_CASE == 0) {
00894                                 foreach($this->fields as $k=>$v) {
00895                                         $this->fields[strtolower($k)] = $v;
00896                                 }
00897                         } else if (ADODB_ASSOC_CASE == 1) {
00898                                 foreach($this->fields as $k=>$v) {
00899                                         $this->fields[strtoupper($k)] = $v;
00900                                 }
00901                         }
00902                 } else {
00903                         $this->fields = @mssql_fetch_row($this->_queryID);
00904                 }
00905                 return $this->fields;
00906         }
00907         
00908         /*      close() only needs to be called if you are worried about using too much memory while your script
00909                 is running. All associated result memory for the specified result identifier will automatically be freed.       */
00910 
00911         function _close() 
00912         {
00913                 $rez = mssql_free_result($this->_queryID);      
00914                 $this->_queryID = false;
00915                 return $rez;
00916         }
00917         // mssql uses a default date like Dec 30 2000 12:00AM
00918         function UnixDate($v)
00919         {
00920                 return ADORecordSet_array_mssql::UnixDate($v);
00921         }
00922         
00923         function UnixTimeStamp($v)
00924         {
00925                 return ADORecordSet_array_mssql::UnixTimeStamp($v);
00926         }
00927         
00928 }
00929 
00930 
00931 class ADORecordSet_array_mssql extends ADORecordSet_array {
00932         function ADORecordSet_array_mssql($id=-1,$mode=false) 
00933         {
00934                 $this->ADORecordSet_array($id,$mode);
00935         }
00936         
00937                 // mssql uses a default date like Dec 30 2000 12:00AM
00938         function UnixDate($v)
00939         {
00940         
00941                 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
00942                 
00943         global $ADODB_mssql_mths,$ADODB_mssql_date_order;
00944         
00945                 //Dec 30 2000 12:00AM 
00946                 if ($ADODB_mssql_date_order == 'dmy') {
00947                         if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
00948                                 return parent::UnixDate($v);
00949                         }
00950                         if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
00951                         
00952                         $theday = $rr[1];
00953                         $themth =  substr(strtoupper($rr[2]),0,3);
00954                 } else {
00955                         if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
00956                                 return parent::UnixDate($v);
00957                         }
00958                         if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
00959                         
00960                         $theday = $rr[2];
00961                         $themth = substr(strtoupper($rr[1]),0,3);
00962                 }
00963                 $themth = $ADODB_mssql_mths[$themth];
00964                 if ($themth <= 0) return false;
00965                 // h-m-s-MM-DD-YY
00966                 return  mktime(0,0,0,$themth,$theday,$rr[3]);
00967         }
00968         
00969         function UnixTimeStamp($v)
00970         {
00971         
00972                 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
00973                 
00974         global $ADODB_mssql_mths,$ADODB_mssql_date_order;
00975         
00976                 //Dec 30 2000 12:00AM
00977                  if ($ADODB_mssql_date_order == 'dmy') {
00978                          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})|"
00979                         ,$v, $rr)) return parent::UnixTimeStamp($v);
00980                         if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
00981                 
00982                         $theday = $rr[1];
00983                         $themth =  substr(strtoupper($rr[2]),0,3);
00984                 } else {
00985                         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})|"
00986                         ,$v, $rr)) return parent::UnixTimeStamp($v);
00987                         if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
00988                 
00989                         $theday = $rr[2];
00990                         $themth = substr(strtoupper($rr[1]),0,3);
00991                 }
00992                 
00993                 $themth = $ADODB_mssql_mths[$themth];
00994                 if ($themth <= 0) return false;
00995                 
00996                 switch (strtoupper($rr[6])) {
00997                 case 'P':
00998                         if ($rr[4]<12) $rr[4] += 12;
00999                         break;
01000                 case 'A':
01001                         if ($rr[4]==12) $rr[4] = 0;
01002                         break;
01003                 default:
01004                         break;
01005                 }
01006                 // h-m-s-MM-DD-YY
01007                 return  mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
01008         }
01009 }
01010 
01011 /*
01012 Code Example 1:
01013 
01014 select  object_name(constid) as constraint_name,
01015         object_name(fkeyid) as table_name, 
01016         col_name(fkeyid, fkey) as column_name,
01017         object_name(rkeyid) as referenced_table_name,
01018         col_name(rkeyid, rkey) as referenced_column_name
01019 from sysforeignkeys
01020 where object_name(fkeyid) = x
01021 order by constraint_name, table_name, referenced_table_name,  keyno
01022 
01023 Code Example 2:
01024 select  constraint_name,
01025         column_name,
01026         ordinal_position
01027 from information_schema.key_column_usage
01028 where constraint_catalog = db_name()
01029 and table_name = x
01030 order by constraint_name, ordinal_position
01031 
01032 http://www.databasejournal.com/scripts/article.php/1440551
01033 */
01034 
01035 ?>