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

adodb-mssqlnative.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  http://www.microsoft.com/sql/technologies/php/default.mspx
13  To configure for Unix, see
14  http://phpbuilder.com/columns/alberto20000919.php3
15 
16  $stream = sqlsrv_get_field($stmt, $index, SQLSRV_SQLTYPE_STREAM(SQLSRV_ENC_BINARY));
17  stream_filter_append($stream, "convert.iconv.ucs-2/utf-8"); // Voila, UTF-8 can be read directly from $stream
18 
19 */
20 
21 // security - hide paths
22 if (!defined('ADODB_DIR')) die();
23 
24 if (!function_exists('sqlsrv_configure')) {
25  die("mssqlnative extension not installed");
26 }
27 
28 if (!function_exists('sqlsrv_set_error_handling')) {
29  function sqlsrv_set_error_handling($constant) {
30  sqlsrv_configure("WarningsReturnAsErrors", $constant);
31  }
32 }
33 if (!function_exists('sqlsrv_log_set_severity')) {
34  function sqlsrv_log_set_severity($constant) {
35  sqlsrv_configure("LogSeverity", $constant);
36  }
37 }
38 if (!function_exists('sqlsrv_log_set_subsystems')) {
39  function sqlsrv_log_set_subsystems($constant) {
40  sqlsrv_configure("LogSubsystems", $constant);
41  }
42 }
43 
44 
45 //----------------------------------------------------------------
46 // MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002
47 // and this causes tons of problems because localized versions of
48 // MSSQL will return the dates in dmy or mdy order; and also the
49 // month strings depends on what language has been configured. The
50 // following two variables allow you to control the localization
51 // settings - Ugh.
52 //
53 // MORE LOCALIZATION INFO
54 // ----------------------
55 // To configure datetime, look for and modify sqlcommn.loc,
56 // typically found in c:\mssql\install
57 // Also read :
58 // http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918
59 // Alternatively use:
60 // CONVERT(char(12),datecol,120)
61 //
62 // Also if your month is showing as month-1,
63 // e.g. Jan 13, 2002 is showing as 13/0/2002, then see
64 // http://phplens.com/lens/lensforum/msgs.php?id=7048&x=1
65 // it's a localisation problem.
66 //----------------------------------------------------------------
67 
68 
69 // has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc
70 if (ADODB_PHPVER >= 0x4300) {
71 // docs say 4.2.0, but testing shows only since 4.3.0 does it work!
72  ini_set('mssql.datetimeconvert',0);
73 } else {
74  global $ADODB_mssql_mths; // array, months must be upper-case
75  $ADODB_mssql_date_order = 'mdy';
76  $ADODB_mssql_mths = array(
77  'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6,
78  'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12);
79 }
80 
81 class ADODB_mssqlnative extends ADOConnection {
82  var $databaseType = "mssqlnative";
83  var $dataProvider = "mssqlnative";
84  var $replaceQuote = "''"; // string to use to replace quotes
85  var $fmtDate = "'Y-m-d'";
86  var $fmtTimeStamp = "'Y-m-d H:i:s'";
87  var $hasInsertID = true;
88  var $substr = "substring";
89  var $length = 'len';
90  var $hasAffectedRows = true;
91  var $poorAffectedRows = false;
92  var $metaDatabasesSQL = "select name from sys.sysdatabases where name <> 'master'";
93  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'))";
94  var $metaColumnsSQL =
95  "select c.name,
96  t.name as type,
97  c.length,
98  c.xprec as precision,
99  c.xscale as scale,
100  c.isnullable as nullable,
101  c.cdefault as default_value,
102  c.xtype,
103  t.length as type_length,
104  sc.is_identity
105  from syscolumns c
106  join systypes t on t.xusertype=c.xusertype
107  join sysobjects o on o.id=c.id
108  join sys.tables st on st.name=o.name
109  join sys.columns sc on sc.object_id = st.object_id and sc.name=c.name
110  where o.name='%s'";
111  var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE
112  var $hasGenID = true;
113  var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)';
114  var $sysTimeStamp = 'GetDate()';
115  var $maxParameterLen = 4000;
116  var $arrayClass = 'ADORecordSet_array_mssqlnative';
117  var $uniqueSort = true;
118  var $leftOuter = '*=';
119  var $rightOuter = '=*';
120  var $ansiOuter = true; // for mssql7 or later
121  var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000
122  var $uniqueOrderBy = true;
123  var $_bindInputArray = true;
124  var $_dropSeqSQL = "drop table %s";
125  var $connectionInfo = array();
126  var $sequences = false;
127  var $mssql_version = '';
128 
129  function ADODB_mssqlnative()
130  {
131  if ($this->debug) {
132  error_log("<pre>");
133  sqlsrv_set_error_handling( SQLSRV_ERRORS_LOG_ALL );
134  sqlsrv_log_set_severity( SQLSRV_LOG_SEVERITY_ALL );
135  sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
136  sqlsrv_configure('warnings_return_as_errors', 0);
137  } else {
138  sqlsrv_set_error_handling(0);
139  sqlsrv_log_set_severity(0);
140  sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
141  sqlsrv_configure('warnings_return_as_errors', 0);
142  }
143  }
144  function ServerVersion() {
145  $data = $this->ServerInfo();
146  if (preg_match('/^09/',$data['version'])){
147  /*
148  * SQL Server 2005
149  */
150  $this->mssql_version = 9;
151  } elseif (preg_match('/^10/',$data['version'])){
152  /*
153  * SQL Server 2008
154  */
155  $this->mssql_version = 10;
156  } elseif (preg_match('/^11/',$data['version'])){
157  /*
158  * SQL Server 2012
159  */
160  $this->mssql_version = 11;
161  } else
162  die("SQL SERVER VERSION {$data['version']} NOT SUPPORTED IN mssqlnative DRIVER");
163  }
164 
165  function ServerInfo() {
166  global $ADODB_FETCH_MODE;
167  static $arr = false;
168  if (is_array($arr))
169  return $arr;
170  if ($this->fetchMode === false) {
171  $savem = $ADODB_FETCH_MODE;
172  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
173  } elseif ($this->fetchMode >=0 && $this->fetchMode <=2) {
174  $savem = $this->fetchMode;
175  } else
176  $savem = $this->SetFetchMode(ADODB_FETCH_NUM);
177 
178  $arrServerInfo = sqlsrv_server_info($this->_connectionID);
179  $ADODB_FETCH_MODE = $savem;
180  $arr['description'] = $arrServerInfo['SQLServerName'].' connected to '.$arrServerInfo['CurrentDatabase'];
181  $arr['version'] = $arrServerInfo['SQLServerVersion'];//ADOConnection::_findvers($arr['description']);
182  return $arr;
183  }
184 
185  function IfNull( $field, $ifNull )
186  {
187  return " ISNULL($field, $ifNull) "; // if MS SQL Server
188  }
189 
190  function _insertid()
191  {
192  // SCOPE_IDENTITY()
193  // Returns the last IDENTITY value inserted into an IDENTITY column in
194  // the same scope. A scope is a module -- a stored procedure, trigger,
195  // function, or batch. Thus, two statements are in the same scope if
196  // they are in the same stored procedure, function, or batch.
197  return $this->lastInsertID;
198  }
199 
200  function _affectedrows()
201  {
202  if ($this->_queryID)
203  return sqlsrv_rows_affected($this->_queryID);
204  }
205 
206  function GenID($seq='adodbseq',$start=1) {
207  if (!$this->mssql_version)
208  $this->ServerVersion();
209  switch($this->mssql_version){
210  case 9:
211  case 10:
212  return $this->GenID2008();
213  break;
214  case 11:
215  return $this->GenID2012();
216  break;
217  }
218  }
219 
220  function CreateSequence($seq='adodbseq',$start=1)
221  {
222  if (!$this->mssql_vesion)
223  $this->ServerVersion();
224 
225  switch($this->mssql_version){
226  case 9:
227  case 10:
228  return $this->CreateSequence2008();
229  break;
230  case 11:
231  return $this->CreateSequence2012();
232  break;
233  }
234 
235  }
236 
240  function CreateSequence2008($seq='adodbseq',$start=1)
241  {
242  if($this->debug) error_log("<hr>CreateSequence($seq,$start)");
243  sqlsrv_begin_transaction($this->_connectionID);
244  $start -= 1;
245  $this->Execute("create table $seq (id int)");//was float(53)
246  $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
247  if (!$ok) {
248  if($this->debug) error_log("<hr>Error: ROLLBACK");
249  sqlsrv_rollback($this->_connectionID);
250  return false;
251  }
252  sqlsrv_commit($this->_connectionID);
253  return true;
254  }
255 
259  function CreateSequence2012($seq='adodb',$start=1){
260  if (!$this->sequences){
261  $sql = "SELECT name FROM sys.sequences";
262  $this->sequences = $this->GetCol($sql);
263  }
264  $ok = $this->Execute("CREATE SEQUENCE $seq START WITH $start INCREMENT BY 1");
265  if (!$ok)
266  die("CANNOT CREATE SEQUENCE" . print_r(sqlsrv_errors(),true));
267  $this->sequences[] = $seq;
268  }
269 
273  function GenID2008($seq='adodbseq',$start=1)
274  {
275  if($this->debug) error_log("<hr>CreateSequence($seq,$start)");
276  sqlsrv_begin_transaction($this->_connectionID);
277  $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
278  if (!$ok) {
279  $start -= 1;
280  $this->Execute("create table $seq (id int)");//was float(53)
281  $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
282  if (!$ok) {
283  if($this->debug) error_log("<hr>Error: ROLLBACK");
284  sqlsrv_rollback($this->_connectionID);
285  return false;
286  }
287  }
288  $num = $this->GetOne("select id from $seq");
289  sqlsrv_commit($this->_connectionID);
290  return true;
291  }
298  function GenID2012($seq='adodbseq',$start=1)
299  {
300 
301  /*
302  * First time in create an array of sequence names that we
303  * can use in later requests to see if the sequence exists
304  * the overhead is creating a list of sequences every time
305  * we need access to at least 1. If we really care about
306  * performance, we could maybe flag a 'nocheck' class variable
307  */
308  if (!$this->sequences){
309  $sql = "SELECT name FROM sys.sequences";
310  $this->sequences = $this->GetCol($sql);
311  }
312  if (!is_array($this->sequences)
313  || is_array($this->sequences) && !in_array($seq,$this->sequences)){
314  $this->CreateSequence2012($seq='adodbseq',$start=1);
315 
316  }
317  $num = $this->GetOne("SELECT NEXT VALUE FOR $seq");
318  return $num;
319  }
320 
321  // Format date column in sql string given an input format that understands Y M D
322  function SQLDate($fmt, $col=false)
323  {
324  if (!$col) $col = $this->sysTimeStamp;
325  $s = '';
326 
327  $len = strlen($fmt);
328  for ($i=0; $i < $len; $i++) {
329  if ($s) $s .= '+';
330  $ch = $fmt[$i];
331  switch($ch) {
332  case 'Y':
333  case 'y':
334  $s .= "datename(yyyy,$col)";
335  break;
336  case 'M':
337  $s .= "convert(char(3),$col,0)";
338  break;
339  case 'm':
340  $s .= "replace(str(month($col),2),' ','0')";
341  break;
342  case 'Q':
343  case 'q':
344  $s .= "datename(quarter,$col)";
345  break;
346  case 'D':
347  case 'd':
348  $s .= "replace(str(day($col),2),' ','0')";
349  break;
350  case 'h':
351  $s .= "substring(convert(char(14),$col,0),13,2)";
352  break;
353 
354  case 'H':
355  $s .= "replace(str(datepart(hh,$col),2),' ','0')";
356  break;
357 
358  case 'i':
359  $s .= "replace(str(datepart(mi,$col),2),' ','0')";
360  break;
361  case 's':
362  $s .= "replace(str(datepart(ss,$col),2),' ','0')";
363  break;
364  case 'a':
365  case 'A':
366  $s .= "substring(convert(char(19),$col,0),18,2)";
367  break;
368 
369  default:
370  if ($ch == '\\') {
371  $i++;
372  $ch = substr($fmt,$i,1);
373  }
374  $s .= $this->qstr($ch);
375  break;
376  }
377  }
378  return $s;
379  }
380 
381 
382  function BeginTrans()
383  {
384  if ($this->transOff) return true;
385  $this->transCnt += 1;
386  if ($this->debug) error_log('<hr>begin transaction');
387  sqlsrv_begin_transaction($this->_connectionID);
388  return true;
389  }
390 
391  function CommitTrans($ok=true)
392  {
393  if ($this->transOff) return true;
394  if ($this->debug) error_log('<hr>commit transaction');
395  if (!$ok) return $this->RollbackTrans();
396  if ($this->transCnt) $this->transCnt -= 1;
397  sqlsrv_commit($this->_connectionID);
398  return true;
399  }
400  function RollbackTrans()
401  {
402  if ($this->transOff) return true;
403  if ($this->debug) error_log('<hr>rollback transaction');
404  if ($this->transCnt) $this->transCnt -= 1;
405  sqlsrv_rollback($this->_connectionID);
406  return true;
407  }
408 
409  function SetTransactionMode( $transaction_mode )
410  {
411  $this->_transmode = $transaction_mode;
412  if (empty($transaction_mode)) {
413  $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
414  return;
415  }
416  if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
417  $this->Execute("SET TRANSACTION ".$transaction_mode);
418  }
419 
420  /*
421  Usage:
422 
423  $this->BeginTrans();
424  $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
425 
426  # some operation on both tables table1 and table2
427 
428  $this->CommitTrans();
429 
430  See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
431  */
432  function RowLock($tables,$where,$col='1 as adodbignore')
433  {
434  if ($col == '1 as adodbignore') $col = 'top 1 null as ignore';
435  if (!$this->transCnt) $this->BeginTrans();
436  return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where");
437  }
438 
439  function SelectDB($dbName)
440  {
441  $this->database = $dbName;
442  $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
443  if ($this->_connectionID) {
444  $rs = $this->Execute('USE '.$dbName);
445  if($rs) {
446  return true;
447  } else return false;
448  }
449  else return false;
450  }
451 
452  function ErrorMsg()
453  {
454  $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL);
455  if($retErrors != null) {
456  foreach($retErrors as $arrError) {
457  $this->_errorMsg .= "SQLState: ".$arrError[ 'SQLSTATE']."\n";
458  $this->_errorMsg .= "Error Code: ".$arrError[ 'code']."\n";
459  $this->_errorMsg .= "Message: ".$arrError[ 'message']."\n";
460  }
461  } else {
462  $this->_errorMsg = "No errors found";
463  }
464  return $this->_errorMsg;
465  }
466 
467  function ErrorNo()
468  {
469  if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
470  $err = sqlsrv_errors(SQLSRV_ERR_ALL);
471  if($err[0]) return $err[0]['code'];
472  else return -1;
473  }
474 
475  // returns true or false
476  function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
477  {
478  if (!function_exists('sqlsrv_connect')) return null;
479  $connectionInfo = $this->connectionInfo;
480  $connectionInfo["Database"]=$argDatabasename;
481  $connectionInfo["UID"]=$argUsername;
482  $connectionInfo["PWD"]=$argPassword;
483  if ($this->debug) error_log("<hr>connecting... hostname: $argHostname params: ".var_export($connectionInfo,true));
484  //if ($this->debug) error_log("<hr>_connectionID before: ".serialize($this->_connectionID));
485  if(!($this->_connectionID = sqlsrv_connect($argHostname,$connectionInfo))) {
486  if ($this->debug) error_log( "<hr><b>errors</b>: ".print_r( sqlsrv_errors(), true));
487  return false;
488  }
489  //if ($this->debug) error_log(" _connectionID after: ".serialize($this->_connectionID));
490  //if ($this->debug) error_log("<hr>defined functions: <pre>".var_export(get_defined_functions(),true)."</pre>");
491  return true;
492  }
493 
494  // returns true or false
495  function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
496  {
497  //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!)
498  return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
499  }
500 
501  function Prepare($sql)
502  {
503  return $sql; // prepare does not work properly with bind parameters as bind parameters are managed by sqlsrv_prepare!
504 
505  $stmt = sqlsrv_prepare( $this->_connectionID, $sql);
506  if (!$stmt) return $sql;
507  return array($sql,$stmt);
508  }
509 
510  // returns concatenated string
511  // MSSQL requires integers to be cast as strings
512  // automatically cast every datatype to VARCHAR(255)
513  // @author David Rogers (introspectshun)
514  function Concat()
515  {
516  $s = "";
517  $arr = func_get_args();
518 
519  // Split single record on commas, if possible
520  if (sizeof($arr) == 1) {
521  foreach ($arr as $arg) {
522  $args = explode(',', $arg);
523  }
524  $arr = $args;
525  }
526 
527  array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
528  $s = implode('+',$arr);
529  if (sizeof($arr) > 0) return "$s";
530 
531  return '';
532  }
533 
534  /*
535  Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
536  So all your blobs must be of type "image".
537 
538  Remember to set in php.ini the following...
539 
540  ; Valid range 0 - 2147483647. Default = 4096.
541  mssql.textlimit = 0 ; zero to pass through
542 
543  ; Valid range 0 - 2147483647. Default = 4096.
544  mssql.textsize = 0 ; zero to pass through
545  */
546  function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
547  {
548 
549  if (strtoupper($blobtype) == 'CLOB') {
550  $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
551  return $this->Execute($sql) != false;
552  }
553  $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
554  return $this->Execute($sql) != false;
555  }
556 
557  // returns query ID if successful, otherwise false
558  function _query($sql,$inputarr=false)
559  {
560  $this->_errorMsg = false;
561 
562  if (is_array($sql)) $sql = $sql[1];
563 
564  $insert = false;
565  // handle native driver flaw for retrieving the last insert ID
566  if(preg_match('/^\W*(insert [^;]+);?$/i', $sql)) {
567  $insert = true;
568  $sql .= '; '.$this->identitySQL; // select scope_identity()
569  }
570  if($inputarr) {
571  $rez = sqlsrv_query($this->_connectionID, $sql, $inputarr);
572  } else {
573  $rez = sqlsrv_query($this->_connectionID,$sql);
574  }
575 
576  if ($this->debug) error_log("<hr>running query: ".var_export($sql,true)."<hr>input array: ".var_export($inputarr,true)."<hr>result: ".var_export($rez,true));
577 
578  if(!$rez) {
579  $rez = false;
580  } else if ($insert) {
581  // retrieve the last insert ID (where applicable)
582  sqlsrv_next_result($rez);
583  sqlsrv_fetch($rez);
584  $this->lastInsertID = sqlsrv_get_field($rez, 0);
585  }
586  return $rez;
587  }
588 
589  // returns true or false
590  function _close()
591  {
592  if ($this->transCnt) $this->RollbackTrans();
593  $rez = @sqlsrv_close($this->_connectionID);
594  $this->_connectionID = false;
595  return $rez;
596  }
597 
598  // mssql uses a default date like Dec 30 2000 12:00AM
599  static function UnixDate($v)
600  {
601  return ADORecordSet_array_mssqlnative::UnixDate($v);
602  }
603 
604  static function UnixTimeStamp($v)
605  {
606  return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
607  }
608 
609  function MetaIndexes($table,$primary=false, $owner = false)
610  {
611  $table = $this->qstr($table);
612 
613  $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
614  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,
615  CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
616  FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
617  INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
618  INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
619  WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
620  ORDER BY O.name, I.Name, K.keyno";
621 
622  global $ADODB_FETCH_MODE;
623  $save = $ADODB_FETCH_MODE;
624  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
625  if ($this->fetchMode !== FALSE) {
626  $savem = $this->SetFetchMode(FALSE);
627  }
628 
629  $rs = $this->Execute($sql);
630  if (isset($savem)) {
631  $this->SetFetchMode($savem);
632  }
633  $ADODB_FETCH_MODE = $save;
634 
635  if (!is_object($rs)) {
636  return FALSE;
637  }
638 
639  $indexes = array();
640  while ($row = $rs->FetchRow()) {
641  if (!$primary && $row[5]) continue;
642 
643  $indexes[$row[0]]['unique'] = $row[6];
644  $indexes[$row[0]]['columns'][] = $row[1];
645  }
646  return $indexes;
647  }
648 
649  function MetaForeignKeys($table, $owner=false, $upper=false)
650  {
651  global $ADODB_FETCH_MODE;
652 
653  $save = $ADODB_FETCH_MODE;
654  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
655  $table = $this->qstr(strtoupper($table));
656 
657  $sql =
658  "select object_name(constid) as constraint_name,
659  col_name(fkeyid, fkey) as column_name,
660  object_name(rkeyid) as referenced_table_name,
661  col_name(rkeyid, rkey) as referenced_column_name
662  from sysforeignkeys
663  where upper(object_name(fkeyid)) = $table
664  order by constraint_name, referenced_table_name, keyno";
665 
666  $constraints =& $this->GetArray($sql);
667 
668  $ADODB_FETCH_MODE = $save;
669 
670  $arr = false;
671  foreach($constraints as $constr) {
672  //print_r($constr);
673  $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
674  }
675  if (!$arr) return false;
676 
677  $arr2 = false;
678 
679  foreach($arr as $k => $v) {
680  foreach($v as $a => $b) {
681  if ($upper) $a = strtoupper($a);
682  $arr2[$a] = $b;
683  }
684  }
685  return $arr2;
686  }
687 
688  //From: Fernando Moreira <FMoreira@imediata.pt>
689  function MetaDatabases()
690  {
691  $this->SelectDB("master");
692  $rs =& $this->Execute($this->metaDatabasesSQL);
693  $rows = $rs->GetRows();
694  $ret = array();
695  for($i=0;$i<count($rows);$i++) {
696  $ret[] = $rows[$i][0];
697  }
698  $this->SelectDB($this->database);
699  if($ret)
700  return $ret;
701  else
702  return false;
703  }
704 
705  // "Stein-Aksel Basma" <basma@accelero.no>
706  // tested with MSSQL 2000
707  function MetaPrimaryKeys($table, $owner=false)
708  {
709  global $ADODB_FETCH_MODE;
710 
711  $schema = '';
712  $this->_findschema($table,$schema);
713  if (!$schema) $schema = $this->database;
714  if ($schema) $schema = "and k.table_catalog like '$schema%'";
715 
716  $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
717  information_schema.table_constraints tc
718  where tc.constraint_name = k.constraint_name and tc.constraint_type =
719  'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
720 
721  $savem = $ADODB_FETCH_MODE;
722  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
723  $a = $this->GetCol($sql);
724  $ADODB_FETCH_MODE = $savem;
725 
726  if ($a && sizeof($a)>0) return $a;
727  $false = false;
728  return $false;
729  }
730 
731 
732  function MetaTables($ttype=false,$showSchema=false,$mask=false)
733  {
734  if ($mask) {
735  $save = $this->metaTablesSQL;
736  $mask = $this->qstr(($mask));
737  $this->metaTablesSQL .= " AND name like $mask";
738  }
739  $ret = ADOConnection::MetaTables($ttype,$showSchema);
740 
741  if ($mask) {
742  $this->metaTablesSQL = $save;
743  }
744  return $ret;
745  }
746  function MetaColumns($table, $upper=true, $schema=false){
747 
748  # start adg
749  static $cached_columns = array();
750  if ($this->cachedSchemaFlush)
751  $cached_columns = array();
752 
753  if (array_key_exists($table,$cached_columns)){
754  return $cached_columns[$table];
755  }
756  # end adg
757 
758  if (!$this->mssql_version)
759  $this->ServerVersion();
760 
761  $this->_findschema($table,$schema);
762  if ($schema) {
763  $dbName = $this->database;
764  $this->SelectDB($schema);
765  }
766  global $ADODB_FETCH_MODE;
767  $save = $ADODB_FETCH_MODE;
768  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
769 
770  if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
771  $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table));
772 
773  if ($schema) {
774  $this->SelectDB($dbName);
775  }
776 
777  if (isset($savem)) $this->SetFetchMode($savem);
778  $ADODB_FETCH_MODE = $save;
779  if (!is_object($rs)) {
780  $false = false;
781  return $false;
782  }
783 
784  $retarr = array();
785  while (!$rs->EOF){
786 
787  $fld = new ADOFieldObject();
788  if (array_key_exists(0,$rs->fields)) {
789  $fld->name = $rs->fields[0];
790  $fld->type = $rs->fields[1];
791  $fld->max_length = $rs->fields[2];
792  $fld->precision = $rs->fields[3];
793  $fld->scale = $rs->fields[4];
794  $fld->not_null =!$rs->fields[5];
795  $fld->has_default = $rs->fields[6];
796  $fld->xtype = $rs->fields[7];
797  $fld->type_length = $rs->fields[8];
798  $fld->auto_increment= $rs->fields[9];
799  } else {
800  $fld->name = $rs->fields['name'];
801  $fld->type = $rs->fields['type'];
802  $fld->max_length = $rs->fields['length'];
803  $fld->precision = $rs->fields['precision'];
804  $fld->scale = $rs->fields['scale'];
805  $fld->not_null =!$rs->fields['nullable'];
806  $fld->has_default = $rs->fields['default_value'];
807  $fld->xtype = $rs->fields['xtype'];
808  $fld->type_length = $rs->fields['type_length'];
809  $fld->auto_increment= $rs->fields['is_identity'];
810  }
811 
812  if ($save == ADODB_FETCH_NUM)
813  $retarr[] = $fld;
814  else
815  $retarr[strtoupper($fld->name)] = $fld;
816 
817  $rs->MoveNext();
818 
819  }
820  $rs->Close();
821  # start adg
822  $cached_columns[$table] = $retarr;
823  # end adg
824  return $retarr;
825  }
826 
827 }
828 
829 /*--------------------------------------------------------------------------------------
830  Class Name: Recordset
831 --------------------------------------------------------------------------------------*/
832 
833 class ADORecordset_mssqlnative extends ADORecordSet {
834 
835  var $databaseType = "mssqlnative";
836  var $canSeek = false;
837  var $fieldOffset = 0;
838  // _mths works only in non-localised system
839 
840  function ADORecordset_mssqlnative($id,$mode=false)
841  {
842  if ($mode === false) {
843  global $ADODB_FETCH_MODE;
844  $mode = $ADODB_FETCH_MODE;
845 
846  }
847  $this->fetchMode = $mode;
848  return $this->ADORecordSet($id,$mode);
849  }
850 
851 
852  function _initrs()
853  {
854  global $ADODB_COUNTRECS;
855  # KMN # if ($this->connection->debug) error_log("(before) ADODB_COUNTRECS: {$ADODB_COUNTRECS} _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
856  /*$retRowsAff = sqlsrv_rows_affected($this->_queryID);//"If you need to determine the number of rows a query will return before retrieving the actual results, appending a SELECT COUNT ... query would let you get that information, and then a call to next_result would move you to the "real" results."
857  error_log("rowsaff: ".serialize($retRowsAff));
858  $this->_numOfRows = ($ADODB_COUNTRECS)? $retRowsAff:-1;*/
859  $this->_numOfRows = -1;//not supported
860  $fieldmeta = sqlsrv_field_metadata($this->_queryID);
861  $this->_numOfFields = ($fieldmeta)? count($fieldmeta):-1;
862  # KMN # if ($this->connection->debug) error_log("(after) _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
863  /*
864  * Copy the oracle method and cache the metadata at init time
865  */
866  if ($this->_numOfFields>0) {
867  $this->_fieldobjs = array();
868  $max = $this->_numOfFields;
869  for ($i=0;$i<$max; $i++) $this->_fieldobjs[] = $this->_FetchField($i);
870  }
871 
872  }
873 
874 
875  //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
876  // get next resultset - requires PHP 4.0.5 or later
877  function NextRecordSet()
878  {
879  if (!sqlsrv_next_result($this->_queryID)) return false;
880  $this->_inited = false;
881  $this->bind = false;
882  $this->_currentRow = -1;
883  $this->Init();
884  return true;
885  }
886 
887  /* Use associative array to get fields array */
888  function Fields($colname)
889  {
890  if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
891  if (!$this->bind) {
892  $this->bind = array();
893  for ($i=0; $i < $this->_numOfFields; $i++) {
894  $o = $this->FetchField($i);
895  $this->bind[strtoupper($o->name)] = $i;
896  }
897  }
898 
899  return $this->fields[$this->bind[strtoupper($colname)]];
900  }
901 
902  /* Returns: an object containing field information.
903  Get column information in the Recordset object. fetchField() can be used in order to obtain information about
904  fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
905  fetchField() is retrieved.
906  Designed By jcortinap#jc.com.mx
907  */
908  function _FetchField($fieldOffset = -1)
909  {
910  $_typeConversion = array(
911  -155 => 'datetimeoffset',
912  -154 => 'time',
913  -152 => 'xml',
914  -151 => 'udt',
915  -11 => 'uniqueidentifier',
916  -10 => 'ntext',
917  -9 => 'nvarchar',
918  -8 => 'nchar',
919  -7 => 'bit',
920  -6 => 'tinyint',
921  -5 => 'bigint',
922  -4 => 'image',
923  -3 => 'varbinary',
924  -2 => 'timestamp',
925  -1 => 'text',
926  1 => 'char',
927  2 => 'numeric',
928  3 => 'decimal',
929  4 => 'int',
930  5 => 'smallint',
931  6 => 'float',
932  7 => 'real',
933  12 => 'varchar',
934  91 => 'date',
935  93 => 'datetime'
936  );
937 
938  $fa = @sqlsrv_field_metadata($this->_queryID);
939  if ($fieldOffset != -1) {
940  $fa = $fa[$fieldOffset];
941  }
942  $false = false;
943  if (empty($fa)) {
944  $f = false;//PHP Notice: Only variable references should be returned by reference
945  }
946  else
947  {
948  // Convert to an object
949  $fa = array_change_key_case($fa, CASE_LOWER);
950  $fb = array();
951  if ($fieldOffset != -1)
952  {
953  $fb = array(
954  'name' => $fa['name'],
955  'max_length' => $fa['size'],
956  'column_source' => $fa['name'],
957  'type' => $_typeConversion[$fa['type']]
958  );
959  }
960  else
961  {
962  foreach ($fa as $key => $value)
963  {
964  $fb[] = array(
965  'name' => $value['name'],
966  'max_length' => $value['size'],
967  'column_source' => $value['name'],
968  'type' => $_typeConversion[$value['type']]
969  );
970  }
971  }
972  $f = (object) $fb;
973  }
974  return $f;
975  }
976 
977  /*
978  * Fetchfield copies the oracle method, it loads the field information
979  * into the _fieldobjs array once, to save multiple calls to the
980  * sqlsrv_field_metadata function
981  *
982  * @author KM Newnham
983  * @date 02/20/2013
984  */
985  function FetchField($fieldOffset = -1)
986  {
987  return $this->_fieldobjs[$fieldOffset];
988  }
989 
990  function _seek($row)
991  {
992  return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams.
993  }
994 
995  // speedup
996  function MoveNext()
997  {
998  //# KMN # if ($this->connection->debug) error_log("movenext()");
999  //# KMN # if ($this->connection->debug) error_log("eof (beginning): ".$this->EOF);
1000  if ($this->EOF) return false;
1001 
1002  $this->_currentRow++;
1003  // # KMN # if ($this->connection->debug) error_log("_currentRow: ".$this->_currentRow);
1004 
1005  if ($this->_fetch()) return true;
1006  $this->EOF = true;
1007  //# KMN # if ($this->connection->debug) error_log("eof (end): ".$this->EOF);
1008 
1009  return false;
1010  }
1011 
1012 
1013  // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
1014  // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
1015  function _fetch($ignore_fields=false)
1016  {
1017  # KMN # if ($this->connection->debug) error_log("_fetch()");
1018  if ($this->fetchMode & ADODB_FETCH_ASSOC) {
1019  if ($this->fetchMode & ADODB_FETCH_NUM) {
1020  //# KMN # if ($this->connection->debug) error_log("fetch mode: both");
1021  $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH);
1022  } else {
1023  //# KMN # if ($this->connection->debug) error_log("fetch mode: assoc");
1024  $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC);
1025  }
1026 
1027  if (is_array($this->fields)) {
1028  if (ADODB_ASSOC_CASE == 0) {
1029  foreach($this->fields as $k=>$v) {
1030  $this->fields[strtolower($k)] = $v;
1031  }
1032  } else if (ADODB_ASSOC_CASE == 1) {
1033  foreach($this->fields as $k=>$v) {
1034  $this->fields[strtoupper($k)] = $v;
1035  }
1036  }
1037  }
1038  } else {
1039  //# KMN # if ($this->connection->debug) error_log("fetch mode: num");
1040  $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC);
1041  }
1042  if(is_array($this->fields) && array_key_exists(1,$this->fields) && !array_key_exists(0,$this->fields)) {//fix fetch numeric keys since they're not 0 based
1043  $arrFixed = array();
1044  foreach($this->fields as $key=>$value) {
1045  if(is_numeric($key)) {
1046  $arrFixed[$key-1] = $value;
1047  } else {
1048  $arrFixed[$key] = $value;
1049  }
1050  }
1051  //if($this->connection->debug) error_log("<hr>fixing non 0 based return array, old: ".print_r($this->fields,true)." new: ".print_r($arrFixed,true));
1052  $this->fields = $arrFixed;
1053  }
1054  if(is_array($this->fields)) {
1055  foreach($this->fields as $key=>$value) {
1056  if (is_object($value) && method_exists($value, 'format')) {//is DateTime object
1057  $this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z");
1058  }
1059  }
1060  }
1061  if($this->fields === null) $this->fields = false;
1062  # KMN # if ($this->connection->debug) error_log("<hr>after _fetch, fields: <pre>".print_r($this->fields,true)." backtrace: ".adodb_backtrace(false));
1063  return $this->fields;
1064  }
1065 
1066  /* close() only needs to be called if you are worried about using too much memory while your script
1067  is running. All associated result memory for the specified result identifier will automatically be freed. */
1068  function _close()
1069  {
1070  $rez = sqlsrv_free_stmt($this->_queryID);
1071  $this->_queryID = false;
1072  return $rez;
1073  }
1074 
1075  // mssql uses a default date like Dec 30 2000 12:00AM
1076  static function UnixDate($v)
1077  {
1078  return ADORecordSet_array_mssqlnative::UnixDate($v);
1079  }
1080 
1081  static function UnixTimeStamp($v)
1082  {
1083  return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
1084  }
1085 }
1086 
1087 
1088 class ADORecordSet_array_mssqlnative extends ADORecordSet_array {
1089  function ADORecordSet_array_mssqlnative($id=-1,$mode=false)
1090  {
1091  $this->ADORecordSet_array($id,$mode);
1092  }
1093 
1094  // mssql uses a default date like Dec 30 2000 12:00AM
1095  static function UnixDate($v)
1096  {
1097 
1098  if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
1099 
1100  global $ADODB_mssql_mths,$ADODB_mssql_date_order;
1101 
1102  //Dec 30 2000 12:00AM
1103  if ($ADODB_mssql_date_order == 'dmy') {
1104  if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
1105  return parent::UnixDate($v);
1106  }
1107  if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1108 
1109  $theday = $rr[1];
1110  $themth = substr(strtoupper($rr[2]),0,3);
1111  } else {
1112  if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
1113  return parent::UnixDate($v);
1114  }
1115  if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1116 
1117  $theday = $rr[2];
1118  $themth = substr(strtoupper($rr[1]),0,3);
1119  }
1120  $themth = $ADODB_mssql_mths[$themth];
1121  if ($themth <= 0) return false;
1122  // h-m-s-MM-DD-YY
1123  return adodb_mktime(0,0,0,$themth,$theday,$rr[3]);
1124  }
1125 
1126  static function UnixTimeStamp($v)
1127  {
1128 
1129  if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
1130 
1131  global $ADODB_mssql_mths,$ADODB_mssql_date_order;
1132 
1133  //Dec 30 2000 12:00AM
1134  if ($ADODB_mssql_date_order == 'dmy') {
1135  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})|"
1136  ,$v, $rr)) return parent::UnixTimeStamp($v);
1137  if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1138 
1139  $theday = $rr[1];
1140  $themth = substr(strtoupper($rr[2]),0,3);
1141  } else {
1142  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})|"
1143  ,$v, $rr)) return parent::UnixTimeStamp($v);
1144  if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1145 
1146  $theday = $rr[2];
1147  $themth = substr(strtoupper($rr[1]),0,3);
1148  }
1149 
1150  $themth = $ADODB_mssql_mths[$themth];
1151  if ($themth <= 0) return false;
1152 
1153  switch (strtoupper($rr[6])) {
1154  case 'P':
1155  if ($rr[4]<12) $rr[4] += 12;
1156  break;
1157  case 'A':
1158  if ($rr[4]==12) $rr[4] = 0;
1159  break;
1160  default:
1161  break;
1162  }
1163  // h-m-s-MM-DD-YY
1164  return adodb_mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
1165  }
1166 }
1167 
1168 /*
1169 Code Example 1:
1170 
1171 select object_name(constid) as constraint_name,
1172  object_name(fkeyid) as table_name,
1173  col_name(fkeyid, fkey) as column_name,
1174  object_name(rkeyid) as referenced_table_name,
1175  col_name(rkeyid, rkey) as referenced_column_name
1176 from sysforeignkeys
1177 where object_name(fkeyid) = x
1178 order by constraint_name, table_name, referenced_table_name, keyno
1179 
1180 Code Example 2:
1181 select constraint_name,
1182  column_name,
1183  ordinal_position
1184 from information_schema.key_column_usage
1185 where constraint_catalog = db_name()
1186 and table_name = x
1187 order by constraint_name, ordinal_position
1188 
1189 http://www.databasejournal.com/scripts/article.php/1440551
1190 */
1191 
1192 ?>




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