?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
on-demand release 4.5dev+
[moodle.git] / lib / adodb / drivers / adodb-mssqlnative.inc.php
blobf7e1bcc1672b22377fecca936b80022b9cfd42d9
1 <?php
2 /**
3 * Native MSSQL driver.
4 *
5 * Requires mssql client. Works on Windows.
6 * https://docs.microsoft.com/sql/connect/php
7 *
8 * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
9 *
10 * @package ADOdb
11 * @link https://adodb.org Project's web site and documentation
12 * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
13 *
14 * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
15 * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
16 * any later version. This means you can use it in proprietary products.
17 * See the LICENSE.md file distributed with this source code for details.
18 * @license BSD-3-Clause
19 * @license LGPL-2.1-or-later
20 *
21 * @copyright 2000-2013 John Lim
22 * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
23 */
24
25 // security - hide paths
26 if (!defined('ADODB_DIR')) die();
27
28 if (!function_exists('sqlsrv_configure')) {
29 die("mssqlnative extension not installed");
30 }
31
32 if (!function_exists('sqlsrv_set_error_handling')) {
33 function sqlsrv_set_error_handling($constant) {
34 sqlsrv_configure("WarningsReturnAsErrors", $constant);
35 }
36 }
37 if (!function_exists('sqlsrv_log_set_severity')) {
38 function sqlsrv_log_set_severity($constant) {
39 sqlsrv_configure("LogSeverity", $constant);
40 }
41 }
42 if (!function_exists('sqlsrv_log_set_subsystems')) {
43 function sqlsrv_log_set_subsystems($constant) {
44 sqlsrv_configure("LogSubsystems", $constant);
45 }
46 }
47
48 class ADODB_mssqlnative extends ADOConnection {
49 var $databaseType = "mssqlnative";
50 var $dataProvider = "mssqlnative";
51 var $replaceQuote = "''"; // string to use to replace quotes
52 var $fmtDate = "'Y-m-d'";
53 var $fmtTimeStamp = "'Y-m-d\TH:i:s'";
54 /**
55 * Enabling InsertID capability will cause execution of an extra query
56 * {@see $identitySQL} after each INSERT statement. To improve performance
57 * when inserting a large number of records, you should switch this off by
58 * calling {@see enableLastInsertID enableLastInsertID(false)}.
59 * @var bool $hasInsertID
60 */
61 var $hasInsertID = true;
62 var $substr = "substring";
63 var $length = 'len';
64 var $hasAffectedRows = true;
65 var $poorAffectedRows = false;
66 var $metaDatabasesSQL = "select name from sys.sysdatabases where name <> 'master'";
67 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'))";
68 var $metaColumnsSQL =
69 "select c.name,
70 t.name as type,
71 c.length,
72 c.xprec as precision,
73 c.xscale as scale,
74 c.isnullable as nullable,
75 c.cdefault as default_value,
76 c.xtype,
77 t.length as type_length,
78 sc.is_identity
79 from syscolumns c
80 join systypes t on t.xusertype=c.xusertype
81 join sysobjects o on o.id=c.id
82 join sys.tables st on st.name=o.name
83 join sys.columns sc on sc.object_id = st.object_id and sc.name=c.name
84 where o.name='%s'";
85 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE
86 var $hasGenID = true;
87 var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)';
88 var $sysTimeStamp = 'GetDate()';
89 var $maxParameterLen = 4000;
90 var $arrayClass = 'ADORecordSet_array_mssqlnative';
91 var $uniqueSort = true;
92 var $leftOuter = '*=';
93 var $rightOuter = '=*';
94 var $ansiOuter = true; // for mssql7 or later
95 var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000
96 var $uniqueOrderBy = true;
97 var $_bindInputArray = true;
98 var $_dropSeqSQL = "drop table %s";
99
100 var $connectionInfo = array('ReturnDatesAsStrings'=>true);
101 var $cachedSchemaFlush = false;
102
103 var $sequences = false;
104 var $mssql_version = '';
105
106 function __construct()
107 {
108 if ($this->debug) {
109 ADOConnection::outp("<pre>");
110 sqlsrv_set_error_handling( SQLSRV_ERRORS_LOG_ALL );
111 sqlsrv_log_set_severity( SQLSRV_LOG_SEVERITY_ALL );
112 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
113 sqlsrv_configure('WarningsReturnAsErrors', 0);
114 } else {
115 sqlsrv_set_error_handling(0);
116 sqlsrv_log_set_severity(0);
117 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
118 sqlsrv_configure('WarningsReturnAsErrors', 0);
119 }
120 }
121
122 /**
123 * Initializes the SQL Server version.
124 * Dies if connected to a non-supported version (2000 and older)
125 */
126 function ServerVersion() {
127 $data = $this->ServerInfo();
128 preg_match('/^\d{2}/', $data['version'], $matches);
129 $version = (int)reset($matches);
130
131 // We only support SQL Server 2005 and up
132 if($version < 9) {
133 die("SQL SERVER VERSION {$data['version']} NOT SUPPORTED IN mssqlnative DRIVER");
134 }
135
136 $this->mssql_version = $version;
137 }
138
139 function ServerInfo() {
140 global $ADODB_FETCH_MODE;
141 static $arr = false;
142 if (is_array($arr))
143 return $arr;
144 if ($this->fetchMode === false) {
145 $savem = $ADODB_FETCH_MODE;
146 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
147 } elseif ($this->fetchMode >=0 && $this->fetchMode <=2) {
148 $savem = $this->fetchMode;
149 } else
150 $savem = $this->SetFetchMode(ADODB_FETCH_NUM);
151
152 $arrServerInfo = sqlsrv_server_info($this->_connectionID);
153 $ADODB_FETCH_MODE = $savem;
154
155 $arr = array();
156 $arr['description'] = $arrServerInfo['SQLServerName'].' connected to '.$arrServerInfo['CurrentDatabase'];
157 $arr['version'] = $arrServerInfo['SQLServerVersion'];//ADOConnection::_findvers($arr['description']);
158 return $arr;
159 }
160
161 function IfNull( $field, $ifNull )
162 {
163 return " ISNULL($field, $ifNull) "; // if MS SQL Server
164 }
165
166 public function enableLastInsertID($enable = true) {
167 $this->hasInsertID = $enable;
168 $this->lastInsID = false;
169 }
170
171 /**
172 * Get the last value inserted into an IDENTITY column.
173 *
174 * The value will actually be set in {@see _query()} when executing an
175 * INSERT statement, but only if the connection's $hasInsertId property
176 * is true; this can be set with {@see enableLastInsertId()}.
177 *
178 * @inheritDoc
179 */
180 protected function _insertID($table = '', $column = '')
181 {
182 return $this->lastInsID;
183 }
184
185 function _affectedrows()
186 {
187 if ($this->_queryID && is_resource($this->_queryID)) {
188 return sqlsrv_rows_affected($this->_queryID);
189 }
190 return false;
191 }
192
193 function GenID($seq='adodbseq',$start=1) {
194 switch($this->mssql_version){
195 case 9:
196 case 10:
197 return $this->GenID2008($seq, $start);
198 break;
199 default:
200 return $this->GenID2012($seq, $start);
201 break;
202 }
203 }
204
205 function CreateSequence($seq='adodbseq',$start=1)
206 {
207 switch($this->mssql_version){
208 case 9:
209 case 10:
210 return $this->CreateSequence2008($seq, $start);
211 break;
212 default:
213 return $this->CreateSequence2012($seq, $start);
214 break;
215 }
216 }
217
218 /**
219 * For Server 2005,2008, duplicate a sequence with an identity table
220 */
221 function CreateSequence2008($seq='adodbseq',$start=1)
222 {
223 if($this->debug) ADOConnection::outp("<hr>CreateSequence($seq,$start)");
224 sqlsrv_begin_transaction($this->_connectionID);
225 $start -= 1;
226 $this->Execute("create table $seq (id int)");//was float(53)
227 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
228 if (!$ok) {
229 if($this->debug) ADOConnection::outp("<hr>Error: ROLLBACK");
230 sqlsrv_rollback($this->_connectionID);
231 return false;
232 }
233 sqlsrv_commit($this->_connectionID);
234 return true;
235 }
236
237 /**
238 * Proper Sequences Only available to Server 2012 and up
239 */
240 function CreateSequence2012($seq='adodbseq',$start=1){
241 if (!$this->sequences){
242 $sql = "SELECT name FROM sys.sequences";
243 $this->sequences = $this->GetCol($sql);
244 }
245 $ok = $this->Execute("CREATE SEQUENCE $seq START WITH $start INCREMENT BY 1");
246 if (!$ok)
247 die("CANNOT CREATE SEQUENCE" . print_r(sqlsrv_errors(),true));
248 $this->sequences[] = $seq;
249 }
250
251 /**
252 * For Server 2005,2008, duplicate a sequence with an identity table
253 */
254 function GenID2008($seq='adodbseq',$start=1)
255 {
256 if($this->debug) ADOConnection::outp("<hr>CreateSequence($seq,$start)");
257 sqlsrv_begin_transaction($this->_connectionID);
258 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
259 if (!$ok) {
260 $start -= 1;
261 $this->Execute("create table $seq (id int)");//was float(53)
262 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
263 if (!$ok) {
264 if($this->debug) ADOConnection::outp("<hr>Error: ROLLBACK");
265 sqlsrv_rollback($this->_connectionID);
266 return false;
267 }
268 }
269 $num = $this->GetOne("select id from $seq");
270 sqlsrv_commit($this->_connectionID);
271 return $num;
272 }
273 /**
274 * Only available to Server 2012 and up
275 * Cannot do this the normal adodb way by trapping an error if the
276 * sequence does not exist because sql server will auto create a
277 * sequence with the starting number of -9223372036854775808
278 */
279 function GenID2012($seq='adodbseq',$start=1)
280 {
281
282 /*
283 * First time in create an array of sequence names that we
284 * can use in later requests to see if the sequence exists
285 * the overhead is creating a list of sequences every time
286 * we need access to at least 1. If we really care about
287 * performance, we could maybe flag a 'nocheck' class variable
288 */
289 if (!$this->sequences){
290 $sql = "SELECT name FROM sys.sequences";
291 $this->sequences = $this->GetCol($sql);
292 }
293 if (!is_array($this->sequences)
294 || is_array($this->sequences) && !in_array($seq,$this->sequences)){
295 $this->CreateSequence2012($seq, $start);
296
297 }
298 $num = $this->GetOne("SELECT NEXT VALUE FOR $seq");
299 return $num;
300 }
301
302 // Format date column in sql string given an input format that understands Y M D
303 function SQLDate($fmt, $col=false)
304 {
305 if (!$col) {
306 $col = $this->sysTimeStamp;
307 }
308 $s = '';
309
310 $ConvertableFmt=array(
311 "m/d/Y"=>101, "m/d/y"=>101 // US
312 ,"Y.m.d"=>102, "y.m.d"=>102 // ANSI
313 ,"d/m/Y"=>103, "d/m/y"=>103 // French /english
314 ,"d.m.Y"=>104, "d.m.y"=>104 // German
315 ,"d-m-Y"=>105, "d-m-y"=>105 // Italian
316 ,"m-d-Y"=>110, "m-d-y"=>110 // US Dash
317 ,"Y/m/d"=>111, "y/m/d"=>111 // Japan
318 ,"Ymd"=>112, "ymd"=>112 // ISO
319 ,"H:i:s"=>108 // Time
320 );
321 if (key_exists($fmt,$ConvertableFmt)) {
322 return "convert (varchar ,$col," . $ConvertableFmt[$fmt] . ")";
323 }
324
325 $len = strlen($fmt);
326 for ($i=0; $i < $len; $i++) {
327 if ($s) $s .= '+';
328 $ch = $fmt[$i];
329 switch($ch) {
330 case 'Y':
331 case 'y':
332 $s .= "datename(yyyy,$col)";
333 break;
334 case 'M':
335 $s .= "convert(char(3),$col,0)";
336 break;
337 case 'm':
338 $s .= "replace(str(month($col),2),' ','0')";
339 break;
340 case 'Q':
341 case 'q':
342 $s .= "datename(quarter,$col)";
343 break;
344 case 'D':
345 case 'd':
346 $s .= "replace(str(day($col),2),' ','0')";
347 break;
348 case 'h':
349 $s .= "substring(convert(char(14),$col,0),13,2)";
350 break;
351
352 case 'H':
353 $s .= "replace(str(datepart(hh,$col),2),' ','0')";
354 break;
355
356 case 'i':
357 $s .= "replace(str(datepart(mi,$col),2),' ','0')";
358 break;
359 case 's':
360 $s .= "replace(str(datepart(ss,$col),2),' ','0')";
361 break;
362 case 'a':
363 case 'A':
364 $s .= "substring(convert(char(19),$col,0),18,2)";
365 break;
366 case 'l':
367 $s .= "datename(dw,$col)";
368 break;
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) ADOConnection::outp('<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) ADOConnection::outp('<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
401 function RollbackTrans()
402 {
403 if ($this->transOff) return true;
404 if ($this->debug) ADOConnection::outp('<hr>rollback transaction');
405 if ($this->transCnt) $this->transCnt -= 1;
406 sqlsrv_rollback($this->_connectionID);
407 return true;
408 }
409
410 function SetTransactionMode( $transaction_mode )
411 {
412 $this->_transmode = $transaction_mode;
413 if (empty($transaction_mode)) {
414 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
415 return;
416 }
417 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
418 $this->Execute("SET TRANSACTION ".$transaction_mode);
419 }
420
421 /*
422 Usage:
423
424 $this->BeginTrans();
425 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
426
427 # some operation on both tables table1 and table2
428
429 $this->CommitTrans();
430
431 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
432 */
433 function RowLock($tables,$where,$col='1 as adodbignore')
434 {
435 if ($col == '1 as adodbignore') $col = 'top 1 null as ignore';
436 if (!$this->transCnt) $this->BeginTrans();
437 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where");
438 }
439
440 function SelectDB($dbName)
441 {
442 $this->database = $dbName;
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 }
462 return $this->_errorMsg;
463 }
464
465 function ErrorNo()
466 {
467 $err = sqlsrv_errors(SQLSRV_ERR_ALL);
468 if ($err && $err[0])
469 return $err[0]['code'];
470 else
471 return 0;
472 }
473
474 // returns true or false
475 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
476 {
477 if (!function_exists('sqlsrv_connect'))
478 {
479 if ($this->debug)
480 ADOConnection::outp('Microsoft SQL Server native driver (mssqlnative) not installed');
481 return null;
482 }
483
484 if (!empty($this->port))
485 /*
486 * Port uses a comma
487 */
488 $argHostname .= ",".$this->port;
489
490 $connectionInfo = $this->connectionInfo;
491 $connectionInfo["Database"] = $argDatabasename;
492 if ((string)$argUsername != '' || (string)$argPassword != '')
493 {
494 /*
495 * If they pass either a userid or password, we assume
496 * SQL Server authentication
497 */
498 $connectionInfo["UID"] = $argUsername;
499 $connectionInfo["PWD"] = $argPassword;
500
501 if ($this->debug)
502 ADOConnection::outp('userid or password supplied, attempting connection with SQL Server Authentication');
503
504 }
505 else
506 {
507 /*
508 * If they don't pass either value, we won't add them to the
509 * connection parameters. This will then force an attempt
510 * to use windows authentication
511 */
512 if ($this->debug)
513
514 ADOConnection::outp('No userid or password supplied, attempting connection with Windows Authentication');
515 }
516
517
518 /*
519 * Now merge in the passed connection parameters setting
520 */
521 foreach ($this->connectionParameters as $options)
522 {
523 foreach($options as $parameter=>$value)
524 $connectionInfo[$parameter] = $value;
525 }
526
527 if ($this->debug) ADOConnection::outp("connecting to host: $argHostname params: ".var_export($connectionInfo,true));
528 if(!($this->_connectionID = @sqlsrv_connect($argHostname,$connectionInfo)))
529 {
530 if ($this->debug)
531 ADOConnection::outp( 'Connection Failed: '.print_r( sqlsrv_errors(), true));
532 return false;
533 }
534
535 $this->ServerVersion();
536
537 return true;
538 }
539
540 // returns true or false
541 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
542 {
543 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!)
544 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
545 }
546
547
548 function Prepare($sql)
549 {
550 return $sql; // prepare does not work properly with bind parameters as bind parameters are managed by sqlsrv_prepare!
551 }
552
553 // returns concatenated string
554 // MSSQL requires integers to be cast as strings
555 // automatically cast every datatype to VARCHAR(255)
556 // @author David Rogers (introspectshun)
557 function Concat()
558 {
559 $s = "";
560 $arr = func_get_args();
561
562 // Split single record on commas, if possible
563 if (sizeof($arr) == 1) {
564 foreach ($arr as $arg) {
565 $args = explode(',', $arg);
566 }
567 $arr = $args;
568 }
569
570 array_walk(
571 $arr,
572 function(&$value, $key) {
573 $value = "CAST(" . $value . " AS VARCHAR(255))";
574 }
575 );
576 $s = implode('+',$arr);
577 if (sizeof($arr) > 0) return "$s";
578
579 return '';
580 }
581
582 /*
583 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
584 So all your blobs must be of type "image".
585
586 Remember to set in php.ini the following...
587
588 ; Valid range 0 - 2147483647. Default = 4096.
589 mssql.textlimit = 0 ; zero to pass through
590
591 ; Valid range 0 - 2147483647. Default = 4096.
592 mssql.textsize = 0 ; zero to pass through
593 */
594 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
595 {
596
597 if (strtoupper($blobtype) == 'CLOB') {
598 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
599 return $this->Execute($sql) != false;
600 }
601 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
602 return $this->Execute($sql) != false;
603 }
604
605 /**
606 * Execute a query.
607 *
608 * If executing an INSERT statement and $hasInsertId is true, will set
609 * $lastInsId.
610 *
611 * @param string $sql
612 * @param array $inputarr
613 * @return resource|false Query Id if successful, otherwise false
614 */
615 function _query($sql, $inputarr = false)
616 {
617 $this->_errorMsg = false;
618
619 if (is_array($sql)) {
620 $sql = $sql[1];
621 }
622
623 // Handle native driver flaw for retrieving the last insert ID
624 if ($this->hasInsertID) {
625 // Check if it's an INSERT statement
626 $retrieveLastInsertID = preg_match(
627 '/^\W*insert[\s\w()[\]",.]+values\s*\((?:[^;\']|\'\'|(?:(?:\'\')*\'[^\']+\'(?:\'\')*))*;?$/i',
628 $sql
629 );
630 if ($retrieveLastInsertID) {
631 // Append the identity SQL, so it is executed in the same
632 // scope as the insert query.
633 $sql .= '; ' . $this->identitySQL;
634 }
635 } else {
636 $retrieveLastInsertID = false;
637 }
638
639 if ($inputarr) {
640 // Ensure that the input array is indexed numerically, as required
641 // by sqlsrv_query(). If param() was used to create portable binds
642 // then the array might be associative.
643 $inputarr = array_values($inputarr);
644 $rez = sqlsrv_query($this->_connectionID, $sql, $inputarr);
645 } else {
646 $rez = sqlsrv_query($this->_connectionID, $sql);
647 }
648
649 $this->lastInsID = false;
650 if (!$rez) {
651 $rez = false;
652 } elseif ($retrieveLastInsertID) {
653 // Get the inserted id from the last result
654 // Note: loop is required as server may return more than one row,
655 // e.g. if triggers are involved (see #41)
656 while (sqlsrv_next_result($rez)) {
657 sqlsrv_fetch($rez);
658 $this->lastInsID = sqlsrv_get_field($rez, 0);
659 }
660 }
661 return $rez;
662 }
663
664 /**
665 * Rolls back pending transactions and closes the connection.
666 *
667 * @return bool True, unless the connection id is invalid
668 */
669 function _close()
670 {
671 if ($this->transCnt) {
672 $this->RollbackTrans();
673 }
674 if ($this->_connectionID) {
675 return sqlsrv_close($this->_connectionID);
676 }
677 $this->_connectionID = false;
678 return true;
679 }
680
681
682 function MetaIndexes($table,$primary=false, $owner = false)
683 {
684 $table = $this->qstr($table);
685
686 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
687 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,
688 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
689 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
690 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
691 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
692 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
693 ORDER BY O.name, I.Name, K.keyno";
694
695 global $ADODB_FETCH_MODE;
696 $save = $ADODB_FETCH_MODE;
697 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
698 if ($this->fetchMode !== FALSE) {
699 $savem = $this->SetFetchMode(FALSE);
700 }
701
702 $rs = $this->Execute($sql);
703 if (isset($savem)) {
704 $this->SetFetchMode($savem);
705 }
706 $ADODB_FETCH_MODE = $save;
707
708 if (!is_object($rs)) {
709 return FALSE;
710 }
711
712 $indexes = array();
713 while ($row = $rs->FetchRow()) {
714 if (!$primary && $row[5]) continue;
715
716 $indexes[$row[0]]['unique'] = $row[6];
717 $indexes[$row[0]]['columns'][] = $row[1];
718 }
719 return $indexes;
720 }
721
722 public function metaForeignKeys($table, $owner = '', $upper = false, $associative = false)
723 {
724 global $ADODB_FETCH_MODE;
725
726 $save = $ADODB_FETCH_MODE;
727 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
728 $table = $this->qstr(strtoupper($table));
729
730 $sql =
731 "select object_name(constid) as constraint_name,
732 col_name(fkeyid, fkey) as column_name,
733 object_name(rkeyid) as referenced_table_name,
734 col_name(rkeyid, rkey) as referenced_column_name
735 from sysforeignkeys
736 where upper(object_name(fkeyid)) = $table
737 order by constraint_name, referenced_table_name, keyno";
738
739 $constraints = $this->GetArray($sql);
740
741 $ADODB_FETCH_MODE = $save;
742
743 $arr = false;
744 foreach($constraints as $constr) {
745 //print_r($constr);
746 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
747 }
748 if (!$arr) return false;
749
750 $arr2 = false;
751
752 foreach($arr as $k => $v) {
753 foreach($v as $a => $b) {
754 if ($upper) $a = strtoupper($a);
755 if (is_array($arr2[$a])) { // a previous foreign key was define for this reference table, we merge the new one
756 $arr2[$a] = array_merge($arr2[$a], $b);
757 } else {
758 $arr2[$a] = $b;
759 }
760 }
761 }
762 return $arr2;
763 }
764
765 //From: Fernando Moreira <FMoreira@imediata.pt>
766 function MetaDatabases()
767 {
768 $this->SelectDB("master");
769 $rs = $this->Execute($this->metaDatabasesSQL);
770 $rows = $rs->GetRows();
771 $ret = array();
772 for($i=0;$i<count($rows);$i++) {
773 $ret[] = $rows[$i][0];
774 }
775 $this->SelectDB($this->database);
776 if($ret)
777 return $ret;
778 else
779 return false;
780 }
781
782 // "Stein-Aksel Basma" <basma@accelero.no>
783 // tested with MSSQL 2000
784 function MetaPrimaryKeys($table, $owner=false)
785 {
786 global $ADODB_FETCH_MODE;
787
788 $schema = '';
789 $this->_findschema($table,$schema);
790 if (!$schema) $schema = $this->database;
791 if ($schema) $schema = "and k.table_catalog like '$schema%'";
792
793 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
794 information_schema.table_constraints tc
795 where tc.constraint_name = k.constraint_name and tc.constraint_type =
796 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
797
798 $savem = $ADODB_FETCH_MODE;
799 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
800 $a = $this->GetCol($sql);
801 $ADODB_FETCH_MODE = $savem;
802
803 if ($a && sizeof($a)>0) return $a;
804 $false = false;
805 return $false;
806 }
807
808
809 function MetaTables($ttype=false,$showSchema=false,$mask=false)
810 {
811 if ($mask) {
812 $save = $this->metaTablesSQL;
813 $mask = $this->qstr(($mask));
814 $this->metaTablesSQL .= " AND name like $mask";
815 }
816 $ret = ADOConnection::MetaTables($ttype,$showSchema);
817
818 if ($mask) {
819 $this->metaTablesSQL = $save;
820 }
821 return $ret;
822 }
823 function MetaColumns($table, $upper=true, $schema=false){
824
825 /*
826 * A simple caching mechanism, to be replaced in ADOdb V6
827 */
828 static $cached_columns = array();
829 if ($this->cachedSchemaFlush)
830 $cached_columns = array();
831
832 if (array_key_exists($table,$cached_columns)){
833 return $cached_columns[$table];
834 }
835
836
837 $this->_findschema($table,$schema);
838 if ($schema) {
839 $dbName = $this->database;
840 $this->SelectDB($schema);
841 }
842 global $ADODB_FETCH_MODE;
843 $save = $ADODB_FETCH_MODE;
844 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
845
846 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
847 $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table));
848
849 if ($schema) {
850 $this->SelectDB($dbName);
851 }
852
853 if (isset($savem)) $this->SetFetchMode($savem);
854 $ADODB_FETCH_MODE = $save;
855 if (!is_object($rs)) {
856 $false = false;
857 return $false;
858 }
859
860 $retarr = array();
861 while (!$rs->EOF){
862
863 $fld = new ADOFieldObject();
864 if (array_key_exists(0,$rs->fields)) {
865 $fld->name = $rs->fields[0];
866 $fld->type = $rs->fields[1];
867 $fld->max_length = $rs->fields[2];
868 $fld->precision = $rs->fields[3];
869 $fld->scale = $rs->fields[4];
870 $fld->not_null =!$rs->fields[5];
871 $fld->has_default = $rs->fields[6];
872 $fld->xtype = $rs->fields[7];
873 $fld->type_length = $rs->fields[8];
874 $fld->auto_increment= $rs->fields[9];
875 } else {
876 $fld->name = $rs->fields['name'];
877 $fld->type = $rs->fields['type'];
878 $fld->max_length = $rs->fields['length'];
879 $fld->precision = $rs->fields['precision'];
880 $fld->scale = $rs->fields['scale'];
881 $fld->not_null =!$rs->fields['nullable'];
882 $fld->has_default = $rs->fields['default_value'];
883 $fld->xtype = $rs->fields['xtype'];
884 $fld->type_length = $rs->fields['type_length'];
885 $fld->auto_increment= $rs->fields['is_identity'];
886 }
887
888 if ($save == ADODB_FETCH_NUM)
889 $retarr[] = $fld;
890 else
891 $retarr[strtoupper($fld->name)] = $fld;
892
893 $rs->MoveNext();
894
895 }
896 $rs->Close();
897 $cached_columns[$table] = $retarr;
898
899 return $retarr;
900 }
901
902 /**
903 * Returns a substring of a varchar type field
904 *
905 * The SQL server version varies because the length is mandatory, so
906 * we append a reasonable string length
907 *
908 * @param string $fld The field to sub-string
909 * @param int $start The start point
910 * @param int $length An optional length
911 *
912 * @return The SQL text
913 */
914 function substr($fld,$start,$length=0)
915 {
916 if ($length == 0)
917 /*
918 * The length available to varchar is 2GB, but that makes no
919 * sense in a substring, so I'm going to arbitrarily limit
920 * the length to 1K, but you could change it if you want
921 */
922 $length = 1024;
923
924 $text = "SUBSTRING($fld,$start,$length)";
925 return $text;
926 }
927
928 /**
929 * Returns the maximum size of a MetaType C field. Because of the
930 * database design, SQL Server places no limits on the size of data inserted
931 * Although the actual limit is 2^31-1 bytes.
932 *
933 * @return int
934 */
935 function charMax()
936 {
937 return ADODB_STRINGMAX_NOLIMIT;
938 }
939
940 /**
941 * Returns the maximum size of a MetaType X field. Because of the
942 * database design, SQL Server places no limits on the size of data inserted
943 * Although the actual limit is 2^31-1 bytes.
944 *
945 * @return int
946 */
947 function textMax()
948 {
949 return ADODB_STRINGMAX_NOLIMIT;
950 }
951 /**
952 * Lists procedures, functions and methods in an array.
953 *
954 * @param string $procedureNamePattern (optional)
955 * @param string $catalog (optional)
956 * @param string $schemaPattern (optional)
957
958 * @return array of stored objects in current database.
959 *
960 */
961 public function metaProcedures($procedureNamePattern = null, $catalog = null, $schemaPattern = null)
962 {
963 $metaProcedures = array();
964 $procedureSQL = '';
965 $catalogSQL = '';
966 $schemaSQL = '';
967
968 if ($procedureNamePattern)
969 $procedureSQL = "AND ROUTINE_NAME LIKE " . strtoupper($this->qstr($procedureNamePattern));
970
971 if ($catalog)
972 $catalogSQL = "AND SPECIFIC_SCHEMA=" . strtoupper($this->qstr($catalog));
973
974 if ($schemaPattern)
975 $schemaSQL = "AND ROUTINE_SCHEMA LIKE {$this->qstr($schemaPattern)}";
976
977 $fields = " ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_SCHEMA,ROUTINE_CATALOG";
978
979 $SQL = "SELECT $fields
980 FROM {$this->database}.information_schema.routines
981 WHERE 1=1
982 $procedureSQL
983 $catalogSQL
984 $schemaSQL
985 ORDER BY ROUTINE_NAME
986 ";
987
988 $result = $this->execute($SQL);
989
990 if (!$result)
991 return false;
992 while ($r = $result->fetchRow()){
993 if (!isset($r[0]))
994 /*
995 * Convert to numeric
996 */
997 $r = array_values($r);
998
999 $procedureName = $r[0];
1000 $schemaName = $r[2];
1001 $routineCatalog= $r[3];
1002 $metaProcedures[$procedureName] = array('type'=> $r[1],
1003 'catalog' => $routineCatalog,
1004 'schema' => $schemaName,
1005 'remarks' => '',
1006 );
1007 }
1008
1009 return $metaProcedures;
1010 }
1011
1012 /**
1013 * An SQL Statement that adds a specific number of
1014 * days or part to local datetime
1015 *
1016 * @param float $dayFraction
1017 * @param string $date
1018 *
1019 * @return string
1020 */
1021 public function offsetDate($dayFraction, $date = false)
1022 {
1023 if (!$date)
1024 /*
1025 * Use GETDATE() via systTimestamp;
1026 */
1027 $date = $this->sysTimeStamp;
1028
1029 /*
1030 * seconds, number of seconds, date base
1031 */
1032 $dateFormat = "DATEADD(s, %s, %s)";
1033
1034 /*
1035 * Adjust the offset back to seconds
1036 */
1037 $fraction = $dayFraction * 24 * 3600;
1038
1039 return sprintf($dateFormat,$fraction,$date);
1040
1041 }
1042
1043 }
1044
1045 /*--------------------------------------------------------------------------------------
1046 Class Name: Recordset
1047 --------------------------------------------------------------------------------------*/
1048
1049 class ADORecordset_mssqlnative extends ADORecordSet {
1050
1051 var $databaseType = "mssqlnative";
1052 var $canSeek = false;
1053 var $fieldOffset = 0;
1054 // _mths works only in non-localised system
1055
1056 /**
1057 * @var bool True if we have retrieved the fields metadata
1058 */
1059 private $fieldObjectsRetrieved = false;
1060
1061 /*
1062 * Cross-reference the objects by name for easy access
1063 */
1064 private $fieldObjectsIndex = array();
1065
1066 /*
1067 * Cross references the dateTime objects for faster decoding
1068 */
1069 private $dateTimeObjects = array();
1070
1071 /*
1072 * flags that we have dateTimeObjects to handle
1073 */
1074 private $hasDateTimeObjects = false;
1075
1076 /*
1077 * This is cross reference between how the types are stored
1078 * in SQL Server and their english-language description
1079 * -154 is a time field, see #432
1080 */
1081 private $_typeConversion = array(
1082 -155 => 'datetimeoffset',
1083 -154 => 'char',
1084 -152 => 'xml',
1085 -151 => 'udt',
1086 -11 => 'uniqueidentifier',
1087 -10 => 'ntext',
1088 -9 => 'nvarchar',
1089 -8 => 'nchar',
1090 -7 => 'bit',
1091 -6 => 'tinyint',
1092 -5 => 'bigint',
1093 -4 => 'image',
1094 -3 => 'varbinary',
1095 -2 => 'timestamp',
1096 -1 => 'text',
1097 1 => 'char',
1098 2 => 'numeric',
1099 3 => 'decimal',
1100 4 => 'int',
1101 5 => 'smallint',
1102 6 => 'float',
1103 7 => 'real',
1104 12 => 'varchar',
1105 91 => 'date',
1106 93 => 'datetime'
1107 );
1108
1109
1110
1111
1112 function __construct($id,$mode=false)
1113 {
1114 if ($mode === false) {
1115 global $ADODB_FETCH_MODE;
1116 $mode = $ADODB_FETCH_MODE;
1117
1118 }
1119 $this->fetchMode = $mode;
1120 parent::__construct($id);
1121 }
1122
1123
1124 function _initrs()
1125 {
1126 $this->_numOfRows = -1;//not supported
1127 // Cache the metadata right now
1128 $this->_fetchField();
1129
1130 }
1131
1132
1133 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
1134 // get next resultset - requires PHP 4.0.5 or later
1135 function NextRecordSet()
1136 {
1137 if (!sqlsrv_next_result($this->_queryID)) return false;
1138 $this->_inited = false;
1139 $this->bind = false;
1140 $this->_currentRow = -1;
1141 $this->Init();
1142 return true;
1143 }
1144
1145 /* Use associative array to get fields array */
1146 function Fields($colname)
1147 {
1148 if (!is_array($this->fields))
1149 /*
1150 * Too early
1151 */
1152 return;
1153 if ($this->fetchMode != ADODB_FETCH_NUM)
1154 return $this->fields[$colname];
1155
1156 if (!$this->bind) {
1157 $this->bind = array();
1158 for ($i=0; $i < $this->_numOfFields; $i++) {
1159 $o = $this->FetchField($i);
1160 $this->bind[strtoupper($o->name)] = $i;
1161 }
1162 }
1163
1164 return $this->fields[$this->bind[strtoupper($colname)]];
1165 }
1166
1167 /**
1168 * Returns: an object containing field information.
1169 *
1170 * Get column information in the Recordset object. fetchField()
1171 * can be used in order to obtain information about fields in a
1172 * certain query result. If the field offset isn't specified,
1173 * the next field that wasn't yet retrieved by fetchField()
1174 * is retrieved.
1175 *
1176 * @param int $fieldOffset (optional default=-1 for all
1177 * @return mixed an ADOFieldObject, or array of objects
1178 */
1179 private function _fetchField($fieldOffset = -1)
1180 {
1181 if ($this->fieldObjectsRetrieved) {
1182 if ($this->fieldObjectsCache) {
1183 // Already got the information
1184 if ($fieldOffset == -1) {
1185 return $this->fieldObjectsCache;
1186 } else {
1187 return $this->fieldObjectsCache[$fieldOffset];
1188 }
1189 } else {
1190 // No metadata available
1191 return false;
1192 }
1193 }
1194
1195 $this->fieldObjectsRetrieved = true;
1196 /*
1197 * Retrieve all metadata in one go. This is always returned as a
1198 * numeric array.
1199 */
1200 $fieldMetaData = sqlsrv_field_metadata($this->_queryID);
1201
1202 if (!$fieldMetaData) {
1203 // Not a statement that gives us metaData
1204 return false;
1205 }
1206
1207 $this->_numOfFields = count($fieldMetaData);
1208 foreach ($fieldMetaData as $key=>$value) {
1209 $fld = new ADOFieldObject;
1210 // Caution - keys are case-sensitive, must respect casing of values
1211 $fld->name = $value['Name'];
1212 $fld->max_length = $value['Size'];
1213 $fld->column_source = $value['Name'];
1214 $fld->type = $this->_typeConversion[$value['Type']];
1215
1216 $this->fieldObjectsCache[$key] = $fld;
1217 $this->fieldObjectsIndex[$fld->name] = $key;
1218 }
1219 if ($fieldOffset == -1) {
1220 return $this->fieldObjectsCache;
1221 }
1222
1223 return $this->fieldObjectsCache[$fieldOffset];
1224 }
1225
1226 /*
1227 * Fetchfield copies the oracle method, it loads the field information
1228 * into the _fieldobjs array once, to save multiple calls to the
1229 * sqlsrv_field_metadata function
1230 *
1231 * @param int $fieldOffset (optional)
1232 *
1233 * @return adoFieldObject
1234 *
1235 * @author KM Newnham
1236 * @date 02/20/2013
1237 */
1238 function fetchField($fieldOffset = -1)
1239 {
1240 return $this->fieldObjectsCache[$fieldOffset];
1241 }
1242
1243 function _seek($row)
1244 {
1245 return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams.
1246 }
1247
1248 // speedup
1249 function MoveNext()
1250 {
1251 if ($this->EOF)
1252 return false;
1253
1254 $this->_currentRow++;
1255
1256 if ($this->_fetch())
1257 return true;
1258 $this->EOF = true;
1259
1260 return false;
1261 }
1262
1263 function _fetch($ignore_fields=false)
1264 {
1265 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
1266 if ($this->fetchMode & ADODB_FETCH_NUM)
1267 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH);
1268 else
1269 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC);
1270
1271 if (is_array($this->fields))
1272 {
1273
1274 if (ADODB_ASSOC_CASE == ADODB_ASSOC_CASE_LOWER)
1275 $this->fields = array_change_key_case($this->fields,CASE_LOWER);
1276 else if (ADODB_ASSOC_CASE == ADODB_ASSOC_CASE_UPPER)
1277 $this->fields = array_change_key_case($this->fields,CASE_UPPER);
1278
1279 }
1280 }
1281 else
1282 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC);
1283
1284 if (!$this->fields)
1285 return false;
1286
1287 return $this->fields;
1288 }
1289
1290 /**
1291 * close() only needs to be called if you are worried about using too much
1292 * memory while your script is running. All associated result memory for
1293 * the specified result identifier will automatically be freed.
1294 *
1295 * @return bool tru if we succeeded in closing down
1296 */
1297 function _close()
1298 {
1299 /*
1300 * If we are closing down a failed query, collect any
1301 * error messages. This is a hack fix to the "close too early"
1302 * problem so this might go away later
1303 */
1304 $this->connection->errorMsg();
1305 if(is_resource($this->_queryID)) {
1306 $rez = sqlsrv_free_stmt($this->_queryID);
1307 $this->_queryID = false;
1308 return $rez;
1309 }
1310
1311 return true;
1312 }
1313
1314 }
1315
1316
1317 class ADORecordSet_array_mssqlnative extends ADORecordSet_array {}
1318
1319 /*
1320 Code Example 1:
1321
1322 select object_name(constid) as constraint_name,
1323 object_name(fkeyid) as table_name,
1324 col_name(fkeyid, fkey) as column_name,
1325 object_name(rkeyid) as referenced_table_name,
1326 col_name(rkeyid, rkey) as referenced_column_name
1327 from sysforeignkeys
1328 where object_name(fkeyid) = x
1329 order by constraint_name, table_name, referenced_table_name, keyno
1330
1331 Code Example 2:
1332 select constraint_name,
1333 column_name,
1334 ordinal_position
1335 from information_schema.key_column_usage
1336 where constraint_catalog = db_name()
1337 and table_name = x
1338 order by constraint_name, ordinal_position
1339
1340 http://www.databasejournal.com/scripts/article.php/1440551
1341 */
Read-only mirror of the Moodle CVS