5 * Requires mssql client. Works on Windows.
6 * https://docs.microsoft.com/sql/connect/php
8 * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
11 * @link https://adodb.org Project's web site and documentation
12 * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
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
21 * @copyright 2000-2013 John Lim
22 * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
25 // security - hide paths
26 if (!defined('ADODB_DIR')) die();
28 if (!function_exists('sqlsrv_configure')) {
29 die("mssqlnative extension not installed");
32 if (!function_exists('sqlsrv_set_error_handling')) {
33 function sqlsrv_set_error_handling($constant) {
34 sqlsrv_configure("WarningsReturnAsErrors", $constant);
37 if (!function_exists('sqlsrv_log_set_severity')) {
38 function sqlsrv_log_set_severity($constant) {
39 sqlsrv_configure("LogSeverity", $constant);
42 if (!function_exists('sqlsrv_log_set_subsystems')) {
43 function sqlsrv_log_set_subsystems($constant) {
44 sqlsrv_configure("LogSubsystems", $constant);
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'";
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
61 var $hasInsertID = true;
62 var $substr = "substring";
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'))";
74 c.isnullable as nullable,
75 c.cdefault as default_value,
77 t.length as type_length,
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
85 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE
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";
100 var $connectionInfo = array('ReturnDatesAsStrings'=>true);
101 var $cachedSchemaFlush = false;
103 var $sequences = false;
104 var $mssql_version = '';
106 function __construct()
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);
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);
123 * Initializes the SQL Server version.
124 * Dies if connected to a non-supported version (2000 and older)
126 function ServerVersion() {
127 $data = $this->ServerInfo();
128 preg_match('/^\d{2}/', $data['version'], $matches);
129 $version = (int)reset($matches);
131 // We only support SQL Server 2005 and up
133 die("SQL SERVER VERSION {$data['version']} NOT SUPPORTED IN mssqlnative DRIVER");
136 $this->mssql_version
= $version;
139 function ServerInfo() {
140 global $ADODB_FETCH_MODE;
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
;
150 $savem = $this->SetFetchMode(ADODB_FETCH_NUM
);
152 $arrServerInfo = sqlsrv_server_info($this->_connectionID
);
153 $ADODB_FETCH_MODE = $savem;
154 $arr['description'] = $arrServerInfo['SQLServerName'].' connected to '.$arrServerInfo['CurrentDatabase'];
155 $arr['version'] = $arrServerInfo['SQLServerVersion'];//ADOConnection::_findvers($arr['description']);
159 function IfNull( $field, $ifNull )
161 return " ISNULL($field, $ifNull) "; // if MS SQL Server
164 public function enableLastInsertID($enable = true) {
165 $this->hasInsertID
= $enable;
166 $this->lastInsID
= false;
170 * Get the last value inserted into an IDENTITY column.
172 * The value will actually be set in {@see _query()} when executing an
173 * INSERT statement, but only if the connection's $hasInsertId property
174 * is true; this can be set with {@see enableLastInsertId()}.
178 protected function _insertID($table = '', $column = '')
180 return $this->lastInsID
;
183 function _affectedrows()
186 return sqlsrv_rows_affected($this->_queryID
);
189 function GenID($seq='adodbseq',$start=1) {
190 switch($this->mssql_version
){
193 return $this->GenID2008($seq, $start);
196 return $this->GenID2012($seq, $start);
201 function CreateSequence($seq='adodbseq',$start=1)
203 switch($this->mssql_version
){
206 return $this->CreateSequence2008($seq, $start);
209 return $this->CreateSequence2012($seq, $start);
215 * For Server 2005,2008, duplicate a sequence with an identity table
217 function CreateSequence2008($seq='adodbseq',$start=1)
219 if($this->debug
) ADOConnection
::outp("<hr>CreateSequence($seq,$start)");
220 sqlsrv_begin_transaction($this->_connectionID
);
222 $this->Execute("create table $seq (id int)");//was float(53)
223 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
225 if($this->debug
) ADOConnection
::outp("<hr>Error: ROLLBACK");
226 sqlsrv_rollback($this->_connectionID
);
229 sqlsrv_commit($this->_connectionID
);
234 * Proper Sequences Only available to Server 2012 and up
236 function CreateSequence2012($seq='adodbseq',$start=1){
237 if (!$this->sequences
){
238 $sql = "SELECT name FROM sys.sequences";
239 $this->sequences
= $this->GetCol($sql);
241 $ok = $this->Execute("CREATE SEQUENCE $seq START WITH $start INCREMENT BY 1");
243 die("CANNOT CREATE SEQUENCE" . print_r(sqlsrv_errors(),true));
244 $this->sequences
[] = $seq;
248 * For Server 2005,2008, duplicate a sequence with an identity table
250 function GenID2008($seq='adodbseq',$start=1)
252 if($this->debug
) ADOConnection
::outp("<hr>CreateSequence($seq,$start)");
253 sqlsrv_begin_transaction($this->_connectionID
);
254 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
257 $this->Execute("create table $seq (id int)");//was float(53)
258 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
260 if($this->debug
) ADOConnection
::outp("<hr>Error: ROLLBACK");
261 sqlsrv_rollback($this->_connectionID
);
265 $num = $this->GetOne("select id from $seq");
266 sqlsrv_commit($this->_connectionID
);
270 * Only available to Server 2012 and up
271 * Cannot do this the normal adodb way by trapping an error if the
272 * sequence does not exist because sql server will auto create a
273 * sequence with the starting number of -9223372036854775808
275 function GenID2012($seq='adodbseq',$start=1)
279 * First time in create an array of sequence names that we
280 * can use in later requests to see if the sequence exists
281 * the overhead is creating a list of sequences every time
282 * we need access to at least 1. If we really care about
283 * performance, we could maybe flag a 'nocheck' class variable
285 if (!$this->sequences
){
286 $sql = "SELECT name FROM sys.sequences";
287 $this->sequences
= $this->GetCol($sql);
289 if (!is_array($this->sequences
)
290 ||
is_array($this->sequences
) && !in_array($seq,$this->sequences
)){
291 $this->CreateSequence2012($seq, $start);
294 $num = $this->GetOne("SELECT NEXT VALUE FOR $seq");
298 // Format date column in sql string given an input format that understands Y M D
299 function SQLDate($fmt, $col=false)
302 $col = $this->sysTimeStamp
;
306 $ConvertableFmt=array(
307 "m/d/Y"=>101, "m/d/y"=>101 // US
308 ,"Y.m.d"=>102, "y.m.d"=>102 // ANSI
309 ,"d/m/Y"=>103, "d/m/y"=>103 // French /english
310 ,"d.m.Y"=>104, "d.m.y"=>104 // German
311 ,"d-m-Y"=>105, "d-m-y"=>105 // Italian
312 ,"m-d-Y"=>110, "m-d-y"=>110 // US Dash
313 ,"Y/m/d"=>111, "y/m/d"=>111 // Japan
314 ,"Ymd"=>112, "ymd"=>112 // ISO
315 ,"H:i:s"=>108 // Time
317 if (key_exists($fmt,$ConvertableFmt)) {
318 return "convert (varchar ,$col," . $ConvertableFmt[$fmt] . ")";
322 for ($i=0; $i < $len; $i++
) {
328 $s .= "datename(yyyy,$col)";
331 $s .= "convert(char(3),$col,0)";
334 $s .= "replace(str(month($col),2),' ','0')";
338 $s .= "datename(quarter,$col)";
342 $s .= "replace(str(day($col),2),' ','0')";
345 $s .= "substring(convert(char(14),$col,0),13,2)";
349 $s .= "replace(str(datepart(hh,$col),2),' ','0')";
353 $s .= "replace(str(datepart(mi,$col),2),' ','0')";
356 $s .= "replace(str(datepart(ss,$col),2),' ','0')";
360 $s .= "substring(convert(char(19),$col,0),18,2)";
363 $s .= "datename(dw,$col)";
368 $ch = substr($fmt,$i,1);
370 $s .= $this->qstr($ch);
378 function BeginTrans()
380 if ($this->transOff
) return true;
381 $this->transCnt +
= 1;
382 if ($this->debug
) ADOConnection
::outp('<hr>begin transaction');
383 sqlsrv_begin_transaction($this->_connectionID
);
387 function CommitTrans($ok=true)
389 if ($this->transOff
) return true;
390 if ($this->debug
) ADOConnection
::outp('<hr>commit transaction');
391 if (!$ok) return $this->RollbackTrans();
392 if ($this->transCnt
) $this->transCnt
-= 1;
393 sqlsrv_commit($this->_connectionID
);
397 function RollbackTrans()
399 if ($this->transOff
) return true;
400 if ($this->debug
) ADOConnection
::outp('<hr>rollback transaction');
401 if ($this->transCnt
) $this->transCnt
-= 1;
402 sqlsrv_rollback($this->_connectionID
);
406 function SetTransactionMode( $transaction_mode )
408 $this->_transmode
= $transaction_mode;
409 if (empty($transaction_mode)) {
410 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
413 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
414 $this->Execute("SET TRANSACTION ".$transaction_mode);
421 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
423 # some operation on both tables table1 and table2
425 $this->CommitTrans();
427 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
429 function RowLock($tables,$where,$col='1 as adodbignore')
431 if ($col == '1 as adodbignore') $col = 'top 1 null as ignore';
432 if (!$this->transCnt
) $this->BeginTrans();
433 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where");
436 function SelectDB($dbName)
438 $this->database
= $dbName;
439 $this->databaseName
= $dbName; # obsolete, retained for compat with older adodb versions
440 if ($this->_connectionID
) {
441 $rs = $this->Execute('USE '.$dbName);
451 $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL
);
452 if($retErrors != null) {
453 foreach($retErrors as $arrError) {
454 $this->_errorMsg
.= "SQLState: ".$arrError[ 'SQLSTATE']."\n";
455 $this->_errorMsg
.= "Error Code: ".$arrError[ 'code']."\n";
456 $this->_errorMsg
.= "Message: ".$arrError[ 'message']."\n";
459 return $this->_errorMsg
;
464 $err = sqlsrv_errors(SQLSRV_ERR_ALL
);
466 return $err[0]['code'];
471 // returns true or false
472 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
474 if (!function_exists('sqlsrv_connect'))
477 ADOConnection
::outp('Microsoft SQL Server native driver (mssqlnative) not installed');
481 if (!empty($this->port
))
485 $argHostname .= ",".$this->port
;
487 $connectionInfo = $this->connectionInfo
;
488 $connectionInfo["Database"] = $argDatabasename;
489 if ((string)$argUsername != '' ||
(string)$argPassword != '')
492 * If they pass either a userid or password, we assume
493 * SQL Server authentication
495 $connectionInfo["UID"] = $argUsername;
496 $connectionInfo["PWD"] = $argPassword;
499 ADOConnection
::outp('userid or password supplied, attempting connection with SQL Server Authentication');
505 * If they don't pass either value, we won't add them to the
506 * connection parameters. This will then force an attempt
507 * to use windows authentication
511 ADOConnection
::outp('No userid or password supplied, attempting connection with Windows Authentication');
516 * Now merge in the passed connection parameters setting
518 foreach ($this->connectionParameters
as $options)
520 foreach($options as $parameter=>$value)
521 $connectionInfo[$parameter] = $value;
524 if ($this->debug
) ADOConnection
::outp("connecting to host: $argHostname params: ".var_export($connectionInfo,true));
525 if(!($this->_connectionID
= @sqlsrv_connect
($argHostname,$connectionInfo)))
528 ADOConnection
::outp( 'Connection Failed: '.print_r( sqlsrv_errors(), true));
532 $this->ServerVersion();
537 // returns true or false
538 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
540 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!)
541 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
545 function Prepare($sql)
547 return $sql; // prepare does not work properly with bind parameters as bind parameters are managed by sqlsrv_prepare!
550 // returns concatenated string
551 // MSSQL requires integers to be cast as strings
552 // automatically cast every datatype to VARCHAR(255)
553 // @author David Rogers (introspectshun)
557 $arr = func_get_args();
559 // Split single record on commas, if possible
560 if (sizeof($arr) == 1) {
561 foreach ($arr as $arg) {
562 $args = explode(',', $arg);
569 function(&$value, $key) {
570 $value = "CAST(" . $value . " AS VARCHAR(255))";
573 $s = implode('+',$arr);
574 if (sizeof($arr) > 0) return "$s";
580 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
581 So all your blobs must be of type "image".
583 Remember to set in php.ini the following...
585 ; Valid range 0 - 2147483647. Default = 4096.
586 mssql.textlimit = 0 ; zero to pass through
588 ; Valid range 0 - 2147483647. Default = 4096.
589 mssql.textsize = 0 ; zero to pass through
591 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
594 if (strtoupper($blobtype) == 'CLOB') {
595 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
596 return $this->Execute($sql) != false;
598 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
599 return $this->Execute($sql) != false;
605 * If executing an INSERT statement and $hasInsertId is true, will set
609 * @param array $inputarr
610 * @return resource|false Query Id if successful, otherwise false
612 function _query($sql, $inputarr = false)
614 $this->_errorMsg
= false;
616 if (is_array($sql)) {
620 // Handle native driver flaw for retrieving the last insert ID
621 if ($this->hasInsertID
) {
622 // Check if it's an INSERT statement
623 $retrieveLastInsertID = preg_match(
624 '/^\W*insert[\s\w()[\]",.]+values\s*\((?:[^;\']|\'\'|(?:(?:\'\')*\'[^\']+\'(?:\'\')*))*;?$/i',
627 if ($retrieveLastInsertID) {
628 // Append the identity SQL, so it is executed in the same
629 // scope as the insert query.
630 $sql .= '; ' . $this->identitySQL
;
633 $retrieveLastInsertID = false;
637 // Ensure that the input array is indexed numerically, as required
638 // by sqlsrv_query(). If param() was used to create portable binds
639 // then the array might be associative.
640 $inputarr = array_values($inputarr);
641 $rez = sqlsrv_query($this->_connectionID
, $sql, $inputarr);
643 $rez = sqlsrv_query($this->_connectionID
, $sql);
647 ADOConnection
::outp("<hr>running query: " . var_export($sql, true)
648 . "<hr>input array: " . var_export($inputarr, true)
649 . "<hr>result: " . var_export($rez, true)
653 $this->lastInsID
= false;
656 } elseif ($retrieveLastInsertID) {
657 // Get the inserted id from the last result
658 // Note: loop is required as server may return more than one row,
659 // e.g. if triggers are involved (see #41)
660 while (sqlsrv_next_result($rez)) {
662 $this->lastInsID
= sqlsrv_get_field($rez, 0, SQLSRV_PHPTYPE_INT
);
668 // returns true or false
671 if ($this->transCnt
) {
672 $this->RollbackTrans();
674 if($this->_connectionID
) {
675 $rez = sqlsrv_close($this->_connectionID
);
677 $this->_connectionID
= false;
682 function MetaIndexes($table,$primary=false, $owner = false)
684 $table = $this->qstr($table);
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";
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);
702 $rs = $this->Execute($sql);
704 $this->SetFetchMode($savem);
706 $ADODB_FETCH_MODE = $save;
708 if (!is_object($rs)) {
713 while ($row = $rs->FetchRow()) {
714 if (!$primary && $row[5]) continue;
716 $indexes[$row[0]]['unique'] = $row[6];
717 $indexes[$row[0]]['columns'][] = $row[1];
722 function MetaForeignKeys($table, $owner=false, $upper=false)
724 global $ADODB_FETCH_MODE;
726 $save = $ADODB_FETCH_MODE;
727 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
728 $table = $this->qstr(strtoupper($table));
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
736 where upper(object_name(fkeyid)) = $table
737 order by constraint_name, referenced_table_name, keyno";
739 $constraints = $this->GetArray($sql);
741 $ADODB_FETCH_MODE = $save;
744 foreach($constraints as $constr) {
746 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
748 if (!$arr) return false;
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);
765 //From: Fernando Moreira <FMoreira@imediata.pt>
766 function MetaDatabases()
768 $this->SelectDB("master");
769 $rs = $this->Execute($this->metaDatabasesSQL
);
770 $rows = $rs->GetRows();
772 for($i=0;$i<count($rows);$i++
) {
773 $ret[] = $rows[$i][0];
775 $this->SelectDB($this->database
);
782 // "Stein-Aksel Basma" <basma@accelero.no>
783 // tested with MSSQL 2000
784 function MetaPrimaryKeys($table, $owner=false)
786 global $ADODB_FETCH_MODE;
789 $this->_findschema($table,$schema);
790 if (!$schema) $schema = $this->database
;
791 if ($schema) $schema = "and k.table_catalog like '$schema%'";
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 ";
798 $savem = $ADODB_FETCH_MODE;
799 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
800 $a = $this->GetCol($sql);
801 $ADODB_FETCH_MODE = $savem;
803 if ($a && sizeof($a)>0) return $a;
809 function MetaTables($ttype=false,$showSchema=false,$mask=false)
812 $save = $this->metaTablesSQL
;
813 $mask = $this->qstr(($mask));
814 $this->metaTablesSQL
.= " AND name like $mask";
816 $ret = ADOConnection
::MetaTables($ttype,$showSchema);
819 $this->metaTablesSQL
= $save;
823 function MetaColumns($table, $upper=true, $schema=false){
826 * A simple caching mechanism, to be replaced in ADOdb V6
828 static $cached_columns = array();
829 if ($this->cachedSchemaFlush
)
830 $cached_columns = array();
832 if (array_key_exists($table,$cached_columns)){
833 return $cached_columns[$table];
837 $this->_findschema($table,$schema);
839 $dbName = $this->database
;
840 $this->SelectDB($schema);
842 global $ADODB_FETCH_MODE;
843 $save = $ADODB_FETCH_MODE;
844 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
846 if ($this->fetchMode
!== false) $savem = $this->SetFetchMode(false);
847 $rs = $this->Execute(sprintf($this->metaColumnsSQL
,$table));
850 $this->SelectDB($dbName);
853 if (isset($savem)) $this->SetFetchMode($savem);
854 $ADODB_FETCH_MODE = $save;
855 if (!is_object($rs)) {
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];
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'];
888 if ($save == ADODB_FETCH_NUM
)
891 $retarr[strtoupper($fld->name
)] = $fld;
897 $cached_columns[$table] = $retarr;
903 * Returns a substring of a varchar type field
905 * The SQL server version varies because the length is mandatory, so
906 * we append a reasonable string length
908 * @param string $fld The field to sub-string
909 * @param int $start The start point
910 * @param int $length An optional length
912 * @return The SQL text
914 function substr($fld,$start,$length=0)
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
924 $text = "SUBSTRING($fld,$start,$length)";
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.
937 return ADODB_STRINGMAX_NOLIMIT
;
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.
949 return ADODB_STRINGMAX_NOLIMIT
;
952 * Lists procedures, functions and methods in an array.
954 * @param string $procedureNamePattern (optional)
955 * @param string $catalog (optional)
956 * @param string $schemaPattern (optional)
958 * @return array of stored objects in current database.
961 public function metaProcedures($procedureNamePattern = null, $catalog = null, $schemaPattern = null)
963 $metaProcedures = array();
968 if ($procedureNamePattern)
969 $procedureSQL = "AND ROUTINE_NAME LIKE " . strtoupper($this->qstr($procedureNamePattern));
972 $catalogSQL = "AND SPECIFIC_SCHEMA=" . strtoupper($this->qstr($catalog));
975 $schemaSQL = "AND ROUTINE_SCHEMA LIKE {$this->qstr($schemaPattern)}";
977 $fields = " ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_SCHEMA,ROUTINE_CATALOG";
979 $SQL = "SELECT $fields
980 FROM {$this->database}.information_schema.routines
985 ORDER BY ROUTINE_NAME
988 $result = $this->execute($SQL);
992 while ($r = $result->fetchRow()){
997 $r = array_values($r);
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,
1009 return $metaProcedures;
1014 /*--------------------------------------------------------------------------------------
1015 Class Name: Recordset
1016 --------------------------------------------------------------------------------------*/
1018 class ADORecordset_mssqlnative
extends ADORecordSet
{
1020 var $databaseType = "mssqlnative";
1021 var $canSeek = false;
1022 var $fieldOffset = 0;
1023 // _mths works only in non-localised system
1026 * @var bool True if we have retrieved the fields metadata
1028 private $fieldObjectsRetrieved = false;
1031 * Cross-reference the objects by name for easy access
1033 private $fieldObjectsIndex = array();
1036 * Cross references the dateTime objects for faster decoding
1038 private $dateTimeObjects = array();
1041 * flags that we have dateTimeObjects to handle
1043 private $hasDateTimeObjects = false;
1046 * This is cross reference between how the types are stored
1047 * in SQL Server and their english-language description
1048 * -154 is a time field, see #432
1050 private $_typeConversion = array(
1051 -155 => 'datetimeoffset',
1055 -11 => 'uniqueidentifier',
1081 function __construct($id,$mode=false)
1083 if ($mode === false) {
1084 global $ADODB_FETCH_MODE;
1085 $mode = $ADODB_FETCH_MODE;
1088 $this->fetchMode
= $mode;
1089 parent
::__construct($id);
1095 $this->_numOfRows
= -1;//not supported
1096 // Cache the metadata right now
1097 $this->_fetchField();
1102 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
1103 // get next resultset - requires PHP 4.0.5 or later
1104 function NextRecordSet()
1106 if (!sqlsrv_next_result($this->_queryID
)) return false;
1107 $this->_inited
= false;
1108 $this->bind
= false;
1109 $this->_currentRow
= -1;
1114 /* Use associative array to get fields array */
1115 function Fields($colname)
1117 if (!is_array($this->fields
))
1122 if ($this->fetchMode
!= ADODB_FETCH_NUM
)
1123 return $this->fields
[$colname];
1126 $this->bind
= array();
1127 for ($i=0; $i < $this->_numOfFields
; $i++
) {
1128 $o = $this->FetchField($i);
1129 $this->bind
[strtoupper($o->name
)] = $i;
1133 return $this->fields
[$this->bind
[strtoupper($colname)]];
1137 * Returns: an object containing field information.
1139 * Get column information in the Recordset object. fetchField()
1140 * can be used in order to obtain information about fields in a
1141 * certain query result. If the field offset isn't specified,
1142 * the next field that wasn't yet retrieved by fetchField()
1145 * @param int $fieldOffset (optional default=-1 for all
1146 * @return mixed an ADOFieldObject, or array of objects
1148 private function _fetchField($fieldOffset = -1)
1150 if ($this->fieldObjectsRetrieved
) {
1151 if ($this->fieldObjectsCache
) {
1152 // Already got the information
1153 if ($fieldOffset == -1) {
1154 return $this->fieldObjectsCache
;
1156 return $this->fieldObjectsCache
[$fieldOffset];
1159 // No metadata available
1164 $this->fieldObjectsRetrieved
= true;
1166 * Retrieve all metadata in one go. This is always returned as a
1169 $fieldMetaData = sqlsrv_field_metadata($this->_queryID
);
1171 if (!$fieldMetaData) {
1172 // Not a statement that gives us metaData
1176 $this->_numOfFields
= count($fieldMetaData);
1177 foreach ($fieldMetaData as $key=>$value) {
1178 $fld = new ADOFieldObject
;
1179 // Caution - keys are case-sensitive, must respect casing of values
1180 $fld->name
= $value['Name'];
1181 $fld->max_length
= $value['Size'];
1182 $fld->column_source
= $value['Name'];
1183 $fld->type
= $this->_typeConversion
[$value['Type']];
1185 $this->fieldObjectsCache
[$key] = $fld;
1186 $this->fieldObjectsIndex
[$fld->name
] = $key;
1188 if ($fieldOffset == -1) {
1189 return $this->fieldObjectsCache
;
1192 return $this->fieldObjectsCache
[$fieldOffset];
1196 * Fetchfield copies the oracle method, it loads the field information
1197 * into the _fieldobjs array once, to save multiple calls to the
1198 * sqlsrv_field_metadata function
1200 * @param int $fieldOffset (optional)
1202 * @return adoFieldObject
1204 * @author KM Newnham
1207 function fetchField($fieldOffset = -1)
1209 return $this->fieldObjectsCache
[$fieldOffset];
1212 function _seek($row)
1214 return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams.
1223 $this->_currentRow++
;
1225 if ($this->_fetch())
1232 function _fetch($ignore_fields=false)
1234 if ($this->fetchMode
& ADODB_FETCH_ASSOC
) {
1235 if ($this->fetchMode
& ADODB_FETCH_NUM
)
1236 $this->fields
= @sqlsrv_fetch_array
($this->_queryID
,SQLSRV_FETCH_BOTH
);
1238 $this->fields
= @sqlsrv_fetch_array
($this->_queryID
,SQLSRV_FETCH_ASSOC
);
1240 if (is_array($this->fields
))
1243 if (ADODB_ASSOC_CASE
== ADODB_ASSOC_CASE_LOWER
)
1244 $this->fields
= array_change_key_case($this->fields
,CASE_LOWER
);
1245 else if (ADODB_ASSOC_CASE
== ADODB_ASSOC_CASE_UPPER
)
1246 $this->fields
= array_change_key_case($this->fields
,CASE_UPPER
);
1251 $this->fields
= @sqlsrv_fetch_array
($this->_queryID
,SQLSRV_FETCH_NUMERIC
);
1256 return $this->fields
;
1260 * close() only needs to be called if you are worried about using too much
1261 * memory while your script is running. All associated result memory for
1262 * the specified result identifier will automatically be freed.
1264 * @return bool tru if we succeeded in closing down
1269 * If we are closing down a failed query, collect any
1270 * error messages. This is a hack fix to the "close too early"
1271 * problem so this might go away later
1273 $this->connection
->errorMsg();
1274 if(is_resource($this->_queryID
)) {
1275 $rez = sqlsrv_free_stmt($this->_queryID
);
1276 $this->_queryID
= false;
1286 class ADORecordSet_array_mssqlnative
extends ADORecordSet_array
{}
1291 select object_name(constid) as constraint_name,
1292 object_name(fkeyid) as table_name,
1293 col_name(fkeyid, fkey) as column_name,
1294 object_name(rkeyid) as referenced_table_name,
1295 col_name(rkeyid, rkey) as referenced_column_name
1297 where object_name(fkeyid) = x
1298 order by constraint_name, table_name, referenced_table_name, keyno
1301 select constraint_name,
1304 from information_schema.key_column_usage
1305 where constraint_catalog = db_name()
1307 order by constraint_name, ordinal_position
1309 http://www.databasejournal.com/scripts/article.php/1440551