3 V5.10 10 Nov 2009 (c) 2000-2009 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.
9 Latest version is available at http://adodb.sourceforge.net
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
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
20 // security - hide paths
21 if (!defined('ADODB_DIR')) die();
23 if (!function_exists('sqlsrv_configure')) {
24 die("mssqlnative extension not installed");
27 if (!function_exists('sqlsrv_set_error_handling')) {
28 function sqlsrv_set_error_handling($constant)
30 sqlsrv_configure("WarningsReturnAsErrors", $constant);
33 if (!function_exists('sqlsrv_log_set_severity')) {
34 function sqlsrv_log_set_severity($constant)
36 sqlsrv_configure("LogSeverity", $constant);
39 if (!function_exists('sqlsrv_log_set_subsystems')) {
40 function sqlsrv_log_set_subsystems($constant)
42 sqlsrv_configure("LogSubsystems", $constant);
46 //----------------------------------------------------------------
47 // MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002
48 // and this causes tons of problems because localized versions of
49 // MSSQL will return the dates in dmy or mdy order; and also the
50 // month strings depends on what language has been configured. The
51 // following two variables allow you to control the localization
54 // MORE LOCALIZATION INFO
55 // ----------------------
56 // To configure datetime, look for and modify sqlcommn.loc,
57 // typically found in c:\mssql\install
59 // http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918
61 // CONVERT(char(12),datecol,120)
63 // Also if your month is showing as month-1,
64 // e.g. Jan 13, 2002 is showing as 13/0/2002, then see
65 // http://phplens.com/lens/lensforum/msgs.php?id=7048&x=1
66 // it's a localisation problem.
67 //----------------------------------------------------------------
70 // has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc
71 if (ADODB_PHPVER >= 0x4300) {
72 // docs say 4.2.0, but testing shows only since 4.3.0 does it work!
73 ini_set('mssql.datetimeconvert', 0);
75 global $ADODB_mssql_mths; // array, months must be upper-case
76 $ADODB_mssql_date_order = 'mdy';
77 $ADODB_mssql_mths = array(
78 'JAN' => 1, 'FEB' => 2, 'MAR' => 3, 'APR' => 4, 'MAY' => 5, 'JUN' => 6,
79 'JUL' => 7, 'AUG' => 8, 'SEP' => 9, 'OCT' => 10, 'NOV' => 11, 'DEC' => 12);
82 //---------------------------------------------------------------------------
83 // Call this to autoset $ADODB_mssql_date_order at the beginning of your code,
84 // just after you connect to the database. Supports mdy and dmy only.
85 // Not required for PHP 4.2.0 and above.
86 function AutoDetect_MSSQL_Date_Order($conn)
88 global $ADODB_mssql_date_order;
89 $adate = $conn->GetOne('select getdate()');
94 //ADOConnection::outp( "MSSQL: YYYY-MM-DD date format not supported currently");
96 $ADODB_mssql_date_order = 'dmy';
98 $ADODB_mssql_date_order = 'mdy';
102 class ADODB_mssqlnative extends ADOConnection
104 var $databaseType = "mssqlnative";
105 var $dataProvider = "mssqlnative";
106 var $replaceQuote = "''"; // string to use to replace quotes
107 var $fmtDate = "'Y-m-d'";
108 var $fmtTimeStamp = "'Y-m-d H:i:s'";
109 var $hasInsertID = true;
110 var $substr = "substring";
112 var $hasAffectedRows = true;
113 var $poorAffectedRows = false;
114 var $metaDatabasesSQL = "select name from sys.sysdatabases where name <> 'master'";
115 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'))";
116 var $metaColumnsSQL = # xtype==61 is datetime
117 "select c.name,t.name,c.length,
118 (case when c.xusertype=61 then 0 else c.xprec end),
119 (case when c.xusertype=61 then 0 else c.xscale end)
120 from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'";
121 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE
122 var $hasGenID = true;
123 var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)';
124 var $sysTimeStamp = 'GetDate()';
125 var $maxParameterLen = 4000;
126 var $arrayClass = 'ADORecordSet_array_mssqlnative';
127 var $uniqueSort = true;
128 var $leftOuter = '*=';
129 var $rightOuter = '=*';
130 var $ansiOuter = true; // for mssql7 or later
131 var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000
132 var $uniqueOrderBy = true;
133 var $_bindInputArray = true;
134 var $_dropSeqSQL = "drop table %s";
136 function ADODB_mssqlnative()
140 sqlsrv_set_error_handling(SQLSRV_ERRORS_LOG_ALL);
141 sqlsrv_log_set_severity(SQLSRV_LOG_SEVERITY_ALL);
142 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
143 sqlsrv_configure('warnings_return_as_errors', 0);
145 sqlsrv_set_error_handling(0);
146 sqlsrv_log_set_severity(0);
147 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
148 sqlsrv_configure('warnings_return_as_errors', 0);
152 function ServerInfo()
154 global $ADODB_FETCH_MODE;
155 if ($this->fetchMode === false) {
156 $savem = $ADODB_FETCH_MODE;
157 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
159 $savem = $this->SetFetchMode(ADODB_FETCH_NUM);
160 $arrServerInfo = sqlsrv_server_info($this->_connectionID);
161 $arr['description'] = $arrServerInfo['SQLServerName'] . ' connected to ' . $arrServerInfo['CurrentDatabase'];
162 $arr['version'] = $arrServerInfo['SQLServerVersion']; //ADOConnection::_findvers($arr['description']);
166 function IfNull($field, $ifNull)
168 return " ISNULL($field, $ifNull) "; // if MS SQL Server
174 // Returns the last IDENTITY value inserted into an IDENTITY column in
175 // the same scope. A scope is a module -- a stored procedure, trigger,
176 // function, or batch. Thus, two statements are in the same scope if
177 // they are in the same stored procedure, function, or batch.
178 return $this->GetOne($this->identitySQL);
181 function _affectedrows()
183 return sqlsrv_rows_affected($this->_queryID);
186 function CreateSequence($seq = 'adodbseq', $start = 1)
188 if ($this->debug) error_log("<hr>CreateSequence($seq,$start)");
189 sqlsrv_begin_transaction($this->_connectionID);
191 $this->Execute("create table $seq (id int)"); //was float(53)
192 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
194 if ($this->debug) error_log("<hr>Error: ROLLBACK");
195 sqlsrv_rollback($this->_connectionID);
198 sqlsrv_commit($this->_connectionID);
202 function GenID($seq = 'adodbseq', $start = 1)
204 if ($this->debug) error_log("<hr>GenID($seq,$start)");
205 sqlsrv_begin_transaction($this->_connectionID);
206 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
208 $this->Execute("create table $seq (id int)");
209 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
211 if ($this->debug) error_log("<hr>Error: ROLLBACK");
212 sqlsrv_rollback($this->_connectionID);
215 sqlsrv_commit($this->_connectionID);
218 $num = $this->GetOne("select id from $seq");
219 sqlsrv_commit($this->_connectionID);
220 if ($this->debug) error_log(" Returning: $num");
224 // Format date column in sql string given an input format that understands Y M D
225 function SQLDate($fmt, $col = false)
227 if (!$col) $col = $this->sysTimeStamp;
231 for ($i = 0; $i < $len; $i++) {
237 $s .= "datename(yyyy,$col)";
240 $s .= "convert(char(3),$col,0)";
243 $s .= "replace(str(month($col),2),' ','0')";
247 $s .= "datename(quarter,$col)";
251 $s .= "replace(str(day($col),2),' ','0')";
254 $s .= "substring(convert(char(14),$col,0),13,2)";
258 $s .= "replace(str(datepart(hh,$col),2),' ','0')";
262 $s .= "replace(str(datepart(mi,$col),2),' ','0')";
265 $s .= "replace(str(datepart(ss,$col),2),' ','0')";
269 $s .= "substring(convert(char(19),$col,0),18,2)";
275 $ch = substr($fmt, $i, 1);
277 $s .= $this->qstr($ch);
285 function BeginTrans()
287 if ($this->transOff) return true;
288 $this->transCnt += 1;
289 if ($this->debug) error_log('<hr>begin transaction');
290 sqlsrv_begin_transaction($this->_connectionID);
294 function CommitTrans($ok = true)
296 if ($this->transOff) return true;
297 if ($this->debug) error_log('<hr>commit transaction');
298 if (!$ok) return $this->RollbackTrans();
299 if ($this->transCnt) $this->transCnt -= 1;
300 sqlsrv_commit($this->_connectionID);
304 function RollbackTrans()
306 if ($this->transOff) return true;
307 if ($this->debug) error_log('<hr>rollback transaction');
308 if ($this->transCnt) $this->transCnt -= 1;
309 sqlsrv_rollback($this->_connectionID);
313 function SetTransactionMode($transaction_mode)
315 $this->_transmode = $transaction_mode;
316 if (empty($transaction_mode)) {
317 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
320 if (!stristr($transaction_mode, 'isolation')) $transaction_mode = 'ISOLATION LEVEL ' . $transaction_mode;
321 $this->Execute("SET TRANSACTION " . $transaction_mode);
328 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
330 # some operation on both tables table1 and table2
332 $this->CommitTrans();
334 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
336 function RowLock($tables, $where, $col = 'top 1 null as ignore')
338 if (!$this->transCnt) $this->BeginTrans();
339 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where");
342 function SelectDB($dbName)
344 $this->database = $dbName;
345 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
346 if ($this->_connectionID) {
347 $rs = $this->Execute('USE ' . $dbName);
356 $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL);
357 if ($retErrors != null) {
358 foreach ($retErrors as $arrError) {
359 $this->_errorMsg .= "SQLState: " . $arrError['SQLSTATE'] . "\n";
360 $this->_errorMsg .= "Error Code: " . $arrError['code'] . "\n";
361 $this->_errorMsg .= "Message: " . $arrError['message'] . "\n";
364 $this->_errorMsg = "No errors found";
366 return $this->_errorMsg;
371 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
372 $err = sqlsrv_errors(SQLSRV_ERR_ALL);
373 if ($err[0]) return $err[0]['code'];
377 // returns true or false
378 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
380 if (!function_exists('sqlsrv_connect')) return null;
381 $connectionInfo = array("Database" => $argDatabasename, 'UID' => $argUsername, 'PWD' => $argPassword);
382 if ($this->debug) error_log("<hr>connecting... hostname: $argHostname params: " . var_export($connectionInfo, true));
383 //if ($this->debug) error_log("<hr>_connectionID before: ".serialize($this->_connectionID));
384 if (!($this->_connectionID = sqlsrv_connect($argHostname, $connectionInfo))) {
385 if ($this->debug) error_log("<hr><b>errors</b>: " . print_r(sqlsrv_errors(), true));
388 //if ($this->debug) error_log(" _connectionID after: ".serialize($this->_connectionID));
389 //if ($this->debug) error_log("<hr>defined functions: <pre>".var_export(get_defined_functions(),true)."</pre>");
393 // returns true or false
394 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
396 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!)
397 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
400 function Prepare($sql)
402 $stmt = sqlsrv_prepare($this->_connectionID, $sql);
403 if (!$stmt) return $sql;
404 return array($sql, $stmt);
407 // returns concatenated string
408 // MSSQL requires integers to be cast as strings
409 // automatically cast every datatype to VARCHAR(255)
410 // @author David Rogers (introspectshun)
414 $arr = func_get_args();
416 // Split single record on commas, if possible
417 if (sizeof($arr) == 1) {
418 foreach ($arr as $arg) {
419 $args = explode(',', $arg);
424 array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
425 $s = implode('+', $arr);
426 if (sizeof($arr) > 0) return "$s";
432 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
433 So all your blobs must be of type "image".
435 Remember to set in php.ini the following...
437 ; Valid range 0 - 2147483647. Default = 4096.
438 mssql.textlimit = 0 ; zero to pass through
440 ; Valid range 0 - 2147483647. Default = 4096.
441 mssql.textsize = 0 ; zero to pass through
443 function UpdateBlob($table, $column, $val, $where, $blobtype = 'BLOB')
446 if (strtoupper($blobtype) == 'CLOB') {
447 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
448 return $this->Execute($sql) != false;
450 $sql = "UPDATE $table SET $column=0x" . bin2hex($val) . " WHERE $where";
451 return $this->Execute($sql) != false;
454 // returns query ID if successful, otherwise false
455 function _query($sql, $inputarr = false)
457 $this->_errorMsg = false;
458 if (is_array($inputarr)) {
459 $rez = sqlsrv_query($this->_connectionID, $sql, $inputarr);
460 } elseif (is_array($sql)) {
461 $rez = sqlsrv_query($this->_connectionID, $sql[1], $inputarr);
463 $rez = sqlsrv_query($this->_connectionID, $sql);
465 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)); //"<hr>connection: ".serialize($this->_connectionID)
466 //fix for returning true on anything besides select statements
467 if (is_array($sql)) $sql = $sql[1];
469 if (stripos($sql, 'SELECT') !== 0 && $rez !== false) {
470 if ($this->debug) error_log(" isn't a select query, returning boolean true");
474 if (!$rez) $rez = false;
478 // returns true or false
481 if ($this->transCnt) $this->RollbackTrans();
482 $rez = @sqlsrv_close($this->_connectionID);
483 $this->_connectionID = false;
487 // mssql uses a default date like Dec 30 2000 12:00AM
488 function UnixDate($v)
490 return ADORecordSet_array_mssql::UnixDate($v);
493 function UnixTimeStamp($v)
495 return ADORecordSet_array_mssql::UnixTimeStamp($v);
498 function &MetaIndexes($table, $primary = false)
500 $table = $this->qstr($table);
502 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
503 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,
504 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
505 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
506 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
507 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
508 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
509 ORDER BY O.name, I.Name, K.keyno";
511 global $ADODB_FETCH_MODE;
512 $save = $ADODB_FETCH_MODE;
513 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
514 if ($this->fetchMode !== FALSE) {
515 $savem = $this->SetFetchMode(FALSE);
518 $rs = $this->Execute($sql);
520 $this->SetFetchMode($savem);
522 $ADODB_FETCH_MODE = $save;
524 if (!is_object($rs)) {
529 while ($row = $rs->FetchRow()) {
530 if (!$primary && $row[5]) continue;
532 $indexes[$row[0]]['unique'] = $row[6];
533 $indexes[$row[0]]['columns'][] = $row[1];
538 function MetaForeignKeys($table, $owner = false, $upper = false)
540 global $ADODB_FETCH_MODE;
542 $save = $ADODB_FETCH_MODE;
543 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
544 $table = $this->qstr(strtoupper($table));
547 "select object_name(constid) as constraint_name,
548 col_name(fkeyid, fkey) as column_name,
549 object_name(rkeyid) as referenced_table_name,
550 col_name(rkeyid, rkey) as referenced_column_name
552 where upper(object_name(fkeyid)) = $table
553 order by constraint_name, referenced_table_name, keyno";
555 $constraints =& $this->GetArray($sql);
557 $ADODB_FETCH_MODE = $save;
560 foreach ($constraints as $constr) {
562 $arr[$constr[0]][$constr[2]][] = $constr[1] . '=' . $constr[3];
564 if (!$arr) return false;
568 foreach ($arr as $k => $v) {
569 foreach ($v as $a => $b) {
570 if ($upper) $a = strtoupper($a);
577 //From: Fernando Moreira <FMoreira@imediata.pt>
578 function MetaDatabases()
580 $this->SelectDB("master");
581 $rs =& $this->Execute($this->metaDatabasesSQL);
582 $rows = $rs->GetRows();
584 for ($i = 0; $i < count($rows); $i++) {
585 $ret[] = $rows[$i][0];
587 $this->SelectDB($this->database);
594 // "Stein-Aksel Basma" <basma@accelero.no>
595 // tested with MSSQL 2000
596 function &MetaPrimaryKeys($table)
598 global $ADODB_FETCH_MODE;
601 $this->_findschema($table, $schema);
602 if (!$schema) $schema = $this->database;
603 if ($schema) $schema = "and k.table_catalog like '$schema%'";
605 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
606 information_schema.table_constraints tc
607 where tc.constraint_name = k.constraint_name and tc.constraint_type =
608 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
610 $savem = $ADODB_FETCH_MODE;
611 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
612 $a = $this->GetCol($sql);
613 $ADODB_FETCH_MODE = $savem;
615 if ($a && sizeof($a) > 0) return $a;
621 function &MetaTables($ttype = false, $showSchema = false, $mask = false)
624 $save = $this->metaTablesSQL;
625 $mask = $this->qstr(($mask));
626 $this->metaTablesSQL .= " AND name like $mask";
628 $ret =& ADOConnection::MetaTables($ttype, $showSchema);
631 $this->metaTablesSQL = $save;
637 /*--------------------------------------------------------------------------------------
638 Class Name: Recordset
639 --------------------------------------------------------------------------------------*/
641 class ADORecordset_mssqlnative extends ADORecordSet
644 var $databaseType = "mssqlnative";
645 var $canSeek = false;
646 var $fieldOffset = 0;
648 // _mths works only in non-localised system
650 function ADORecordset_mssqlnative($id, $mode = false)
652 if ($mode === false) {
653 global $ADODB_FETCH_MODE;
654 $mode = $ADODB_FETCH_MODE;
657 $this->fetchMode = $mode;
658 return $this->ADORecordSet($id, $mode);
664 global $ADODB_COUNTRECS;
665 if ($this->connection->debug) error_log("(before) ADODB_COUNTRECS: {$ADODB_COUNTRECS} _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
666 /*$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."
667 error_log("rowsaff: ".serialize($retRowsAff));
668 $this->_numOfRows = ($ADODB_COUNTRECS)? $retRowsAff:-1;*/
669 $this->_numOfRows = -1; //not supported
670 $fieldmeta = sqlsrv_field_metadata($this->_queryID);
671 $this->_numOfFields = ($fieldmeta) ? count($fieldmeta) : -1;
672 if ($this->connection->debug) error_log("(after) _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
676 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
677 // get next resultset - requires PHP 4.0.5 or later
678 function NextRecordSet()
680 if (!sqlsrv_next_result($this->_queryID)) return false;
681 $this->_inited = false;
683 $this->_currentRow = -1;
688 /* Use associative array to get fields array */
689 function Fields($colname)
691 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
693 $this->bind = array();
694 for ($i = 0; $i < $this->_numOfFields; $i++) {
695 $o = $this->FetchField($i);
696 $this->bind[strtoupper($o->name)] = $i;
700 return $this->fields[$this->bind[strtoupper($colname)]];
703 /* Returns: an object containing field information.
704 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
705 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
706 fetchField() is retrieved. */
708 function &FetchField($fieldOffset = -1)
710 if ($this->connection->debug) error_log("<hr>fetchfield: $fieldOffset, fetch array: <pre>" . print_r($this->fields, true) . "</pre> backtrace: " . adodb_backtrace(false));
711 if ($fieldOffset != -1) $this->fieldOffset = $fieldOffset;
712 $arrKeys = array_keys($this->fields);
713 if (array_key_exists($this->fieldOffset, $arrKeys) && !array_key_exists($arrKeys[$this->fieldOffset], $this->fields)) {
716 $f = $this->fields[$arrKeys[$this->fieldOffset]];
717 if ($fieldOffset == -1) $this->fieldOffset++;
721 $f = false; //PHP Notice: Only variable references should be returned by reference
728 return false; //There is no support for cursors in the driver at this time. All data is returned via forward-only streams.
734 if ($this->connection->debug) error_log("movenext()");
735 //if ($this->connection->debug) error_log("eof (beginning): ".$this->EOF);
736 if ($this->EOF) return false;
738 $this->_currentRow++;
739 if ($this->connection->debug) error_log("_currentRow: " . $this->_currentRow);
741 if ($this->_fetch()) return true;
743 //if ($this->connection->debug) error_log("eof (end): ".$this->EOF);
749 // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
750 // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
751 function _fetch($ignore_fields = false)
753 if ($this->connection->debug) error_log("_fetch()");
754 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
755 if ($this->fetchMode & ADODB_FETCH_NUM) {
756 if ($this->connection->debug) error_log("fetch mode: both");
757 $this->fields = @sqlsrv_fetch_array($this->_queryID, SQLSRV_FETCH_BOTH);
759 if ($this->connection->debug) error_log("fetch mode: assoc");
760 $this->fields = @sqlsrv_fetch_array($this->_queryID, SQLSRV_FETCH_ASSOC);
763 if (ADODB_ASSOC_CASE == 0) {
764 foreach ($this->fields as $k => $v) {
765 $this->fields[strtolower($k)] = $v;
767 } elseif (ADODB_ASSOC_CASE == 1) {
768 foreach ($this->fields as $k => $v) {
769 $this->fields[strtoupper($k)] = $v;
773 if ($this->connection->debug) error_log("fetch mode: num");
774 $this->fields = @sqlsrv_fetch_array($this->_queryID, SQLSRV_FETCH_NUMERIC);
776 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
778 foreach ($this->fields as $key => $value) {
779 if (is_numeric($key)) {
780 $arrFixed[$key - 1] = $value;
782 $arrFixed[$key] = $value;
785 //if($this->connection->debug) error_log("<hr>fixing non 0 based return array, old: ".print_r($this->fields,true)." new: ".print_r($arrFixed,true));
786 $this->fields = $arrFixed;
788 if (is_array($this->fields)) {
789 foreach ($this->fields as $key => $value) {
790 if (is_object($value) && method_exists($value, 'format')) { //is DateTime object
791 $this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z");
795 if ($this->fields === null) $this->fields = false;
796 if ($this->connection->debug) error_log("<hr>after _fetch, fields: <pre>" . print_r($this->fields, true) . " backtrace: " . adodb_backtrace(false));
797 return $this->fields;
800 /* close() only needs to be called if you are worried about using too much memory while your script
801 is running. All associated result memory for the specified result identifier will automatically be freed. */
804 $rez = sqlsrv_free_stmt($this->_queryID);
805 $this->_queryID = false;
809 // mssql uses a default date like Dec 30 2000 12:00AM
810 function UnixDate($v)
812 return ADORecordSet_array_mssqlnative::UnixDate($v);
815 function UnixTimeStamp($v)
817 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
822 class ADORecordSet_array_mssqlnative extends ADORecordSet_array
824 function ADORecordSet_array_mssqlnative($id = -1, $mode = false)
826 $this->ADORecordSet_array($id, $mode);
829 // mssql uses a default date like Dec 30 2000 12:00AM
830 function UnixDate($v)
833 if (is_numeric(substr($v, 0, 1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
835 global $ADODB_mssql_mths, $ADODB_mssql_date_order;
837 //Dec 30 2000 12:00AM
838 if ($ADODB_mssql_date_order == 'dmy') {
839 if (!preg_match("|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|", $v, $rr)) {
840 return parent::UnixDate($v);
842 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
845 $themth = substr(strtoupper($rr[2]), 0, 3);
847 if (!preg_match("|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|", $v, $rr)) {
848 return parent::UnixDate($v);
850 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
853 $themth = substr(strtoupper($rr[1]), 0, 3);
855 $themth = $ADODB_mssql_mths[$themth];
856 if ($themth <= 0) return false;
858 return mktime(0, 0, 0, $themth, $theday, $rr[3]);
861 function UnixTimeStamp($v)
864 if (is_numeric(substr($v, 0, 1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
866 global $ADODB_mssql_mths, $ADODB_mssql_date_order;
868 //Dec 30 2000 12:00AM
869 if ($ADODB_mssql_date_order == 'dmy') {
870 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})|"
872 ) return parent::UnixTimeStamp($v);
873 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
876 $themth = substr(strtoupper($rr[2]), 0, 3);
878 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})|"
880 ) return parent::UnixTimeStamp($v);
881 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
884 $themth = substr(strtoupper($rr[1]), 0, 3);
887 $themth = $ADODB_mssql_mths[$themth];
888 if ($themth <= 0) return false;
890 switch (strtoupper($rr[6])) {
892 if ($rr[4] < 12) $rr[4] += 12;
895 if ($rr[4] == 12) $rr[4] = 0;
901 return mktime($rr[4], $rr[5], 0, $themth, $theday, $rr[3]);
908 select object_name(constid) as constraint_name,
909 object_name(fkeyid) as table_name,
910 col_name(fkeyid, fkey) as column_name,
911 object_name(rkeyid) as referenced_table_name,
912 col_name(rkeyid, rkey) as referenced_column_name
914 where object_name(fkeyid) = x
915 order by constraint_name, table_name, referenced_table_name, keyno
918 select constraint_name,
921 from information_schema.key_column_usage
922 where constraint_catalog = db_name()
924 order by constraint_name, ordinal_position
926 http://www.databasejournal.com/scripts/article.php/1440551