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   MSSQL support via ODBC. Requires ODBC. Works on Windows and Unix. 
00012   For Unix configuration, see http://phpbuilder.com/columns/alberto20000919.php3
00013 */
00014 
00015 // security - hide paths
00016 if (!defined('ADODB_DIR')) die();
00017 
00018 if (!defined('_ADODB_ODBC_LAYER')) {
00019         include(ADODB_DIR."/drivers/adodb-odbc.inc.php");
00020 }
00021 
00022  
00023 class  ADODB_odbc_mssql extends ADODB_odbc {    
00024         var $databaseType = 'odbc_mssql';
00025         var $fmtDate = "'Y-m-d'";
00026         var $fmtTimeStamp = "'Y-m-d H:i:s'";
00027         var $_bindInputArray = true;
00028         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'))";
00029         var $metaColumnsSQL = "select c.name,t.name,c.length from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'";
00030         var $hasTop = 'top';            // support mssql/interbase SELECT TOP 10 * FROM TABLE
00031         var $sysDate = 'GetDate()';
00032         var $sysTimeStamp = 'GetDate()';
00033         var $leftOuter = '*=';
00034         var $rightOuter = '=*';
00035         var $substr = 'substring';
00036         var $length = 'len';
00037         var $ansiOuter = true; // for mssql7 or later
00038         var $identitySQL = 'select @@IDENTITY'; // 'select SCOPE_IDENTITY'; # for mssql 2000
00039         var $hasInsertID = true;
00040         var $connectStmt = 'SET CONCAT_NULL_YIELDS_NULL OFF'; # When SET CONCAT_NULL_YIELDS_NULL is ON, 
00041                                                                                                                   # concatenating a null value with a string yields a NULL result
00042         
00043         function ADODB_odbc_mssql()
00044         {
00045                 $this->ADODB_odbc();
00046                 //$this->curmode = SQL_CUR_USE_ODBC;    
00047         }
00048 
00049         // crashes php...
00050         function ServerInfo()
00051         {
00052         global $ADODB_FETCH_MODE;
00053                 $save = $ADODB_FETCH_MODE;
00054                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00055                 $row = $this->GetRow("execute sp_server_info 2");
00056                 $ADODB_FETCH_MODE = $save;
00057                 if (!is_array($row)) return false;
00058                 $arr['description'] = $row[2];
00059                 $arr['version'] = ADOConnection::_findvers($arr['description']);
00060                 return $arr;
00061         }
00062 
00063         function IfNull( $field, $ifNull ) 
00064         {
00065                 return " ISNULL($field, $ifNull) "; // if MS SQL Server
00066         }
00067         
00068         function _insertid()
00069         {
00070         // SCOPE_IDENTITY()
00071         // Returns the last IDENTITY value inserted into an IDENTITY column in 
00072         // the same scope. A scope is a module -- a stored procedure, trigger, 
00073         // function, or batch. Thus, two statements are in the same scope if 
00074         // they are in the same stored procedure, function, or batch.
00075                         return $this->GetOne($this->identitySQL);
00076         }
00077         
00078         
00079         function MetaForeignKeys($table, $owner=false, $upper=false)
00080         {
00081         global $ADODB_FETCH_MODE;
00082         
00083                 $save = $ADODB_FETCH_MODE;
00084                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00085                 $table = $this->qstr(strtoupper($table));
00086                 
00087                 $sql = 
00088 "select object_name(constid) as constraint_name,
00089         col_name(fkeyid, fkey) as column_name,
00090         object_name(rkeyid) as referenced_table_name,
00091         col_name(rkeyid, rkey) as referenced_column_name
00092 from sysforeignkeys
00093 where upper(object_name(fkeyid)) = $table
00094 order by constraint_name, referenced_table_name, keyno";
00095                 
00096                 $constraints =& $this->GetArray($sql);
00097                 
00098                 $ADODB_FETCH_MODE = $save;
00099                 
00100                 $arr = false;
00101                 foreach($constraints as $constr) {
00102                         //print_r($constr);
00103                         $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3]; 
00104                 }
00105                 if (!$arr) return false;
00106                 
00107                 $arr2 = false;
00108                 
00109                 foreach($arr as $k => $v) {
00110                         foreach($v as $a => $b) {
00111                                 if ($upper) $a = strtoupper($a);
00112                                 $arr2[$a] = $b;
00113                         }
00114                 }
00115                 return $arr2;
00116         }
00117         
00118         function &MetaTables($ttype=false,$showSchema=false,$mask=false) 
00119         {
00120                 if ($mask) {$this->debug=1;
00121                         $save = $this->metaTablesSQL;
00122                         $mask = $this->qstr($mask);
00123                         $this->metaTablesSQL .= " AND name like $mask";
00124                 }
00125                 $ret =& ADOConnection::MetaTables($ttype,$showSchema);
00126                 
00127                 if ($mask) {
00128                         $this->metaTablesSQL = $save;
00129                 }
00130                 return $ret;
00131         }
00132         
00133         function &MetaColumns($table)
00134         {
00135                 $arr = ADOConnection::MetaColumns($table);
00136                 return $arr;
00137         }
00138         
00139         function _query($sql,$inputarr)
00140         {
00141                 if (is_string($sql)) $sql = str_replace('||','+',$sql);
00142                 return ADODB_odbc::_query($sql,$inputarr);
00143         }
00144         
00145         function SetTransactionMode( $transaction_mode ) 
00146         {
00147                 $this->_transmode  = $transaction_mode;
00148                 if (empty($transaction_mode)) {
00149                         $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
00150                         return;
00151                 }
00152                 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
00153                 $this->Execute("SET TRANSACTION ".$transaction_mode);
00154         }
00155         
00156         // "Stein-Aksel Basma" <basma@accelero.no>
00157         // tested with MSSQL 2000
00158         function &MetaPrimaryKeys($table)
00159         {
00160         global $ADODB_FETCH_MODE;
00161         
00162                 $schema = '';
00163                 $this->_findschema($table,$schema);
00164                 //if (!$schema) $schema = $this->database;
00165                 if ($schema) $schema = "and k.table_catalog like '$schema%'"; 
00166         
00167                 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
00168                 information_schema.table_constraints tc 
00169                 where tc.constraint_name = k.constraint_name and tc.constraint_type =
00170                 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
00171                 
00172                 $savem = $ADODB_FETCH_MODE;
00173                 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
00174                 $a = $this->GetCol($sql);
00175                 $ADODB_FETCH_MODE = $savem;
00176                 
00177                 if ($a && sizeof($a)>0) return $a;
00178                 $false = false;
00179                 return $false;    
00180         }
00181         
00182         function &SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
00183         {
00184                 if ($nrows > 0 && $offset <= 0) {
00185                         $sql = preg_replace(
00186                                 '/(^\s*select\s+(distinctrow|distinct)?)/i','\\1 '.$this->hasTop." $nrows ",$sql);
00187                         $rs =& $this->Execute($sql,$inputarr);
00188                 } else
00189                         $rs =& ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
00190                         
00191                 return $rs;
00192         }
00193         
00194         // Format date column in sql string given an input format that understands Y M D
00195         function SQLDate($fmt, $col=false)
00196         {       
00197                 if (!$col) $col = $this->sysTimeStamp;
00198                 $s = '';
00199                 
00200                 $len = strlen($fmt);
00201                 for ($i=0; $i < $len; $i++) {
00202                         if ($s) $s .= '+';
00203                         $ch = $fmt[$i];
00204                         switch($ch) {
00205                         case 'Y':
00206                         case 'y':
00207                                 $s .= "datename(yyyy,$col)";
00208                                 break;
00209                         case 'M':
00210                                 $s .= "convert(char(3),$col,0)";
00211                                 break;
00212                         case 'm':
00213                                 $s .= "replace(str(month($col),2),' ','0')";
00214                                 break;
00215                         case 'Q':
00216                         case 'q':
00217                                 $s .= "datename(quarter,$col)";
00218                                 break;
00219                         case 'D':
00220                         case 'd':
00221                                 $s .= "replace(str(day($col),2),' ','0')";
00222                                 break;
00223                         case 'h':
00224                                 $s .= "substring(convert(char(14),$col,0),13,2)";
00225                                 break;
00226                         
00227                         case 'H':
00228                                 $s .= "replace(str(datepart(hh,$col),2),' ','0')";
00229                                 break;
00230                                 
00231                         case 'i':
00232                                 $s .= "replace(str(datepart(mi,$col),2),' ','0')";
00233                                 break;
00234                         case 's':
00235                                 $s .= "replace(str(datepart(ss,$col),2),' ','0')";
00236                                 break;
00237                         case 'a':
00238                         case 'A':
00239                                 $s .= "substring(convert(char(19),$col,0),18,2)";
00240                                 break;
00241                                 
00242                         default:
00243                                 if ($ch == '\\') {
00244                                         $i++;
00245                                         $ch = substr($fmt,$i,1);
00246                                 }
00247                                 $s .= $this->qstr($ch);
00248                                 break;
00249                         }
00250                 }
00251                 return $s;
00252         }
00253 
00254 } 
00255  
00256 class  ADORecordSet_odbc_mssql extends ADORecordSet_odbc {      
00257         
00258         var $databaseType = 'odbc_mssql';
00259         
00260         function ADORecordSet_odbc_mssql($id,$mode=false)
00261         {
00262                 return $this->ADORecordSet_odbc($id,$mode);
00263         }       
00264 }
00265 ?>
adodb-odbc_mssql.inc.php
