C:/lib/adodb/drivers/adodb-odbc_mssql.inc.php Quellcode

adodb-odbc_mssql.inc.php
gehe zur Dokumentation dieser Datei
1 <?php
2 /*
3 V5.19dev ??-???-2014 (c) 2000-2014 John Lim (jlim#natsoft.com). All rights reserved.
4  Released under both BSD license and Lesser GPL library license.
5  Whenever there is any discrepancy between the two licenses,
6  the BSD license will take precedence.
7 Set tabs to 4 for best viewing.
8 
9  Latest version is available at http://adodb.sourceforge.net
10 
11  MSSQL support via ODBC. Requires ODBC. Works on Windows and Unix.
12  For Unix configuration, see http://phpbuilder.com/columns/alberto20000919.php3
13 */
14 
15 // security - hide paths
16 if (!defined('ADODB_DIR')) die();
17 
18 if (!defined('_ADODB_ODBC_LAYER')) {
19  include(ADODB_DIR."/drivers/adodb-odbc.inc.php");
20 }
21 
22 
24  var $databaseType = 'odbc_mssql';
25  var $fmtDate = "'Y-m-d'";
26  var $fmtTimeStamp = "'Y-m-d H:i:s'";
27  var $_bindInputArray = true;
28  var $metaDatabasesSQL = "select name from sysdatabases where name <> 'master'";
29  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'))";
30  var $metaColumnsSQL = # xtype==61 is datetime
31  "select c.name,t.name,c.length,c.isnullable, c.status,
32  (case when c.xusertype=61 then 0 else c.xprec end),
33  (case when c.xusertype=61 then 0 else c.xscale end)
34  from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'";
35  var $hasTop = 'top'; // support mssql/interbase SELECT TOP 10 * FROM TABLE
36  var $sysDate = 'GetDate()';
37  var $sysTimeStamp = 'GetDate()';
38  var $leftOuter = '*=';
39  var $rightOuter = '=*';
40  var $substr = 'substring';
41  var $length = 'len';
42  var $ansiOuter = true; // for mssql7 or later
43  var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000
44  var $hasInsertID = true;
45  var $connectStmt = 'SET CONCAT_NULL_YIELDS_NULL OFF'; # When SET CONCAT_NULL_YIELDS_NULL is ON,
46  # concatenating a null value with a string yields a NULL result
47 
48  function ADODB_odbc_mssql()
49  {
50  $this->ADODB_odbc();
51  //$this->curmode = SQL_CUR_USE_ODBC;
52  }
53 
54  // crashes php...
55  function ServerInfo()
56  {
57  global $ADODB_FETCH_MODE;
58  $save = $ADODB_FETCH_MODE;
59  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
60  $row = $this->GetRow("execute sp_server_info 2");
61  $ADODB_FETCH_MODE = $save;
62  if (!is_array($row)) return false;
63  $arr['description'] = $row[2];
64  $arr['version'] = ADOConnection::_findvers($arr['description']);
65  return $arr;
66  }
67 
68  function IfNull( $field, $ifNull )
69  {
70  return " ISNULL($field, $ifNull) "; // if MS SQL Server
71  }
72 
73  function _insertid()
74  {
75  // SCOPE_IDENTITY()
76  // Returns the last IDENTITY value inserted into an IDENTITY column in
77  // the same scope. A scope is a module -- a stored procedure, trigger,
78  // function, or batch. Thus, two statements are in the same scope if
79  // they are in the same stored procedure, function, or batch.
80  return $this->GetOne($this->identitySQL);
81  }
82 
83 
84  function MetaForeignKeys($table, $owner=false, $upper=false)
85  {
86  global $ADODB_FETCH_MODE;
87 
88  $save = $ADODB_FETCH_MODE;
89  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
90  $table = $this->qstr(strtoupper($table));
91 
92  $sql =
93 "select object_name(constid) as constraint_name,
94  col_name(fkeyid, fkey) as column_name,
95  object_name(rkeyid) as referenced_table_name,
96  col_name(rkeyid, rkey) as referenced_column_name
97 from sysforeignkeys
98 where upper(object_name(fkeyid)) = $table
99 order by constraint_name, referenced_table_name, keyno";
100 
101  $constraints = $this->GetArray($sql);
102 
103  $ADODB_FETCH_MODE = $save;
104 
105  $arr = false;
106  foreach($constraints as $constr) {
107  //print_r($constr);
108  $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
109  }
110  if (!$arr) return false;
111 
112  $arr2 = false;
113 
114  foreach($arr as $k => $v) {
115  foreach($v as $a => $b) {
116  if ($upper) $a = strtoupper($a);
117  $arr2[$a] = $b;
118  }
119  }
120  return $arr2;
121  }
122 
123  function MetaTables($ttype=false,$showSchema=false,$mask=false)
124  {
125  if ($mask) {//$this->debug=1;
126  $save = $this->metaTablesSQL;
127  $mask = $this->qstr($mask);
128  $this->metaTablesSQL .= " AND name like $mask";
129  }
130  $ret = ADOConnection::MetaTables($ttype,$showSchema);
131 
132  if ($mask) {
133  $this->metaTablesSQL = $save;
134  }
135  return $ret;
136  }
137 
138  function MetaColumns($table, $normalize=true)
139  {
140 
141  $this->_findschema($table,$schema);
142  if ($schema) {
143  $dbName = $this->database;
144  $this->SelectDB($schema);
145  }
146  global $ADODB_FETCH_MODE;
147  $save = $ADODB_FETCH_MODE;
148  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
149 
150  if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
151  $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table));
152 
153  if ($schema) {
154  $this->SelectDB($dbName);
155  }
156 
157  if (isset($savem)) $this->SetFetchMode($savem);
158  $ADODB_FETCH_MODE = $save;
159  if (!is_object($rs)) {
160  $false = false;
161  return $false;
162  }
163 
164  $retarr = array();
165  while (!$rs->EOF){
166  $fld = new ADOFieldObject();
167  $fld->name = $rs->fields[0];
168  $fld->type = $rs->fields[1];
169 
170  $fld->not_null = (!$rs->fields[3]);
171  $fld->auto_increment = ($rs->fields[4] == 128); // sys.syscolumns status field. 0x80 = 128 ref: http://msdn.microsoft.com/en-us/library/ms186816.aspx
172 
173 
174  if (isset($rs->fields[5]) && $rs->fields[5]) {
175  if ($rs->fields[5]>0) $fld->max_length = $rs->fields[5];
176  $fld->scale = $rs->fields[6];
177  if ($fld->scale>0) $fld->max_length += 1;
178  } else
179  $fld->max_length = $rs->fields[2];
180 
181 
182  if ($save == ADODB_FETCH_NUM) {
183  $retarr[] = $fld;
184  } else {
185  $retarr[strtoupper($fld->name)] = $fld;
186  }
187  $rs->MoveNext();
188  }
189 
190  $rs->Close();
191  return $retarr;
192 
193  }
194 
195 
196  function MetaIndexes($table,$primary=false, $owner=false)
197  {
198  $table = $this->qstr($table);
199 
200  $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
201  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,
202  CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
203  FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
204  INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
205  INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
206  WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
207  ORDER BY O.name, I.Name, K.keyno";
208 
209  global $ADODB_FETCH_MODE;
210  $save = $ADODB_FETCH_MODE;
211  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
212  if ($this->fetchMode !== FALSE) {
213  $savem = $this->SetFetchMode(FALSE);
214  }
215 
216  $rs = $this->Execute($sql);
217  if (isset($savem)) {
218  $this->SetFetchMode($savem);
219  }
220  $ADODB_FETCH_MODE = $save;
221 
222  if (!is_object($rs)) {
223  return FALSE;
224  }
225 
226  $indexes = array();
227  while ($row = $rs->FetchRow()) {
228  if (!$primary && $row[5]) continue;
229 
230  $indexes[$row[0]]['unique'] = $row[6];
231  $indexes[$row[0]]['columns'][] = $row[1];
232  }
233  return $indexes;
234  }
235 
236  function _query($sql,$inputarr=false)
237  {
238  if (is_string($sql)) $sql = str_replace('||','+',$sql);
239  return ADODB_odbc::_query($sql,$inputarr);
240  }
241 
242  function SetTransactionMode( $transaction_mode )
243  {
244  $this->_transmode = $transaction_mode;
245  if (empty($transaction_mode)) {
246  $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
247  return;
248  }
249  if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
250  $this->Execute("SET TRANSACTION ".$transaction_mode);
251  }
252 
253  // "Stein-Aksel Basma" <basma@accelero.no>
254  // tested with MSSQL 2000
255  function MetaPrimaryKeys($table)
256  {
257  global $ADODB_FETCH_MODE;
258 
259  $schema = '';
260  $this->_findschema($table,$schema);
261  //if (!$schema) $schema = $this->database;
262  if ($schema) $schema = "and k.table_catalog like '$schema%'";
263 
264  $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
265  information_schema.table_constraints tc
266  where tc.constraint_name = k.constraint_name and tc.constraint_type =
267  'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
268 
269  $savem = $ADODB_FETCH_MODE;
270  $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
271  $a = $this->GetCol($sql);
272  $ADODB_FETCH_MODE = $savem;
273 
274  if ($a && sizeof($a)>0) return $a;
275  $false = false;
276  return $false;
277  }
278 
279  function SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
280  {
281  if ($nrows > 0 && $offset <= 0) {
282  $sql = preg_replace(
283  '/(^\s*select\s+(distinctrow|distinct)?)/i','\\1 '.$this->hasTop." $nrows ",$sql);
284  $rs = $this->Execute($sql,$inputarr);
285  } else
286  $rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
287 
288  return $rs;
289  }
290 
291  // Format date column in sql string given an input format that understands Y M D
292  function SQLDate($fmt, $col=false)
293  {
294  if (!$col) $col = $this->sysTimeStamp;
295  $s = '';
296 
297  $len = strlen($fmt);
298  for ($i=0; $i < $len; $i++) {
299  if ($s) $s .= '+';
300  $ch = $fmt[$i];
301  switch($ch) {
302  case 'Y':
303  case 'y':
304  $s .= "datename(yyyy,$col)";
305  break;
306  case 'M':
307  $s .= "convert(char(3),$col,0)";
308  break;
309  case 'm':
310  $s .= "replace(str(month($col),2),' ','0')";
311  break;
312  case 'Q':
313  case 'q':
314  $s .= "datename(quarter,$col)";
315  break;
316  case 'D':
317  case 'd':
318  $s .= "replace(str(day($col),2),' ','0')";
319  break;
320  case 'h':
321  $s .= "substring(convert(char(14),$col,0),13,2)";
322  break;
323 
324  case 'H':
325  $s .= "replace(str(datepart(hh,$col),2),' ','0')";
326  break;
327 
328  case 'i':
329  $s .= "replace(str(datepart(mi,$col),2),' ','0')";
330  break;
331  case 's':
332  $s .= "replace(str(datepart(ss,$col),2),' ','0')";
333  break;
334  case 'a':
335  case 'A':
336  $s .= "substring(convert(char(19),$col,0),18,2)";
337  break;
338 
339  default:
340  if ($ch == '\\') {
341  $i++;
342  $ch = substr($fmt,$i,1);
343  }
344  $s .= $this->qstr($ch);
345  break;
346  }
347  }
348  return $s;
349  }
350 
351 }
352 
354 
355  var $databaseType = 'odbc_mssql';
356 
357  function ADORecordSet_odbc_mssql($id,$mode=false)
358  {
359  return $this->ADORecordSet_odbc($id,$mode);
360  }
361 }
362 ?>




Korrekturen, Hinweise und Ergänzungen

Bitte scheuen Sie sich nicht und melden Sie, was auf dieser Seite sachlich falsch oder irreführend ist, was ergänzt werden sollte, was fehlt usw. Dazu bitte oben aus dem Menü Seite den Eintrag Support Forum wählen. Es ist eine kostenlose Anmeldung erforderlich, um Anmerkungen zu posten. Unpassende Postings, Spam usw. werden kommentarlos entfernt.