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);
44 //----------------------------------------------------------------
45 // MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002
46 // and this causes tons of problems because localized versions of
47 // MSSQL will return the dates in dmy or mdy order; and also the
48 // month strings depends on what language has been configured. The
49 // following two variables allow you to control the localization
52 // MORE LOCALIZATION INFO
53 // ----------------------
54 // To configure datetime, look for and modify sqlcommn.loc,
55 // typically found in c:\mssql\install
57 // http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918
59 // CONVERT(char(12),datecol,120)
61 // Also if your month is showing as month-1,
62 // e.g. Jan 13, 2002 is showing as 13/0/2002, then see
63 // http://phplens.com/lens/lensforum/msgs.php?id=7048&x=1
64 // it's a localisation problem.
65 //----------------------------------------------------------------
68 // has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc
69 if (ADODB_PHPVER >= 0x4300) {
70 // docs say 4.2.0, but testing shows only since 4.3.0 does it work!
71 ini_set('mssql.datetimeconvert',0);
73 global $ADODB_mssql_mths; // array, months must be upper-case
74 $ADODB_mssql_date_order = 'mdy';
75 $ADODB_mssql_mths = array(
76 'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6,
77 'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12);
80 //---------------------------------------------------------------------------
81 // Call this to autoset $ADODB_mssql_date_order at the beginning of your code,
82 // just after you connect to the database. Supports mdy and dmy only.
83 // Not required for PHP 4.2.0 and above.
84 function AutoDetect_MSSQL_Date_Order($conn)
86 global $ADODB_mssql_date_order;
87 $adate = $conn->GetOne('select getdate()');
92 //ADOConnection::outp( "MSSQL: YYYY-MM-DD date format not supported currently");
94 $ADODB_mssql_date_order = 'dmy';
96 $ADODB_mssql_date_order = 'mdy';
100 class ADODB_mssqlnative extends ADOConnection {
101 var $databaseType = "mssqlnative";
102 var $dataProvider = "mssqlnative";
103 var $replaceQuote = "''"; // string to use to replace quotes
104 var $fmtDate = "'Y-m-d'";
105 var $fmtTimeStamp = "'Y-m-d H:i:s'";
106 var $hasInsertID = true;
107 var $substr = "substring";
109 var $hasAffectedRows = true;
110 var $poorAffectedRows = false;
111 var $metaDatabasesSQL = "select name from sys.sysdatabases where name <> 'master'";
112 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'))";
113 var $metaColumnsSQL = # xtype==61 is datetime
114 "select c.name,t.name,c.length,
115 (case when c.xusertype=61 then 0 else c.xprec end),
116 (case when c.xusertype=61 then 0 else c.xscale end)
117 from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'";
118 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE
119 var $hasGenID = true;
120 var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)';
121 var $sysTimeStamp = 'GetDate()';
122 var $maxParameterLen = 4000;
123 var $arrayClass = 'ADORecordSet_array_mssqlnative';
124 var $uniqueSort = true;
125 var $leftOuter = '*=';
126 var $rightOuter = '=*';
127 var $ansiOuter = true; // for mssql7 or later
128 var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000
129 var $uniqueOrderBy = true;
130 var $_bindInputArray = true;
131 var $_dropSeqSQL = "drop table %s";
133 function ADODB_mssqlnative()
137 sqlsrv_set_error_handling( SQLSRV_ERRORS_LOG_ALL );
138 sqlsrv_log_set_severity( SQLSRV_LOG_SEVERITY_ALL );
139 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
140 sqlsrv_configure('warnings_return_as_errors', 0);
142 sqlsrv_set_error_handling(0);
143 sqlsrv_log_set_severity(0);
144 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
145 sqlsrv_configure('warnings_return_as_errors', 0);
149 function ServerInfo()
151 global $ADODB_FETCH_MODE;
152 if ($this->fetchMode === false) {
153 $savem = $ADODB_FETCH_MODE;
154 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
156 $savem = $this->SetFetchMode(ADODB_FETCH_NUM);
157 $arrServerInfo = sqlsrv_server_info($this->_connectionID);
158 $arr['description'] = $arrServerInfo['SQLServerName'].' connected to '.$arrServerInfo['CurrentDatabase'];
159 $arr['version'] = $arrServerInfo['SQLServerVersion'];//ADOConnection::_findvers($arr['description']);
163 function IfNull( $field, $ifNull )
165 return " ISNULL($field, $ifNull) "; // if MS SQL Server
171 // Returns the last IDENTITY value inserted into an IDENTITY column in
172 // the same scope. A scope is a module -- a stored procedure, trigger,
173 // function, or batch. Thus, two statements are in the same scope if
174 // they are in the same stored procedure, function, or batch.
175 return $this->GetOne($this->identitySQL);
178 function _affectedrows()
180 return sqlsrv_rows_affected($this->_queryID);
183 function CreateSequence($seq='adodbseq',$start=1)
185 if($this->debug) error_log("<hr>CreateSequence($seq,$start)");
186 sqlsrv_begin_transaction($this->_connectionID);
188 $this->Execute("create table $seq (id int)");//was float(53)
189 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
191 if($this->debug) error_log("<hr>Error: ROLLBACK");
192 sqlsrv_rollback($this->_connectionID);
195 sqlsrv_commit($this->_connectionID);
199 function GenID($seq='adodbseq',$start=1)
201 if($this->debug) error_log("<hr>GenID($seq,$start)");
202 sqlsrv_begin_transaction($this->_connectionID);
203 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
205 $this->Execute("create table $seq (id int)");
206 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
208 if($this->debug) error_log("<hr>Error: ROLLBACK");
209 sqlsrv_rollback($this->_connectionID);
212 sqlsrv_commit($this->_connectionID);
215 $num = $this->GetOne("select id from $seq");
216 sqlsrv_commit($this->_connectionID);
217 if($this->debug) error_log(" Returning: $num");
221 // Format date column in sql string given an input format that understands Y M D
222 function SQLDate($fmt, $col=false)
224 if (!$col) $col = $this->sysTimeStamp;
228 for ($i=0; $i < $len; $i++) {
234 $s .= "datename(yyyy,$col)";
237 $s .= "convert(char(3),$col,0)";
240 $s .= "replace(str(month($col),2),' ','0')";
244 $s .= "datename(quarter,$col)";
248 $s .= "replace(str(day($col),2),' ','0')";
251 $s .= "substring(convert(char(14),$col,0),13,2)";
255 $s .= "replace(str(datepart(hh,$col),2),' ','0')";
259 $s .= "replace(str(datepart(mi,$col),2),' ','0')";
262 $s .= "replace(str(datepart(ss,$col),2),' ','0')";
266 $s .= "substring(convert(char(19),$col,0),18,2)";
272 $ch = substr($fmt,$i,1);
274 $s .= $this->qstr($ch);
282 function BeginTrans()
284 if ($this->transOff) return true;
285 $this->transCnt += 1;
286 if ($this->debug) error_log('<hr>begin transaction');
287 sqlsrv_begin_transaction($this->_connectionID);
291 function CommitTrans($ok=true)
293 if ($this->transOff) return true;
294 if ($this->debug) error_log('<hr>commit transaction');
295 if (!$ok) return $this->RollbackTrans();
296 if ($this->transCnt) $this->transCnt -= 1;
297 sqlsrv_commit($this->_connectionID);
300 function RollbackTrans()
302 if ($this->transOff) return true;
303 if ($this->debug) error_log('<hr>rollback transaction');
304 if ($this->transCnt) $this->transCnt -= 1;
305 sqlsrv_rollback($this->_connectionID);
309 function SetTransactionMode( $transaction_mode )
311 $this->_transmode = $transaction_mode;
312 if (empty($transaction_mode)) {
313 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
316 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
317 $this->Execute("SET TRANSACTION ".$transaction_mode);
324 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
326 # some operation on both tables table1 and table2
328 $this->CommitTrans();
330 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
332 function RowLock($tables,$where,$col='top 1 null as ignore')
334 if (!$this->transCnt) $this->BeginTrans();
335 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where");
338 function SelectDB($dbName)
340 $this->database = $dbName;
341 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
342 if ($this->_connectionID) {
343 $rs = $this->Execute('USE '.$dbName);
353 $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL);
354 if($retErrors != null) {
355 foreach($retErrors as $arrError) {
356 $this->_errorMsg .= "SQLState: ".$arrError[ 'SQLSTATE']."\n";
357 $this->_errorMsg .= "Error Code: ".$arrError[ 'code']."\n";
358 $this->_errorMsg .= "Message: ".$arrError[ 'message']."\n";
361 $this->_errorMsg = "No errors found";
363 return $this->_errorMsg;
368 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
369 $err = sqlsrv_errors(SQLSRV_ERR_ALL);
370 if($err[0]) return $err[0]['code'];
374 // returns true or false
375 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
377 if (!function_exists('sqlsrv_connect')) return null;
378 $connectionInfo = array("Database"=>$argDatabasename,'UID'=>$argUsername,'PWD'=>$argPassword);
379 if ($this->debug) error_log("<hr>connecting... hostname: $argHostname params: ".var_export($connectionInfo,true));
380 //if ($this->debug) error_log("<hr>_connectionID before: ".serialize($this->_connectionID));
381 if(!($this->_connectionID = sqlsrv_connect($argHostname,$connectionInfo))) {
382 if ($this->debug) error_log( "<hr><b>errors</b>: ".print_r( sqlsrv_errors(), true));
385 //if ($this->debug) error_log(" _connectionID after: ".serialize($this->_connectionID));
386 //if ($this->debug) error_log("<hr>defined functions: <pre>".var_export(get_defined_functions(),true)."</pre>");
390 // returns true or false
391 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
393 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!)
394 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
397 function Prepare($sql)
399 $stmt = sqlsrv_prepare( $this->_connectionID, $sql);
400 if (!$stmt) return $sql;
401 return array($sql,$stmt);
404 // returns concatenated string
405 // MSSQL requires integers to be cast as strings
406 // automatically cast every datatype to VARCHAR(255)
407 // @author David Rogers (introspectshun)
411 $arr = func_get_args();
413 // Split single record on commas, if possible
414 if (sizeof($arr) == 1) {
415 foreach ($arr as $arg) {
416 $args = explode(',', $arg);
421 array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
422 $s = implode('+',$arr);
423 if (sizeof($arr) > 0) return "$s";
429 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
430 So all your blobs must be of type "image".
432 Remember to set in php.ini the following...
434 ; Valid range 0 - 2147483647. Default = 4096.
435 mssql.textlimit = 0 ; zero to pass through
437 ; Valid range 0 - 2147483647. Default = 4096.
438 mssql.textsize = 0 ; zero to pass through
440 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
443 if (strtoupper($blobtype) == 'CLOB') {
444 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
445 return $this->Execute($sql) != false;
447 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
448 return $this->Execute($sql) != false;
451 // returns query ID if successful, otherwise false
452 function _query($sql,$inputarr=false)
454 $this->_errorMsg = false;
455 if (is_array($inputarr)) {
456 $rez = sqlsrv_query($this->_connectionID,$sql,$inputarr);
457 } else if (is_array($sql)) {
458 $rez = sqlsrv_query($this->_connectionID,$sql[1],$inputarr);
460 $rez = sqlsrv_query($this->_connectionID,$sql);
462 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)
463 //fix for returning true on anything besides select statements
464 if (is_array($sql)) $sql = $sql[1];
466 if(stripos($sql, 'SELECT') !== 0 && $rez !== false) {
467 if ($this->debug) error_log(" isn't a select query, returning boolean true");
471 if(!$rez) $rez = false;
475 // returns true or false
478 if ($this->transCnt) $this->RollbackTrans();
479 $rez = @sqlsrv_close($this->_connectionID);
480 $this->_connectionID = false;
484 // mssql uses a default date like Dec 30 2000 12:00AM
485 function UnixDate($v)
487 return ADORecordSet_array_mssql::UnixDate($v);
490 function UnixTimeStamp($v)
492 return ADORecordSet_array_mssql::UnixTimeStamp($v);
495 function &MetaIndexes($table,$primary=false)
497 $table = $this->qstr($table);
499 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
500 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,
501 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
502 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
503 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
504 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
505 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
506 ORDER BY O.name, I.Name, K.keyno";
508 global $ADODB_FETCH_MODE;
509 $save = $ADODB_FETCH_MODE;
510 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
511 if ($this->fetchMode !== FALSE) {
512 $savem = $this->SetFetchMode(FALSE);
515 $rs = $this->Execute($sql);
517 $this->SetFetchMode($savem);
519 $ADODB_FETCH_MODE = $save;
521 if (!is_object($rs)) {
526 while ($row = $rs->FetchRow()) {
527 if (!$primary && $row[5]) continue;
529 $indexes[$row[0]]['unique'] = $row[6];
530 $indexes[$row[0]]['columns'][] = $row[1];
535 function MetaForeignKeys($table, $owner=false, $upper=false)
537 global $ADODB_FETCH_MODE;
539 $save = $ADODB_FETCH_MODE;
540 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
541 $table = $this->qstr(strtoupper($table));
544 "select object_name(constid) as constraint_name,
545 col_name(fkeyid, fkey) as column_name,
546 object_name(rkeyid) as referenced_table_name,
547 col_name(rkeyid, rkey) as referenced_column_name
549 where upper(object_name(fkeyid)) = $table
550 order by constraint_name, referenced_table_name, keyno";
552 $constraints =& $this->GetArray($sql);
554 $ADODB_FETCH_MODE = $save;
557 foreach($constraints as $constr) {
559 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
561 if (!$arr) return false;
565 foreach($arr as $k => $v) {
566 foreach($v as $a => $b) {
567 if ($upper) $a = strtoupper($a);
574 //From: Fernando Moreira <FMoreira@imediata.pt>
575 function MetaDatabases()
577 $this->SelectDB("master");
578 $rs =& $this->Execute($this->metaDatabasesSQL);
579 $rows = $rs->GetRows();
581 for($i=0;$i<count($rows);$i++) {
582 $ret[] = $rows[$i][0];
584 $this->SelectDB($this->database);
591 // "Stein-Aksel Basma" <basma@accelero.no>
592 // tested with MSSQL 2000
593 function &MetaPrimaryKeys($table)
595 global $ADODB_FETCH_MODE;
598 $this->_findschema($table,$schema);
599 if (!$schema) $schema = $this->database;
600 if ($schema) $schema = "and k.table_catalog like '$schema%'";
602 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
603 information_schema.table_constraints tc
604 where tc.constraint_name = k.constraint_name and tc.constraint_type =
605 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
607 $savem = $ADODB_FETCH_MODE;
608 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
609 $a = $this->GetCol($sql);
610 $ADODB_FETCH_MODE = $savem;
612 if ($a && sizeof($a)>0) return $a;
618 function &MetaTables($ttype=false,$showSchema=false,$mask=false)
621 $save = $this->metaTablesSQL;
622 $mask = $this->qstr(($mask));
623 $this->metaTablesSQL .= " AND name like $mask";
625 $ret =& ADOConnection::MetaTables($ttype,$showSchema);
628 $this->metaTablesSQL = $save;
634 /*--------------------------------------------------------------------------------------
635 Class Name: Recordset
636 --------------------------------------------------------------------------------------*/
638 class ADORecordset_mssqlnative extends ADORecordSet {
640 var $databaseType = "mssqlnative";
641 var $canSeek = false;
642 var $fieldOffset = 0;
643 // _mths works only in non-localised system
645 function ADORecordset_mssqlnative($id,$mode=false)
647 if ($mode === false) {
648 global $ADODB_FETCH_MODE;
649 $mode = $ADODB_FETCH_MODE;
652 $this->fetchMode = $mode;
653 return $this->ADORecordSet($id,$mode);
659 global $ADODB_COUNTRECS;
660 if ($this->connection->debug) error_log("(before) ADODB_COUNTRECS: {$ADODB_COUNTRECS} _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
661 /*$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."
662 error_log("rowsaff: ".serialize($retRowsAff));
663 $this->_numOfRows = ($ADODB_COUNTRECS)? $retRowsAff:-1;*/
664 $this->_numOfRows = -1;//not supported
665 $fieldmeta = sqlsrv_field_metadata($this->_queryID);
666 $this->_numOfFields = ($fieldmeta)? count($fieldmeta):-1;
667 if ($this->connection->debug) error_log("(after) _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
671 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
672 // get next resultset - requires PHP 4.0.5 or later
673 function NextRecordSet()
675 if (!sqlsrv_next_result($this->_queryID)) return false;
676 $this->_inited = false;
678 $this->_currentRow = -1;
683 /* Use associative array to get fields array */
684 function Fields($colname)
686 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
688 $this->bind = array();
689 for ($i=0; $i < $this->_numOfFields; $i++) {
690 $o = $this->FetchField($i);
691 $this->bind[strtoupper($o->name)] = $i;
695 return $this->fields[$this->bind[strtoupper($colname)]];
698 /* Returns: an object containing field information.
699 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
700 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
701 fetchField() is retrieved. */
703 function &FetchField($fieldOffset = -1)
705 if ($this->connection->debug) error_log("<hr>fetchfield: $fieldOffset, fetch array: <pre>".print_r($this->fields,true)."</pre> backtrace: ".adodb_backtrace(false));
706 if ($fieldOffset != -1) $this->fieldOffset = $fieldOffset;
707 $arrKeys = array_keys($this->fields);
708 if(array_key_exists($this->fieldOffset,$arrKeys) && !array_key_exists($arrKeys[$this->fieldOffset],$this->fields)) {
711 $f = $this->fields[ $arrKeys[$this->fieldOffset] ];
712 if($fieldOffset == -1) $this->fieldOffset++;
716 $f = false;//PHP Notice: Only variable references should be returned by reference
723 return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams.
729 if ($this->connection->debug) error_log("movenext()");
730 //if ($this->connection->debug) error_log("eof (beginning): ".$this->EOF);
731 if ($this->EOF) return false;
733 $this->_currentRow++;
734 if ($this->connection->debug) error_log("_currentRow: ".$this->_currentRow);
736 if ($this->_fetch()) return true;
738 //if ($this->connection->debug) error_log("eof (end): ".$this->EOF);
744 // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
745 // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
746 function _fetch($ignore_fields=false)
748 if ($this->connection->debug) error_log("_fetch()");
749 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
750 if ($this->fetchMode & ADODB_FETCH_NUM) {
751 if ($this->connection->debug) error_log("fetch mode: both");
752 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH);
754 if ($this->connection->debug) error_log("fetch mode: assoc");
755 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC);
758 if (ADODB_ASSOC_CASE == 0) {
759 foreach($this->fields as $k=>$v) {
760 $this->fields[strtolower($k)] = $v;
762 } else if (ADODB_ASSOC_CASE == 1) {
763 foreach($this->fields as $k=>$v) {
764 $this->fields[strtoupper($k)] = $v;
768 if ($this->connection->debug) error_log("fetch mode: num");
769 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC);
771 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
773 foreach($this->fields as $key=>$value) {
774 if(is_numeric($key)) {
775 $arrFixed[$key-1] = $value;
777 $arrFixed[$key] = $value;
780 //if($this->connection->debug) error_log("<hr>fixing non 0 based return array, old: ".print_r($this->fields,true)." new: ".print_r($arrFixed,true));
781 $this->fields = $arrFixed;
783 if(is_array($this->fields)) {
784 foreach($this->fields as $key=>$value) {
785 if (is_object($value) && method_exists($value, 'format')) {//is DateTime object
786 $this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z");
790 if($this->fields === null) $this->fields = false;
791 if ($this->connection->debug) error_log("<hr>after _fetch, fields: <pre>".print_r($this->fields,true)." backtrace: ".adodb_backtrace(false));
792 return $this->fields;
795 /* close() only needs to be called if you are worried about using too much memory while your script
796 is running. All associated result memory for the specified result identifier will automatically be freed. */
799 $rez = sqlsrv_free_stmt($this->_queryID);
800 $this->_queryID = false;
804 // mssql uses a default date like Dec 30 2000 12:00AM
805 function UnixDate($v)
807 return ADORecordSet_array_mssqlnative::UnixDate($v);
810 function UnixTimeStamp($v)
812 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
817 class ADORecordSet_array_mssqlnative extends ADORecordSet_array {
818 function ADORecordSet_array_mssqlnative($id=-1,$mode=false)
820 $this->ADORecordSet_array($id,$mode);
823 // mssql uses a default date like Dec 30 2000 12:00AM
824 function UnixDate($v)
827 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
829 global $ADODB_mssql_mths,$ADODB_mssql_date_order;
831 //Dec 30 2000 12:00AM
832 if ($ADODB_mssql_date_order == 'dmy') {
833 if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
834 return parent::UnixDate($v);
836 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
839 $themth = substr(strtoupper($rr[2]),0,3);
841 if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
842 return parent::UnixDate($v);
844 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
847 $themth = substr(strtoupper($rr[1]),0,3);
849 $themth = $ADODB_mssql_mths[$themth];
850 if ($themth <= 0) return false;
852 return mktime(0,0,0,$themth,$theday,$rr[3]);
855 function UnixTimeStamp($v)
858 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
860 global $ADODB_mssql_mths,$ADODB_mssql_date_order;
862 //Dec 30 2000 12:00AM
863 if ($ADODB_mssql_date_order == 'dmy') {
864 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})|"
865 ,$v, $rr)) return parent::UnixTimeStamp($v);
866 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
869 $themth = substr(strtoupper($rr[2]),0,3);
871 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})|"
872 ,$v, $rr)) return parent::UnixTimeStamp($v);
873 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
876 $themth = substr(strtoupper($rr[1]),0,3);
879 $themth = $ADODB_mssql_mths[$themth];
880 if ($themth <= 0) return false;
882 switch (strtoupper($rr[6])) {
884 if ($rr[4]<12) $rr[4] += 12;
887 if ($rr[4]==12) $rr[4] = 0;
893 return mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
900 select object_name(constid) as constraint_name,
901 object_name(fkeyid) as table_name,
902 col_name(fkeyid, fkey) as column_name,
903 object_name(rkeyid) as referenced_table_name,
904 col_name(rkeyid, rkey) as referenced_column_name
906 where object_name(fkeyid) = x
907 order by constraint_name, table_name, referenced_table_name, keyno
910 select constraint_name,
913 from information_schema.key_column_usage
914 where constraint_catalog = db_name()
916 order by constraint_name, ordinal_position
918 http://www.databasejournal.com/scripts/article.php/1440551