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

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




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.
Erzeugt am Mon Feb 3 2014 14:29:53 für MyOOS [Die Shop-Lösung] von   doxygen 1.8.4