Documentation TYPO3 par Ameos

adodb-mssql.inc.php

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


Généré par Les spécialistes TYPO3 avec  doxygen 1.4.6