Updated adodb syysext to upstream version 5.0.8a.
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / drivers / adodb-mssqlnative.inc.php
1 <?php
2 /*
3 V5.08 6 Apr 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.
8
9 Latest version is available at http://adodb.sourceforge.net
10
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
15
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
18
19 */
20
21 // security - hide paths
22 if (!defined('ADODB_DIR')) die();
23
24 if (!function_exists('sqlsrv_configure')) {
25 die("mssqlnative extension not installed");
26 }
27
28 if (!function_exists('sqlsrv_set_error_handling')) {
29 function sqlsrv_set_error_handling($constant) {
30 sqlsrv_configure("WarningsReturnAsErrors", $constant);
31 }
32 }
33 if (!function_exists('sqlsrv_log_set_severity')) {
34 function sqlsrv_log_set_severity($constant) {
35 sqlsrv_configure("LogSeverity", $constant);
36 }
37 }
38 if (!function_exists('sqlsrv_log_set_subsystems')) {
39 function sqlsrv_log_set_subsystems($constant) {
40 sqlsrv_configure("LogSubsystems", $constant);
41 }
42 }
43
44
45 //----------------------------------------------------------------
46 // MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002
47 // and this causes tons of problems because localized versions of
48 // MSSQL will return the dates in dmy or mdy order; and also the
49 // month strings depends on what language has been configured. The
50 // following two variables allow you to control the localization
51 // settings - Ugh.
52 //
53 // MORE LOCALIZATION INFO
54 // ----------------------
55 // To configure datetime, look for and modify sqlcommn.loc,
56 // typically found in c:\mssql\install
57 // Also read :
58 // http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918
59 // Alternatively use:
60 // CONVERT(char(12),datecol,120)
61 //
62 // Also if your month is showing as month-1,
63 // e.g. Jan 13, 2002 is showing as 13/0/2002, then see
64 // http://phplens.com/lens/lensforum/msgs.php?id=7048&x=1
65 // it's a localisation problem.
66 //----------------------------------------------------------------
67
68
69 // has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc
70 if (ADODB_PHPVER >= 0x4300) {
71 // docs say 4.2.0, but testing shows only since 4.3.0 does it work!
72 ini_set('mssql.datetimeconvert',0);
73 } else {
74 global $ADODB_mssql_mths; // array, months must be upper-case
75 $ADODB_mssql_date_order = 'mdy';
76 $ADODB_mssql_mths = array(
77 'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6,
78 'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12);
79 }
80
81 //---------------------------------------------------------------------------
82 // Call this to autoset $ADODB_mssql_date_order at the beginning of your code,
83 // just after you connect to the database. Supports mdy and dmy only.
84 // Not required for PHP 4.2.0 and above.
85 function AutoDetect_MSSQL_Date_Order($conn)
86 {
87 global $ADODB_mssql_date_order;
88 $adate = $conn->GetOne('select getdate()');
89 if ($adate) {
90 $anum = (int) $adate;
91 if ($anum > 0) {
92 if ($anum > 31) {
93 //ADOConnection::outp( "MSSQL: YYYY-MM-DD date format not supported currently");
94 } else
95 $ADODB_mssql_date_order = 'dmy';
96 } else
97 $ADODB_mssql_date_order = 'mdy';
98 }
99 }
100
101 class ADODB_mssqlnative extends ADOConnection {
102 var $databaseType = "mssqlnative";
103 var $dataProvider = "mssqlnative";
104 var $replaceQuote = "''"; // string to use to replace quotes
105 var $fmtDate = "'Y-m-d'";
106 var $fmtTimeStamp = "'Y-m-d H:i:s'";
107 var $hasInsertID = true;
108 var $substr = "substring";
109 var $length = 'len';
110 var $hasAffectedRows = true;
111 var $poorAffectedRows = false;
112 var $metaDatabasesSQL = "select name from sys.sysdatabases where name <> 'master'";
113 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'))";
114 var $metaColumnsSQL = # xtype==61 is datetime
115 "select c.name,t.name,c.length,
116 (case when c.xusertype=61 then 0 else c.xprec end),
117 (case when c.xusertype=61 then 0 else c.xscale end)
118 from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'";
119 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE
120 var $hasGenID = true;
121 var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)';
122 var $sysTimeStamp = 'GetDate()';
123 var $maxParameterLen = 4000;
124 var $arrayClass = 'ADORecordSet_array_mssqlnative';
125 var $uniqueSort = true;
126 var $leftOuter = '*=';
127 var $rightOuter = '=*';
128 var $ansiOuter = true; // for mssql7 or later
129 var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000
130 var $uniqueOrderBy = true;
131 var $_bindInputArray = true;
132 var $_dropSeqSQL = "drop table %s";
133
134 function ADODB_mssqlnative()
135 {
136 if ($this->debug) {
137 error_log("<pre>");
138 sqlsrv_set_error_handling( SQLSRV_ERRORS_LOG_ALL );
139 sqlsrv_log_set_severity( SQLSRV_LOG_SEVERITY_ALL );
140 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
141 sqlsrv_configure('warnings_return_as_errors', 0);
142 } else {
143 sqlsrv_set_error_handling(0);
144 sqlsrv_log_set_severity(0);
145 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
146 sqlsrv_configure('warnings_return_as_errors', 0);
147 }
148 }
149
150 function ServerInfo()
151 {
152 global $ADODB_FETCH_MODE;
153 if ($this->fetchMode === false) {
154 $savem = $ADODB_FETCH_MODE;
155 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
156 } else
157 $savem = $this->SetFetchMode(ADODB_FETCH_NUM);
158 $arrServerInfo = sqlsrv_server_info($this->_connectionID);
159 $arr['description'] = $arrServerInfo['SQLServerName'].' connected to '.$arrServerInfo['CurrentDatabase'];
160 $arr['version'] = $arrServerInfo['SQLServerVersion'];//ADOConnection::_findvers($arr['description']);
161 return $arr;
162 }
163
164 function IfNull( $field, $ifNull )
165 {
166 return " ISNULL($field, $ifNull) "; // if MS SQL Server
167 }
168
169 function _insertid()
170 {
171 // SCOPE_IDENTITY()
172 // Returns the last IDENTITY value inserted into an IDENTITY column in
173 // the same scope. A scope is a module -- a stored procedure, trigger,
174 // function, or batch. Thus, two statements are in the same scope if
175 // they are in the same stored procedure, function, or batch.
176 return $this->GetOne($this->identitySQL);
177 }
178
179 function _affectedrows()
180 {
181 return sqlsrv_rows_affected($this->_queryID);
182 }
183
184 function CreateSequence($seq='adodbseq',$start=1)
185 {
186 if($this->debug) error_log("<hr>CreateSequence($seq,$start)");
187 sqlsrv_begin_transaction($this->_connectionID);
188 $start -= 1;
189 $this->Execute("create table $seq (id int)");//was float(53)
190 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
191 if (!$ok) {
192 if($this->debug) error_log("<hr>Error: ROLLBACK");
193 sqlsrv_rollback($this->_connectionID);
194 return false;
195 }
196 sqlsrv_commit($this->_connectionID);
197 return true;
198 }
199
200 function GenID($seq='adodbseq',$start=1)
201 {
202 if($this->debug) error_log("<hr>GenID($seq,$start)");
203 sqlsrv_begin_transaction($this->_connectionID);
204 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
205 if (!$ok) {
206 $this->Execute("create table $seq (id int)");
207 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
208 if (!$ok) {
209 if($this->debug) error_log("<hr>Error: ROLLBACK");
210 sqlsrv_rollback($this->_connectionID);
211 return false;
212 }
213 sqlsrv_commit($this->_connectionID);
214 return $start;
215 }
216 $num = $this->GetOne("select id from $seq");
217 sqlsrv_commit($this->_connectionID);
218 if($this->debug) error_log(" Returning: $num");
219 return $num;
220 }
221
222 // Format date column in sql string given an input format that understands Y M D
223 function SQLDate($fmt, $col=false)
224 {
225 if (!$col) $col = $this->sysTimeStamp;
226 $s = '';
227
228 $len = strlen($fmt);
229 for ($i=0; $i < $len; $i++) {
230 if ($s) $s .= '+';
231 $ch = $fmt[$i];
232 switch($ch) {
233 case 'Y':
234 case 'y':
235 $s .= "datename(yyyy,$col)";
236 break;
237 case 'M':
238 $s .= "convert(char(3),$col,0)";
239 break;
240 case 'm':
241 $s .= "replace(str(month($col),2),' ','0')";
242 break;
243 case 'Q':
244 case 'q':
245 $s .= "datename(quarter,$col)";
246 break;
247 case 'D':
248 case 'd':
249 $s .= "replace(str(day($col),2),' ','0')";
250 break;
251 case 'h':
252 $s .= "substring(convert(char(14),$col,0),13,2)";
253 break;
254
255 case 'H':
256 $s .= "replace(str(datepart(hh,$col),2),' ','0')";
257 break;
258
259 case 'i':
260 $s .= "replace(str(datepart(mi,$col),2),' ','0')";
261 break;
262 case 's':
263 $s .= "replace(str(datepart(ss,$col),2),' ','0')";
264 break;
265 case 'a':
266 case 'A':
267 $s .= "substring(convert(char(19),$col,0),18,2)";
268 break;
269
270 default:
271 if ($ch == '\\') {
272 $i++;
273 $ch = substr($fmt,$i,1);
274 }
275 $s .= $this->qstr($ch);
276 break;
277 }
278 }
279 return $s;
280 }
281
282
283 function BeginTrans()
284 {
285 if ($this->transOff) return true;
286 $this->transCnt += 1;
287 if ($this->debug) error_log('<hr>begin transaction');
288 sqlsrv_begin_transaction($this->_connectionID);
289 return true;
290 }
291
292 function CommitTrans($ok=true)
293 {
294 if ($this->transOff) return true;
295 if ($this->debug) error_log('<hr>commit transaction');
296 if (!$ok) return $this->RollbackTrans();
297 if ($this->transCnt) $this->transCnt -= 1;
298 sqlsrv_commit($this->_connectionID);
299 return true;
300 }
301 function RollbackTrans()
302 {
303 if ($this->transOff) return true;
304 if ($this->debug) error_log('<hr>rollback transaction');
305 if ($this->transCnt) $this->transCnt -= 1;
306 sqlsrv_rollback($this->_connectionID);
307 return true;
308 }
309
310 function SetTransactionMode( $transaction_mode )
311 {
312 $this->_transmode = $transaction_mode;
313 if (empty($transaction_mode)) {
314 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
315 return;
316 }
317 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
318 $this->Execute("SET TRANSACTION ".$transaction_mode);
319 }
320
321 /*
322 Usage:
323
324 $this->BeginTrans();
325 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
326
327 # some operation on both tables table1 and table2
328
329 $this->CommitTrans();
330
331 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
332 */
333 function RowLock($tables,$where,$flds='top 1 null as ignore')
334 {
335 if (!$this->transCnt) $this->BeginTrans();
336 return $this->GetOne("select $flds from $tables with (ROWLOCK,HOLDLOCK) where $where");
337 }
338
339 function SelectDB($dbName)
340 {
341 $this->database = $dbName;
342 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
343 if ($this->_connectionID) {
344 $rs = $this->Execute('USE '.$dbName);
345 if($rs) {
346 return true;
347 } else return false;
348 }
349 else return false;
350 }
351
352 function ErrorMsg()
353 {
354 $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL);
355 if($retErrors != null) {
356 foreach($retErrors as $arrError) {
357 $this->_errorMsg .= "SQLState: ".$arrError[ 'SQLSTATE']."\n";
358 $this->_errorMsg .= "Error Code: ".$arrError[ 'code']."\n";
359 $this->_errorMsg .= "Message: ".$arrError[ 'message']."\n";
360 }
361 } else {
362 $this->_errorMsg = "No errors found";
363 }
364 return $this->_errorMsg;
365 }
366
367 function ErrorNo()
368 {
369 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
370 $err = sqlsrv_errors(SQLSRV_ERR_ALL);
371 if($err[0]) return $err[0]['code'];
372 else return -1;
373 }
374
375 // returns true or false
376 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
377 {
378 if (!function_exists('sqlsrv_connect')) return null;
379 $connectionInfo = array("Database"=>$argDatabasename,'UID'=>$argUsername,'PWD'=>$argPassword);
380 if ($this->debug) error_log("<hr>connecting... hostname: $argHostname params: ".var_export($connectionInfo,true));
381 //if ($this->debug) error_log("<hr>_connectionID before: ".serialize($this->_connectionID));
382 if(!($this->_connectionID = sqlsrv_connect($argHostname,$connectionInfo))) {
383 if ($this->debug) error_log( "<hr><b>errors</b>: ".print_r( sqlsrv_errors(), true));
384 return false;
385 }
386 //if ($this->debug) error_log(" _connectionID after: ".serialize($this->_connectionID));
387 //if ($this->debug) error_log("<hr>defined functions: <pre>".var_export(get_defined_functions(),true)."</pre>");
388 return true;
389 }
390
391 // returns true or false
392 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
393 {
394 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!)
395 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
396 }
397
398 function Prepare($sql)
399 {
400 $stmt = sqlsrv_prepare( $this->_connectionID, $sql);
401 if (!$stmt) return $sql;
402 return array($sql,$stmt);
403 }
404
405 // returns concatenated string
406 // MSSQL requires integers to be cast as strings
407 // automatically cast every datatype to VARCHAR(255)
408 // @author David Rogers (introspectshun)
409 function Concat()
410 {
411 $s = "";
412 $arr = func_get_args();
413
414 // Split single record on commas, if possible
415 if (sizeof($arr) == 1) {
416 foreach ($arr as $arg) {
417 $args = explode(',', $arg);
418 }
419 $arr = $args;
420 }
421
422 array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
423 $s = implode('+',$arr);
424 if (sizeof($arr) > 0) return "$s";
425
426 return '';
427 }
428
429 /*
430 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
431 So all your blobs must be of type "image".
432
433 Remember to set in php.ini the following...
434
435 ; Valid range 0 - 2147483647. Default = 4096.
436 mssql.textlimit = 0 ; zero to pass through
437
438 ; Valid range 0 - 2147483647. Default = 4096.
439 mssql.textsize = 0 ; zero to pass through
440 */
441 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
442 {
443
444 if (strtoupper($blobtype) == 'CLOB') {
445 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
446 return $this->Execute($sql) != false;
447 }
448 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
449 return $this->Execute($sql) != false;
450 }
451
452 // returns query ID if successful, otherwise false
453 function _query($sql,$inputarr=false)
454 {
455 $this->_errorMsg = false;
456 if (is_array($inputarr)) {
457 $rez = sqlsrv_query($this->_connectionID,$sql,$inputarr);
458 } else if (is_array($sql)) {
459 $rez = sqlsrv_query($this->_connectionID,$sql[1],$inputarr);
460 } else {
461 $rez = sqlsrv_query($this->_connectionID,$sql);
462 }
463 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)
464 //fix for returning true on anything besides select statements
465 if (is_array($sql)) $sql = $sql[1];
466 $sql = ltrim($sql);
467 if(stripos($sql, 'SELECT') !== 0 && $rez !== false) {
468 if ($this->debug) error_log(" isn't a select query, returning boolean true");
469 return true;
470 }
471 //end fix
472 if(!$rez) $rez = false;
473 return $rez;
474 }
475
476 // returns true or false
477 function _close()
478 {
479 if ($this->transCnt) $this->RollbackTrans();
480 $rez = @sqlsrv_close($this->_connectionID);
481 $this->_connectionID = false;
482 return $rez;
483 }
484
485 // mssql uses a default date like Dec 30 2000 12:00AM
486 static function UnixDate($v)
487 {
488 return ADORecordSet_array_mssql::UnixDate($v);
489 }
490
491 static function UnixTimeStamp($v)
492 {
493 return ADORecordSet_array_mssql::UnixTimeStamp($v);
494 }
495
496 function &MetaIndexes($table,$primary=false)
497 {
498 $table = $this->qstr($table);
499
500 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
501 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,
502 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
503 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
504 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
505 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
506 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
507 ORDER BY O.name, I.Name, K.keyno";
508
509 global $ADODB_FETCH_MODE;
510 $save = $ADODB_FETCH_MODE;
511 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
512 if ($this->fetchMode !== FALSE) {
513 $savem = $this->SetFetchMode(FALSE);
514 }
515
516 $rs = $this->Execute($sql);
517 if (isset($savem)) {
518 $this->SetFetchMode($savem);
519 }
520 $ADODB_FETCH_MODE = $save;
521
522 if (!is_object($rs)) {
523 return FALSE;
524 }
525
526 $indexes = array();
527 while ($row = $rs->FetchRow()) {
528 if (!$primary && $row[5]) continue;
529
530 $indexes[$row[0]]['unique'] = $row[6];
531 $indexes[$row[0]]['columns'][] = $row[1];
532 }
533 return $indexes;
534 }
535
536 function MetaForeignKeys($table, $owner=false, $upper=false)
537 {
538 global $ADODB_FETCH_MODE;
539
540 $save = $ADODB_FETCH_MODE;
541 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
542 $table = $this->qstr(strtoupper($table));
543
544 $sql =
545 "select object_name(constid) as constraint_name,
546 col_name(fkeyid, fkey) as column_name,
547 object_name(rkeyid) as referenced_table_name,
548 col_name(rkeyid, rkey) as referenced_column_name
549 from sysforeignkeys
550 where upper(object_name(fkeyid)) = $table
551 order by constraint_name, referenced_table_name, keyno";
552
553 $constraints =& $this->GetArray($sql);
554
555 $ADODB_FETCH_MODE = $save;
556
557 $arr = false;
558 foreach($constraints as $constr) {
559 //print_r($constr);
560 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
561 }
562 if (!$arr) return false;
563
564 $arr2 = false;
565
566 foreach($arr as $k => $v) {
567 foreach($v as $a => $b) {
568 if ($upper) $a = strtoupper($a);
569 $arr2[$a] = $b;
570 }
571 }
572 return $arr2;
573 }
574
575 //From: Fernando Moreira <FMoreira@imediata.pt>
576 function MetaDatabases()
577 {
578 $this->SelectDB("master");
579 $rs =& $this->Execute($this->metaDatabasesSQL);
580 $rows = $rs->GetRows();
581 $ret = array();
582 for($i=0;$i<count($rows);$i++) {
583 $ret[] = $rows[$i][0];
584 }
585 $this->SelectDB($this->database);
586 if($ret)
587 return $ret;
588 else
589 return false;
590 }
591
592 // "Stein-Aksel Basma" <basma@accelero.no>
593 // tested with MSSQL 2000
594 function &MetaPrimaryKeys($table)
595 {
596 global $ADODB_FETCH_MODE;
597
598 $schema = '';
599 $this->_findschema($table,$schema);
600 if (!$schema) $schema = $this->database;
601 if ($schema) $schema = "and k.table_catalog like '$schema%'";
602
603 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
604 information_schema.table_constraints tc
605 where tc.constraint_name = k.constraint_name and tc.constraint_type =
606 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
607
608 $savem = $ADODB_FETCH_MODE;
609 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
610 $a = $this->GetCol($sql);
611 $ADODB_FETCH_MODE = $savem;
612
613 if ($a && sizeof($a)>0) return $a;
614 $false = false;
615 return $false;
616 }
617
618
619 function &MetaTables($ttype=false,$showSchema=false,$mask=false)
620 {
621 if ($mask) {
622 $save = $this->metaTablesSQL;
623 $mask = $this->qstr(($mask));
624 $this->metaTablesSQL .= " AND name like $mask";
625 }
626 $ret =& ADOConnection::MetaTables($ttype,$showSchema);
627
628 if ($mask) {
629 $this->metaTablesSQL = $save;
630 }
631 return $ret;
632 }
633 }
634
635 /*--------------------------------------------------------------------------------------
636 Class Name: Recordset
637 --------------------------------------------------------------------------------------*/
638
639 class ADORecordset_mssqlnative extends ADORecordSet {
640
641 var $databaseType = "mssqlnative";
642 var $canSeek = false;
643 var $fieldOffset = 0;
644 // _mths works only in non-localised system
645
646 function ADORecordset_mssqlnative($id,$mode=false)
647 {
648 if ($mode === false) {
649 global $ADODB_FETCH_MODE;
650 $mode = $ADODB_FETCH_MODE;
651
652 }
653 $this->fetchMode = $mode;
654 return $this->ADORecordSet($id,$mode);
655 }
656
657
658 function _initrs()
659 {
660 global $ADODB_COUNTRECS;
661 if ($this->connection->debug) error_log("(before) ADODB_COUNTRECS: {$ADODB_COUNTRECS} _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
662 /*$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."
663 error_log("rowsaff: ".serialize($retRowsAff));
664 $this->_numOfRows = ($ADODB_COUNTRECS)? $retRowsAff:-1;*/
665 $this->_numOfRows = -1;//not supported
666 $fieldmeta = sqlsrv_field_metadata($this->_queryID);
667 $this->_numOfFields = ($fieldmeta)? count($fieldmeta):-1;
668 if ($this->connection->debug) error_log("(after) _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
669 }
670
671
672 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
673 // get next resultset - requires PHP 4.0.5 or later
674 function NextRecordSet()
675 {
676 if (!sqlsrv_next_result($this->_queryID)) return false;
677 $this->_inited = false;
678 $this->bind = false;
679 $this->_currentRow = -1;
680 $this->Init();
681 return true;
682 }
683
684 /* Use associative array to get fields array */
685 function Fields($colname)
686 {
687 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
688 if (!$this->bind) {
689 $this->bind = array();
690 for ($i=0; $i < $this->_numOfFields; $i++) {
691 $o = $this->FetchField($i);
692 $this->bind[strtoupper($o->name)] = $i;
693 }
694 }
695
696 return $this->fields[$this->bind[strtoupper($colname)]];
697 }
698
699 /* Returns: an object containing field information.
700 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
701 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
702 fetchField() is retrieved. */
703
704 function &FetchField($fieldOffset = -1)
705 {
706 if ($this->connection->debug) error_log("<hr>fetchfield: $fieldOffset, fetch array: <pre>".print_r($this->fields,true)."</pre> backtrace: ".adodb_backtrace(false));
707 if ($fieldOffset != -1) $this->fieldOffset = $fieldOffset;
708 $arrKeys = array_keys($this->fields);
709 if(array_key_exists($this->fieldOffset,$arrKeys) && !array_key_exists($arrKeys[$this->fieldOffset],$this->fields)) {
710 $f = false;
711 } else {
712 $f = $this->fields[ $arrKeys[$this->fieldOffset] ];
713 if($fieldOffset == -1) $this->fieldOffset++;
714 }
715
716 if (empty($f)) {
717 $f = false;//PHP Notice: Only variable references should be returned by reference
718 }
719 return $f;
720 }
721
722 function _seek($row)
723 {
724 return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams.
725 }
726
727 // speedup
728 function MoveNext()
729 {
730 if ($this->connection->debug) error_log("movenext()");
731 //if ($this->connection->debug) error_log("eof (beginning): ".$this->EOF);
732 if ($this->EOF) return false;
733
734 $this->_currentRow++;
735 if ($this->connection->debug) error_log("_currentRow: ".$this->_currentRow);
736
737 if ($this->_fetch()) return true;
738 $this->EOF = true;
739 //if ($this->connection->debug) error_log("eof (end): ".$this->EOF);
740
741 return false;
742 }
743
744
745 // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
746 // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
747 function _fetch($ignore_fields=false)
748 {
749 if ($this->connection->debug) error_log("_fetch()");
750 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
751 if ($this->fetchMode & ADODB_FETCH_NUM) {
752 if ($this->connection->debug) error_log("fetch mode: both");
753 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH);
754 } else {
755 if ($this->connection->debug) error_log("fetch mode: assoc");
756 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC);
757 }
758
759 if (ADODB_ASSOC_CASE == 0) {
760 foreach($this->fields as $k=>$v) {
761 $this->fields[strtolower($k)] = $v;
762 }
763 } else if (ADODB_ASSOC_CASE == 1) {
764 foreach($this->fields as $k=>$v) {
765 $this->fields[strtoupper($k)] = $v;
766 }
767 }
768 } else {
769 if ($this->connection->debug) error_log("fetch mode: num");
770 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC);
771 }
772 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 $arrFixed = array();
774 foreach($this->fields as $key=>$value) {
775 if(is_numeric($key)) {
776 $arrFixed[$key-1] = $value;
777 } else {
778 $arrFixed[$key] = $value;
779 }
780 }
781 //if($this->connection->debug) error_log("<hr>fixing non 0 based return array, old: ".print_r($this->fields,true)." new: ".print_r($arrFixed,true));
782 $this->fields = $arrFixed;
783 }
784 if(is_array($this->fields)) {
785 foreach($this->fields as $key=>$value) {
786 if (is_object($value) && method_exists($value, 'format')) {//is DateTime object
787 $this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z");
788 }
789 }
790 }
791 if($this->fields === null) $this->fields = false;
792 if ($this->connection->debug) error_log("<hr>after _fetch, fields: <pre>".print_r($this->fields,true)." backtrace: ".adodb_backtrace(false));
793 return $this->fields;
794 }
795
796 /* close() only needs to be called if you are worried about using too much memory while your script
797 is running. All associated result memory for the specified result identifier will automatically be freed. */
798 function _close()
799 {
800 $rez = sqlsrv_free_stmt($this->_queryID);
801 $this->_queryID = false;
802 return $rez;
803 }
804
805 // mssql uses a default date like Dec 30 2000 12:00AM
806 static function UnixDate($v)
807 {
808 return ADORecordSet_array_mssqlnative::UnixDate($v);
809 }
810
811 static function UnixTimeStamp($v)
812 {
813 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
814 }
815 }
816
817
818 class ADORecordSet_array_mssqlnative extends ADORecordSet_array {
819 function ADORecordSet_array_mssqlnative($id=-1,$mode=false)
820 {
821 $this->ADORecordSet_array($id,$mode);
822 }
823
824 // mssql uses a default date like Dec 30 2000 12:00AM
825 static function UnixDate($v)
826 {
827
828 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
829
830 global $ADODB_mssql_mths,$ADODB_mssql_date_order;
831
832 //Dec 30 2000 12:00AM
833 if ($ADODB_mssql_date_order == 'dmy') {
834 if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
835 return parent::UnixDate($v);
836 }
837 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
838
839 $theday = $rr[1];
840 $themth = substr(strtoupper($rr[2]),0,3);
841 } else {
842 if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
843 return parent::UnixDate($v);
844 }
845 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
846
847 $theday = $rr[2];
848 $themth = substr(strtoupper($rr[1]),0,3);
849 }
850 $themth = $ADODB_mssql_mths[$themth];
851 if ($themth <= 0) return false;
852 // h-m-s-MM-DD-YY
853 return mktime(0,0,0,$themth,$theday,$rr[3]);
854 }
855
856 static function UnixTimeStamp($v)
857 {
858
859 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
860
861 global $ADODB_mssql_mths,$ADODB_mssql_date_order;
862
863 //Dec 30 2000 12:00AM
864 if ($ADODB_mssql_date_order == 'dmy') {
865 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})|"
866 ,$v, $rr)) return parent::UnixTimeStamp($v);
867 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
868
869 $theday = $rr[1];
870 $themth = substr(strtoupper($rr[2]),0,3);
871 } else {
872 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})|"
873 ,$v, $rr)) return parent::UnixTimeStamp($v);
874 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
875
876 $theday = $rr[2];
877 $themth = substr(strtoupper($rr[1]),0,3);
878 }
879
880 $themth = $ADODB_mssql_mths[$themth];
881 if ($themth <= 0) return false;
882
883 switch (strtoupper($rr[6])) {
884 case 'P':
885 if ($rr[4]<12) $rr[4] += 12;
886 break;
887 case 'A':
888 if ($rr[4]==12) $rr[4] = 0;
889 break;
890 default:
891 break;
892 }
893 // h-m-s-MM-DD-YY
894 return mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
895 }
896 }
897
898 /*
899 Code Example 1:
900
901 select object_name(constid) as constraint_name,
902 object_name(fkeyid) as table_name,
903 col_name(fkeyid, fkey) as column_name,
904 object_name(rkeyid) as referenced_table_name,
905 col_name(rkeyid, rkey) as referenced_column_name
906 from sysforeignkeys
907 where object_name(fkeyid) = x
908 order by constraint_name, table_name, referenced_table_name, keyno
909
910 Code Example 2:
911 select constraint_name,
912 column_name,
913 ordinal_position
914 from information_schema.key_column_usage
915 where constraint_catalog = db_name()
916 and table_name = x
917 order by constraint_name, ordinal_position
918
919 http://www.databasejournal.com/scripts/article.php/1440551
920 */
921
922 ?>