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) {
29 sqlsrv_configure("WarningsReturnAsErrors", $constant);
32 if (!function_exists('sqlsrv_log_set_severity')) {
33 function sqlsrv_log_set_severity($constant) {
34 sqlsrv_configure("LogSeverity", $constant);
37 if (!function_exists('sqlsrv_log_set_subsystems')) {
38 function sqlsrv_log_set_subsystems($constant) {
39 sqlsrv_configure("LogSubsystems", $constant);
43 //----------------------------------------------------------------
44 // MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002
45 // and this causes tons of problems because localized versions of
46 // MSSQL will return the dates in dmy or mdy order; and also the
47 // month strings depends on what language has been configured. The
48 // following two variables allow you to control the localization
51 // MORE LOCALIZATION INFO
52 // ----------------------
53 // To configure datetime, look for and modify sqlcommn.loc,
54 // typically found in c:\mssql\install
56 // http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918
58 // CONVERT(char(12),datecol,120)
60 // Also if your month is showing as month-1,
61 // e.g. Jan 13, 2002 is showing as 13/0/2002, then see
62 // http://phplens.com/lens/lensforum/msgs.php?id=7048&x=1
63 // it's a localisation problem.
64 //----------------------------------------------------------------
67 // has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc
68 if (ADODB_PHPVER >= 0x4300) {
69 // docs say 4.2.0, but testing shows only since 4.3.0 does it work!
70 ini_set('mssql.datetimeconvert',0);
72 global $ADODB_mssql_mths; // array, months must be upper-case
73 $ADODB_mssql_date_order = 'mdy';
74 $ADODB_mssql_mths = array(
75 'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6,
76 'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12);
79 //---------------------------------------------------------------------------
80 // Call this to autoset $ADODB_mssql_date_order at the beginning of your code,
81 // just after you connect to the database. Supports mdy and dmy only.
82 // Not required for PHP 4.2.0 and above.
83 function AutoDetect_MSSQL_Date_Order($conn)
85 global $ADODB_mssql_date_order;
86 $adate = $conn->GetOne('select getdate()');
91 //ADOConnection::outp( "MSSQL: YYYY-MM-DD date format not supported currently");
93 $ADODB_mssql_date_order = 'dmy';
95 $ADODB_mssql_date_order = 'mdy';
99 class ADODB_mssqlnative extends ADOConnection {
100 var $databaseType = "mssqlnative";
101 var $dataProvider = "mssqlnative";
102 var $replaceQuote = "''"; // string to use to replace quotes
103 var $fmtDate = "'Y-m-d'";
104 var $fmtTimeStamp = "'Y-m-d H:i:s'";
105 var $hasInsertID = true;
106 var $substr = "substring";
108 var $hasAffectedRows = true;
109 var $poorAffectedRows = false;
110 var $metaDatabasesSQL = "select name from sys.sysdatabases where name <> 'master'";
111 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'))";
112 var $metaColumnsSQL = # xtype==61 is datetime
113 "select c.name,t.name,c.length,
114 (case when c.xusertype=61 then 0 else c.xprec end),
115 (case when c.xusertype=61 then 0 else c.xscale end)
116 from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'";
117 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE
118 var $hasGenID = true;
119 var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)';
120 var $sysTimeStamp = 'GetDate()';
121 var $maxParameterLen = 4000;
122 var $arrayClass = 'ADORecordSet_array_mssqlnative';
123 var $uniqueSort = true;
124 var $leftOuter = '*=';
125 var $rightOuter = '=*';
126 var $ansiOuter = true; // for mssql7 or later
127 var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000
128 var $uniqueOrderBy = true;
129 var $_bindInputArray = true;
130 var $_dropSeqSQL = "drop table %s";
132 function ADODB_mssqlnative()
136 sqlsrv_set_error_handling( SQLSRV_ERRORS_LOG_ALL );
137 sqlsrv_log_set_severity( SQLSRV_LOG_SEVERITY_ALL );
138 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
139 sqlsrv_configure('warnings_return_as_errors', 0);
141 sqlsrv_set_error_handling(0);
142 sqlsrv_log_set_severity(0);
143 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
144 sqlsrv_configure('warnings_return_as_errors', 0);
148 function ServerInfo()
150 global $ADODB_FETCH_MODE;
151 if ($this->fetchMode === false) {
152 $savem = $ADODB_FETCH_MODE;
153 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
155 $savem = $this->SetFetchMode(ADODB_FETCH_NUM);
156 $arrServerInfo = sqlsrv_server_info($this->_connectionID);
157 $arr['description'] = $arrServerInfo['SQLServerName'].' connected to '.$arrServerInfo['CurrentDatabase'];
158 $arr['version'] = $arrServerInfo['SQLServerVersion'];//ADOConnection::_findvers($arr['description']);
162 function IfNull( $field, $ifNull )
164 return " ISNULL($field, $ifNull) "; // if MS SQL Server
170 // Returns the last IDENTITY value inserted into an IDENTITY column in
171 // the same scope. A scope is a module -- a stored procedure, trigger,
172 // function, or batch. Thus, two statements are in the same scope if
173 // they are in the same stored procedure, function, or batch.
174 return $this->GetOne($this->identitySQL);
177 function _affectedrows()
179 return sqlsrv_rows_affected($this->_queryID);
182 function CreateSequence($seq='adodbseq',$start=1)
184 if($this->debug) error_log("<hr>CreateSequence($seq,$start)");
185 sqlsrv_begin_transaction($this->_connectionID);
187 $this->Execute("create table $seq (id int)");//was float(53)
188 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
190 if($this->debug) error_log("<hr>Error: ROLLBACK");
191 sqlsrv_rollback($this->_connectionID);
194 sqlsrv_commit($this->_connectionID);
198 function GenID($seq='adodbseq',$start=1)
200 if($this->debug) error_log("<hr>GenID($seq,$start)");
201 sqlsrv_begin_transaction($this->_connectionID);
202 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
204 $this->Execute("create table $seq (id int)");
205 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
207 if($this->debug) error_log("<hr>Error: ROLLBACK");
208 sqlsrv_rollback($this->_connectionID);
211 sqlsrv_commit($this->_connectionID);
214 $num = $this->GetOne("select id from $seq");
215 sqlsrv_commit($this->_connectionID);
216 if($this->debug) error_log(" Returning: $num");
220 // Format date column in sql string given an input format that understands Y M D
221 function SQLDate($fmt, $col=false)
223 if (!$col) $col = $this->sysTimeStamp;
227 for ($i=0; $i < $len; $i++) {
233 $s .= "datename(yyyy,$col)";
236 $s .= "convert(char(3),$col,0)";
239 $s .= "replace(str(month($col),2),' ','0')";
243 $s .= "datename(quarter,$col)";
247 $s .= "replace(str(day($col),2),' ','0')";
250 $s .= "substring(convert(char(14),$col,0),13,2)";
254 $s .= "replace(str(datepart(hh,$col),2),' ','0')";
258 $s .= "replace(str(datepart(mi,$col),2),' ','0')";
261 $s .= "replace(str(datepart(ss,$col),2),' ','0')";
265 $s .= "substring(convert(char(19),$col,0),18,2)";
271 $ch = substr($fmt,$i,1);
273 $s .= $this->qstr($ch);
281 function BeginTrans()
283 if ($this->transOff) return true;
284 $this->transCnt += 1;
285 if ($this->debug) error_log('<hr>begin transaction');
286 sqlsrv_begin_transaction($this->_connectionID);
290 function CommitTrans($ok=true)
292 if ($this->transOff) return true;
293 if ($this->debug) error_log('<hr>commit transaction');
294 if (!$ok) return $this->RollbackTrans();
295 if ($this->transCnt) $this->transCnt -= 1;
296 sqlsrv_commit($this->_connectionID);
299 function RollbackTrans()
301 if ($this->transOff) return true;
302 if ($this->debug) error_log('<hr>rollback transaction');
303 if ($this->transCnt) $this->transCnt -= 1;
304 sqlsrv_rollback($this->_connectionID);
308 function SetTransactionMode( $transaction_mode )
310 $this->_transmode = $transaction_mode;
311 if (empty($transaction_mode)) {
312 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
315 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
316 $this->Execute("SET TRANSACTION ".$transaction_mode);
323 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
325 # some operation on both tables table1 and table2
327 $this->CommitTrans();
329 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
331 function RowLock($tables,$where,$col='top 1 null as ignore')
333 if (!$this->transCnt) $this->BeginTrans();
334 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where");
337 function SelectDB($dbName)
339 $this->database = $dbName;
340 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
341 if ($this->_connectionID) {
342 $rs = $this->Execute('USE '.$dbName);
352 $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL);
353 if($retErrors != null) {
354 foreach($retErrors as $arrError) {
355 $this->_errorMsg .= "SQLState: ".$arrError[ 'SQLSTATE']."\n";
356 $this->_errorMsg .= "Error Code: ".$arrError[ 'code']."\n";
357 $this->_errorMsg .= "Message: ".$arrError[ 'message']."\n";
360 $this->_errorMsg = "No errors found";
362 return $this->_errorMsg;
367 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
368 $err = sqlsrv_errors(SQLSRV_ERR_ALL);
369 if($err[0]) return $err[0]['code'];
373 // returns true or false
374 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
376 if (!function_exists('sqlsrv_connect')) return null;
377 $connectionInfo = array("Database"=>$argDatabasename,'UID'=>$argUsername,'PWD'=>$argPassword);
378 if ($this->debug) error_log("<hr>connecting... hostname: $argHostname params: ".var_export($connectionInfo,true));
379 //if ($this->debug) error_log("<hr>_connectionID before: ".serialize($this->_connectionID));
380 if(!($this->_connectionID = sqlsrv_connect($argHostname,$connectionInfo))) {
381 if ($this->debug) error_log( "<hr><b>errors</b>: ".print_r( sqlsrv_errors(), true));
384 //if ($this->debug) error_log(" _connectionID after: ".serialize($this->_connectionID));
385 //if ($this->debug) error_log("<hr>defined functions: <pre>".var_export(get_defined_functions(),true)."</pre>");
389 // returns true or false
390 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
392 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!)
393 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
396 function Prepare($sql)
398 $stmt = sqlsrv_prepare( $this->_connectionID, $sql);
399 if (!$stmt) return $sql;
400 return array($sql,$stmt);
403 // returns concatenated string
404 // MSSQL requires integers to be cast as strings
405 // automatically cast every datatype to VARCHAR(255)
406 // @author David Rogers (introspectshun)
410 $arr = func_get_args();
412 // Split single record on commas, if possible
413 if (sizeof($arr) == 1) {
414 foreach ($arr as $arg) {
415 $args = explode(',', $arg);
420 array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
421 $s = implode('+',$arr);
422 if (sizeof($arr) > 0) return "$s";
428 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
429 So all your blobs must be of type "image".
431 Remember to set in php.ini the following...
433 ; Valid range 0 - 2147483647. Default = 4096.
434 mssql.textlimit = 0 ; zero to pass through
436 ; Valid range 0 - 2147483647. Default = 4096.
437 mssql.textsize = 0 ; zero to pass through
439 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
442 if (strtoupper($blobtype) == 'CLOB') {
443 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
444 return $this->Execute($sql) != false;
446 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
447 return $this->Execute($sql) != false;
450 // returns query ID if successful, otherwise false
451 function _query($sql,$inputarr=false)
453 $this->_errorMsg = false;
454 if (is_array($inputarr)) {
455 $rez = sqlsrv_query($this->_connectionID,$sql,$inputarr);
456 } elseif (is_array($sql)) {
457 $rez = sqlsrv_query($this->_connectionID,$sql[1],$inputarr);
459 $rez = sqlsrv_query($this->_connectionID,$sql);
461 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)
462 //fix for returning true on anything besides select statements
463 if (is_array($sql)) $sql = $sql[1];
465 if(stripos($sql, 'SELECT') !== 0 && $rez !== false) {
466 if ($this->debug) error_log(" isn't a select query, returning boolean true");
470 if(!$rez) $rez = false;
474 // returns true or false
477 if ($this->transCnt) $this->RollbackTrans();
478 $rez = @sqlsrv_close($this->_connectionID);
479 $this->_connectionID = false;
483 // mssql uses a default date like Dec 30 2000 12:00AM
484 function UnixDate($v)
486 return ADORecordSet_array_mssql::UnixDate($v);
489 function UnixTimeStamp($v)
491 return ADORecordSet_array_mssql::UnixTimeStamp($v);
494 function &MetaIndexes($table,$primary=false)
496 $table = $this->qstr($table);
498 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
499 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,
500 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
501 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
502 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
503 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
504 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
505 ORDER BY O.name, I.Name, K.keyno";
507 global $ADODB_FETCH_MODE;
508 $save = $ADODB_FETCH_MODE;
509 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
510 if ($this->fetchMode !== FALSE) {
511 $savem = $this->SetFetchMode(FALSE);
514 $rs = $this->Execute($sql);
516 $this->SetFetchMode($savem);
518 $ADODB_FETCH_MODE = $save;
520 if (!is_object($rs)) {
525 while ($row = $rs->FetchRow()) {
526 if (!$primary && $row[5]) continue;
528 $indexes[$row[0]]['unique'] = $row[6];
529 $indexes[$row[0]]['columns'][] = $row[1];
534 function MetaForeignKeys($table, $owner=false, $upper=false)
536 global $ADODB_FETCH_MODE;
538 $save = $ADODB_FETCH_MODE;
539 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
540 $table = $this->qstr(strtoupper($table));
543 "select object_name(constid) as constraint_name,
544 col_name(fkeyid, fkey) as column_name,
545 object_name(rkeyid) as referenced_table_name,
546 col_name(rkeyid, rkey) as referenced_column_name
548 where upper(object_name(fkeyid)) = $table
549 order by constraint_name, referenced_table_name, keyno";
551 $constraints =& $this->GetArray($sql);
553 $ADODB_FETCH_MODE = $save;
556 foreach($constraints as $constr) {
558 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
560 if (!$arr) return false;
564 foreach($arr as $k => $v) {
565 foreach($v as $a => $b) {
566 if ($upper) $a = strtoupper($a);
573 //From: Fernando Moreira <FMoreira@imediata.pt>
574 function MetaDatabases()
576 $this->SelectDB("master");
577 $rs =& $this->Execute($this->metaDatabasesSQL);
578 $rows = $rs->GetRows();
580 for($i=0;$i<count($rows);$i++) {
581 $ret[] = $rows[$i][0];
583 $this->SelectDB($this->database);
590 // "Stein-Aksel Basma" <basma@accelero.no>
591 // tested with MSSQL 2000
592 function &MetaPrimaryKeys($table)
594 global $ADODB_FETCH_MODE;
597 $this->_findschema($table,$schema);
598 if (!$schema) $schema = $this->database;
599 if ($schema) $schema = "and k.table_catalog like '$schema%'";
601 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
602 information_schema.table_constraints tc
603 where tc.constraint_name = k.constraint_name and tc.constraint_type =
604 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
606 $savem = $ADODB_FETCH_MODE;
607 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
608 $a = $this->GetCol($sql);
609 $ADODB_FETCH_MODE = $savem;
611 if ($a && sizeof($a)>0) return $a;
617 function &MetaTables($ttype=false,$showSchema=false,$mask=false)
620 $save = $this->metaTablesSQL;
621 $mask = $this->qstr(($mask));
622 $this->metaTablesSQL .= " AND name like $mask";
624 $ret =& ADOConnection::MetaTables($ttype,$showSchema);
627 $this->metaTablesSQL = $save;
633 /*--------------------------------------------------------------------------------------
634 Class Name: Recordset
635 --------------------------------------------------------------------------------------*/
637 class ADORecordset_mssqlnative extends ADORecordSet {
639 var $databaseType = "mssqlnative";
640 var $canSeek = false;
641 var $fieldOffset = 0;
642 // _mths works only in non-localised system
644 function ADORecordset_mssqlnative($id,$mode=false)
646 if ($mode === false) {
647 global $ADODB_FETCH_MODE;
648 $mode = $ADODB_FETCH_MODE;
651 $this->fetchMode = $mode;
652 return $this->ADORecordSet($id,$mode);
658 global $ADODB_COUNTRECS;
659 if ($this->connection->debug) error_log("(before) ADODB_COUNTRECS: {$ADODB_COUNTRECS} _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
660 /*$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."
661 error_log("rowsaff: ".serialize($retRowsAff));
662 $this->_numOfRows = ($ADODB_COUNTRECS)? $retRowsAff:-1;*/
663 $this->_numOfRows = -1;//not supported
664 $fieldmeta = sqlsrv_field_metadata($this->_queryID);
665 $this->_numOfFields = ($fieldmeta)? count($fieldmeta):-1;
666 if ($this->connection->debug) error_log("(after) _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
670 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
671 // get next resultset - requires PHP 4.0.5 or later
672 function NextRecordSet()
674 if (!sqlsrv_next_result($this->_queryID)) return false;
675 $this->_inited = false;
677 $this->_currentRow = -1;
682 /* Use associative array to get fields array */
683 function Fields($colname)
685 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
687 $this->bind = array();
688 for ($i=0; $i < $this->_numOfFields; $i++) {
689 $o = $this->FetchField($i);
690 $this->bind[strtoupper($o->name)] = $i;
694 return $this->fields[$this->bind[strtoupper($colname)]];
697 /* Returns: an object containing field information.
698 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
699 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
700 fetchField() is retrieved. */
702 function &FetchField($fieldOffset = -1)
704 if ($this->connection->debug) error_log("<hr>fetchfield: $fieldOffset, fetch array: <pre>".print_r($this->fields,true)."</pre> backtrace: ".adodb_backtrace(false));
705 if ($fieldOffset != -1) $this->fieldOffset = $fieldOffset;
706 $arrKeys = array_keys($this->fields);
707 if(array_key_exists($this->fieldOffset,$arrKeys) && !array_key_exists($arrKeys[$this->fieldOffset],$this->fields)) {
710 $f = $this->fields[ $arrKeys[$this->fieldOffset] ];
711 if($fieldOffset == -1) $this->fieldOffset++;
715 $f = false;//PHP Notice: Only variable references should be returned by reference
722 return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams.
728 if ($this->connection->debug) error_log("movenext()");
729 //if ($this->connection->debug) error_log("eof (beginning): ".$this->EOF);
730 if ($this->EOF) return false;
732 $this->_currentRow++;
733 if ($this->connection->debug) error_log("_currentRow: ".$this->_currentRow);
735 if ($this->_fetch()) return true;
737 //if ($this->connection->debug) error_log("eof (end): ".$this->EOF);
743 // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
744 // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
745 function _fetch($ignore_fields=false)
747 if ($this->connection->debug) error_log("_fetch()");
748 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
749 if ($this->fetchMode & ADODB_FETCH_NUM) {
750 if ($this->connection->debug) error_log("fetch mode: both");
751 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH);
753 if ($this->connection->debug) error_log("fetch mode: assoc");
754 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC);
757 if (ADODB_ASSOC_CASE == 0) {
758 foreach($this->fields as $k=>$v) {
759 $this->fields[strtolower($k)] = $v;
761 } elseif (ADODB_ASSOC_CASE == 1) {
762 foreach($this->fields as $k=>$v) {
763 $this->fields[strtoupper($k)] = $v;
767 if ($this->connection->debug) error_log("fetch mode: num");
768 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC);
770 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
772 foreach($this->fields as $key=>$value) {
773 if(is_numeric($key)) {
774 $arrFixed[$key-1] = $value;
776 $arrFixed[$key] = $value;
779 //if($this->connection->debug) error_log("<hr>fixing non 0 based return array, old: ".print_r($this->fields,true)." new: ".print_r($arrFixed,true));
780 $this->fields = $arrFixed;
782 if(is_array($this->fields)) {
783 foreach($this->fields as $key=>$value) {
784 if (is_object($value) && method_exists($value, 'format')) {//is DateTime object
785 $this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z");
789 if($this->fields === null) $this->fields = false;
790 if ($this->connection->debug) error_log("<hr>after _fetch, fields: <pre>".print_r($this->fields,true)." backtrace: ".adodb_backtrace(false));
791 return $this->fields;
794 /* close() only needs to be called if you are worried about using too much memory while your script
795 is running. All associated result memory for the specified result identifier will automatically be freed. */
798 $rez = sqlsrv_free_stmt($this->_queryID);
799 $this->_queryID = false;
803 // mssql uses a default date like Dec 30 2000 12:00AM
804 function UnixDate($v)
806 return ADORecordSet_array_mssqlnative::UnixDate($v);
809 function UnixTimeStamp($v)
811 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
816 class ADORecordSet_array_mssqlnative extends ADORecordSet_array {
817 function ADORecordSet_array_mssqlnative($id=-1,$mode=false)
819 $this->ADORecordSet_array($id,$mode);
822 // mssql uses a default date like Dec 30 2000 12:00AM
823 function UnixDate($v)
826 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
828 global $ADODB_mssql_mths,$ADODB_mssql_date_order;
830 //Dec 30 2000 12:00AM
831 if ($ADODB_mssql_date_order == 'dmy') {
832 if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
833 return parent::UnixDate($v);
835 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
838 $themth = substr(strtoupper($rr[2]),0,3);
840 if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
841 return parent::UnixDate($v);
843 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
846 $themth = substr(strtoupper($rr[1]),0,3);
848 $themth = $ADODB_mssql_mths[$themth];
849 if ($themth <= 0) return false;
851 return mktime(0,0,0,$themth,$theday,$rr[3]);
854 function UnixTimeStamp($v)
857 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
859 global $ADODB_mssql_mths,$ADODB_mssql_date_order;
861 //Dec 30 2000 12:00AM
862 if ($ADODB_mssql_date_order == 'dmy') {
863 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})|"
864 ,$v, $rr)) return parent::UnixTimeStamp($v);
865 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
868 $themth = substr(strtoupper($rr[2]),0,3);
870 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})|"
871 ,$v, $rr)) return parent::UnixTimeStamp($v);
872 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
875 $themth = substr(strtoupper($rr[1]),0,3);
878 $themth = $ADODB_mssql_mths[$themth];
879 if ($themth <= 0) return false;
881 switch (strtoupper($rr[6])) {
883 if ($rr[4]<12) $rr[4] += 12;
886 if ($rr[4]==12) $rr[4] = 0;
892 return mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
899 select object_name(constid) as constraint_name,
900 object_name(fkeyid) as table_name,
901 col_name(fkeyid, fkey) as column_name,
902 object_name(rkeyid) as referenced_table_name,
903 col_name(rkeyid, rkey) as referenced_column_name
905 where object_name(fkeyid) = x
906 order by constraint_name, table_name, referenced_table_name, keyno
909 select constraint_name,
912 from information_schema.key_column_usage
913 where constraint_catalog = db_name()
915 order by constraint_name, ordinal_position
917 http://www.databasejournal.com/scripts/article.php/1440551