[BUGFIX] MSSQL native driver for ADOdb returns erroneous message
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / drivers / adodb-mssqlnative.inc.php
1 <?php
2 /*
3 V5.19 23-Apr-2014 (c) 2000-2014 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 class ADODB_mssqlnative extends ADOConnection {
82 var $databaseType = "mssqlnative";
83 var $dataProvider = "mssqlnative";
84 var $replaceQuote = "''"; // string to use to replace quotes
85 var $fmtDate = "'Y-m-d'";
86 var $fmtTimeStamp = "'Y-m-d\TH:i:s'";
87 var $hasInsertID = true;
88 var $substr = "substring";
89 var $length = 'len';
90 var $hasAffectedRows = true;
91 var $poorAffectedRows = false;
92 var $metaDatabasesSQL = "select name from sys.sysdatabases where name <> 'master'";
93 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'))";
94 var $metaColumnsSQL =
95 "select c.name,
96 t.name as type,
97 c.length,
98 c.xprec as precision,
99 c.xscale as scale,
100 c.isnullable as nullable,
101 c.cdefault as default_value,
102 c.xtype,
103 t.length as type_length,
104 sc.is_identity
105 from syscolumns c
106 join systypes t on t.xusertype=c.xusertype
107 join sysobjects o on o.id=c.id
108 join sys.tables st on st.name=o.name
109 join sys.columns sc on sc.object_id = st.object_id and sc.name=c.name
110 where o.name='%s'";
111 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE
112 var $hasGenID = true;
113 var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)';
114 var $sysTimeStamp = 'GetDate()';
115 var $maxParameterLen = 4000;
116 var $arrayClass = 'ADORecordSet_array_mssqlnative';
117 var $uniqueSort = true;
118 var $leftOuter = '*=';
119 var $rightOuter = '=*';
120 var $ansiOuter = true; // for mssql7 or later
121 var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000
122 var $uniqueOrderBy = true;
123 var $_bindInputArray = true;
124 var $_dropSeqSQL = "drop table %s";
125 var $connectionInfo = array();
126 var $sequences = false;
127 var $mssql_version = '';
128
129 function ADODB_mssqlnative()
130 {
131 if ($this->debug) {
132 error_log("<pre>");
133 sqlsrv_set_error_handling( SQLSRV_ERRORS_LOG_ALL );
134 sqlsrv_log_set_severity( SQLSRV_LOG_SEVERITY_ALL );
135 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
136 sqlsrv_configure('warnings_return_as_errors', 0);
137 } else {
138 sqlsrv_set_error_handling(0);
139 sqlsrv_log_set_severity(0);
140 sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
141 sqlsrv_configure('warnings_return_as_errors', 0);
142 }
143 }
144 function ServerVersion() {
145 $data = $this->ServerInfo();
146 if (preg_match('/^09/',$data['version'])){
147 /*
148 * SQL Server 2005
149 */
150 $this->mssql_version = 9;
151 } elseif (preg_match('/^10/',$data['version'])){
152 /*
153 * SQL Server 2008
154 */
155 $this->mssql_version = 10;
156 } elseif (preg_match('/^11/',$data['version'])){
157 /*
158 * SQL Server 2012
159 */
160 $this->mssql_version = 11;
161 } else
162 die("SQL SERVER VERSION {$data['version']} NOT SUPPORTED IN mssqlnative DRIVER");
163 }
164
165 function ServerInfo() {
166 global $ADODB_FETCH_MODE;
167 static $arr = false;
168 if (is_array($arr))
169 return $arr;
170 if ($this->fetchMode === false) {
171 $savem = $ADODB_FETCH_MODE;
172 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
173 } elseif ($this->fetchMode >=0 && $this->fetchMode <=2) {
174 $savem = $this->fetchMode;
175 } else
176 $savem = $this->SetFetchMode(ADODB_FETCH_NUM);
177
178 $arrServerInfo = sqlsrv_server_info($this->_connectionID);
179 $ADODB_FETCH_MODE = $savem;
180 $arr['description'] = $arrServerInfo['SQLServerName'].' connected to '.$arrServerInfo['CurrentDatabase'];
181 $arr['version'] = $arrServerInfo['SQLServerVersion'];//ADOConnection::_findvers($arr['description']);
182 return $arr;
183 }
184
185 function IfNull( $field, $ifNull )
186 {
187 return " ISNULL($field, $ifNull) "; // if MS SQL Server
188 }
189
190 function _insertid()
191 {
192 // SCOPE_IDENTITY()
193 // Returns the last IDENTITY value inserted into an IDENTITY column in
194 // the same scope. A scope is a module -- a stored procedure, trigger,
195 // function, or batch. Thus, two statements are in the same scope if
196 // they are in the same stored procedure, function, or batch.
197 return $this->lastInsertID;
198 }
199
200 function _affectedrows()
201 {
202 if ($this->_queryID)
203 return sqlsrv_rows_affected($this->_queryID);
204 }
205
206 function GenID($seq='adodbseq',$start=1) {
207 if (!$this->mssql_version)
208 $this->ServerVersion();
209 switch($this->mssql_version){
210 case 9:
211 case 10:
212 return $this->GenID2008();
213 break;
214 case 11:
215 return $this->GenID2012();
216 break;
217 }
218 }
219
220 function CreateSequence($seq='adodbseq',$start=1)
221 {
222 if (!$this->mssql_vesion)
223 $this->ServerVersion();
224
225 switch($this->mssql_version){
226 case 9:
227 case 10:
228 return $this->CreateSequence2008();
229 break;
230 case 11:
231 return $this->CreateSequence2012();
232 break;
233 }
234
235 }
236
237 /**
238 * For Server 2005,2008, duplicate a sequence with an identity table
239 */
240 function CreateSequence2008($seq='adodbseq',$start=1)
241 {
242 if($this->debug) error_log("<hr>CreateSequence($seq,$start)");
243 sqlsrv_begin_transaction($this->_connectionID);
244 $start -= 1;
245 $this->Execute("create table $seq (id int)");//was float(53)
246 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
247 if (!$ok) {
248 if($this->debug) error_log("<hr>Error: ROLLBACK");
249 sqlsrv_rollback($this->_connectionID);
250 return false;
251 }
252 sqlsrv_commit($this->_connectionID);
253 return true;
254 }
255
256 /**
257 * Proper Sequences Only available to Server 2012 and up
258 */
259 function CreateSequence2012($seq='adodb',$start=1){
260 if (!$this->sequences){
261 $sql = "SELECT name FROM sys.sequences";
262 $this->sequences = $this->GetCol($sql);
263 }
264 $ok = $this->Execute("CREATE SEQUENCE $seq START WITH $start INCREMENT BY 1");
265 if (!$ok)
266 die("CANNOT CREATE SEQUENCE" . print_r(sqlsrv_errors(),true));
267 $this->sequences[] = $seq;
268 }
269
270 /**
271 * For Server 2005,2008, duplicate a sequence with an identity table
272 */
273 function GenID2008($seq='adodbseq',$start=1)
274 {
275 if($this->debug) error_log("<hr>CreateSequence($seq,$start)");
276 sqlsrv_begin_transaction($this->_connectionID);
277 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
278 if (!$ok) {
279 $start -= 1;
280 $this->Execute("create table $seq (id int)");//was float(53)
281 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
282 if (!$ok) {
283 if($this->debug) error_log("<hr>Error: ROLLBACK");
284 sqlsrv_rollback($this->_connectionID);
285 return false;
286 }
287 }
288 $num = $this->GetOne("select id from $seq");
289 sqlsrv_commit($this->_connectionID);
290 return true;
291 }
292 /**
293 * Only available to Server 2012 and up
294 * Cannot do this the normal adodb way by trapping an error if the
295 * sequence does not exist because sql server will auto create a
296 * sequence with the starting number of -9223372036854775808
297 */
298 function GenID2012($seq='adodbseq',$start=1)
299 {
300
301 /*
302 * First time in create an array of sequence names that we
303 * can use in later requests to see if the sequence exists
304 * the overhead is creating a list of sequences every time
305 * we need access to at least 1. If we really care about
306 * performance, we could maybe flag a 'nocheck' class variable
307 */
308 if (!$this->sequences){
309 $sql = "SELECT name FROM sys.sequences";
310 $this->sequences = $this->GetCol($sql);
311 }
312 if (!is_array($this->sequences)
313 || is_array($this->sequences) && !in_array($seq,$this->sequences)){
314 $this->CreateSequence2012($seq='adodbseq',$start=1);
315
316 }
317 $num = $this->GetOne("SELECT NEXT VALUE FOR $seq");
318 return $num;
319 }
320
321 // Format date column in sql string given an input format that understands Y M D
322 function SQLDate($fmt, $col=false)
323 {
324 if (!$col) $col = $this->sysTimeStamp;
325 $s = '';
326
327 $len = strlen($fmt);
328 for ($i=0; $i < $len; $i++) {
329 if ($s) $s .= '+';
330 $ch = $fmt[$i];
331 switch($ch) {
332 case 'Y':
333 case 'y':
334 $s .= "datename(yyyy,$col)";
335 break;
336 case 'M':
337 $s .= "convert(char(3),$col,0)";
338 break;
339 case 'm':
340 $s .= "replace(str(month($col),2),' ','0')";
341 break;
342 case 'Q':
343 case 'q':
344 $s .= "datename(quarter,$col)";
345 break;
346 case 'D':
347 case 'd':
348 $s .= "replace(str(day($col),2),' ','0')";
349 break;
350 case 'h':
351 $s .= "substring(convert(char(14),$col,0),13,2)";
352 break;
353
354 case 'H':
355 $s .= "replace(str(datepart(hh,$col),2),' ','0')";
356 break;
357
358 case 'i':
359 $s .= "replace(str(datepart(mi,$col),2),' ','0')";
360 break;
361 case 's':
362 $s .= "replace(str(datepart(ss,$col),2),' ','0')";
363 break;
364 case 'a':
365 case 'A':
366 $s .= "substring(convert(char(19),$col,0),18,2)";
367 break;
368
369 default:
370 if ($ch == '\\') {
371 $i++;
372 $ch = substr($fmt,$i,1);
373 }
374 $s .= $this->qstr($ch);
375 break;
376 }
377 }
378 return $s;
379 }
380
381
382 function BeginTrans()
383 {
384 if ($this->transOff) return true;
385 $this->transCnt += 1;
386 if ($this->debug) error_log('<hr>begin transaction');
387 sqlsrv_begin_transaction($this->_connectionID);
388 return true;
389 }
390
391 function CommitTrans($ok=true)
392 {
393 if ($this->transOff) return true;
394 if ($this->debug) error_log('<hr>commit transaction');
395 if (!$ok) return $this->RollbackTrans();
396 if ($this->transCnt) $this->transCnt -= 1;
397 sqlsrv_commit($this->_connectionID);
398 return true;
399 }
400 function RollbackTrans()
401 {
402 if ($this->transOff) return true;
403 if ($this->debug) error_log('<hr>rollback transaction');
404 if ($this->transCnt) $this->transCnt -= 1;
405 sqlsrv_rollback($this->_connectionID);
406 return true;
407 }
408
409 function SetTransactionMode( $transaction_mode )
410 {
411 $this->_transmode = $transaction_mode;
412 if (empty($transaction_mode)) {
413 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
414 return;
415 }
416 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
417 $this->Execute("SET TRANSACTION ".$transaction_mode);
418 }
419
420 /*
421 Usage:
422
423 $this->BeginTrans();
424 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
425
426 # some operation on both tables table1 and table2
427
428 $this->CommitTrans();
429
430 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
431 */
432 function RowLock($tables,$where,$col='1 as adodbignore')
433 {
434 if ($col == '1 as adodbignore') $col = 'top 1 null as ignore';
435 if (!$this->transCnt) $this->BeginTrans();
436 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where");
437 }
438
439 function SelectDB($dbName)
440 {
441 $this->database = $dbName;
442 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
443 if ($this->_connectionID) {
444 $rs = $this->Execute('USE '.$dbName);
445 if($rs) {
446 return true;
447 } else return false;
448 }
449 else return false;
450 }
451
452 function ErrorMsg()
453 {
454 $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL);
455 if($retErrors != null) {
456 foreach($retErrors as $arrError) {
457 $this->_errorMsg .= "SQLState: ".$arrError[ 'SQLSTATE']."\n";
458 $this->_errorMsg .= "Error Code: ".$arrError[ 'code']."\n";
459 $this->_errorMsg .= "Message: ".$arrError[ 'message']."\n";
460 }
461 } else {
462 $this->_errorMsg = '';
463 }
464 return $this->_errorMsg;
465 }
466
467 function ErrorNo()
468 {
469 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
470 $err = sqlsrv_errors(SQLSRV_ERR_ALL);
471 if($err[0]) return $err[0]['code'];
472 else return -1;
473 }
474
475 // returns true or false
476 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
477 {
478 if (!function_exists('sqlsrv_connect')) return null;
479 $connectionInfo = $this->connectionInfo;
480 $connectionInfo["Database"]=$argDatabasename;
481 $connectionInfo["UID"]=$argUsername;
482 $connectionInfo["PWD"]=$argPassword;
483 if ($this->debug) error_log("<hr>connecting... hostname: $argHostname params: ".var_export($connectionInfo,true));
484 //if ($this->debug) error_log("<hr>_connectionID before: ".serialize($this->_connectionID));
485 if(!($this->_connectionID = sqlsrv_connect($argHostname,$connectionInfo))) {
486 if ($this->debug) error_log( "<hr><b>errors</b>: ".print_r( sqlsrv_errors(), true));
487 return false;
488 }
489 //if ($this->debug) error_log(" _connectionID after: ".serialize($this->_connectionID));
490 //if ($this->debug) error_log("<hr>defined functions: <pre>".var_export(get_defined_functions(),true)."</pre>");
491 return true;
492 }
493
494 // returns true or false
495 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
496 {
497 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!)
498 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
499 }
500
501 function Prepare($sql)
502 {
503 return $sql; // prepare does not work properly with bind parameters as bind parameters are managed by sqlsrv_prepare!
504
505 $stmt = sqlsrv_prepare( $this->_connectionID, $sql);
506 if (!$stmt) return $sql;
507 return array($sql,$stmt);
508 }
509
510 // returns concatenated string
511 // MSSQL requires integers to be cast as strings
512 // automatically cast every datatype to VARCHAR(255)
513 // @author David Rogers (introspectshun)
514 function Concat()
515 {
516 $s = "";
517 $arr = func_get_args();
518
519 // Split single record on commas, if possible
520 if (sizeof($arr) == 1) {
521 foreach ($arr as $arg) {
522 $args = explode(',', $arg);
523 }
524 $arr = $args;
525 }
526
527 array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
528 $s = implode('+',$arr);
529 if (sizeof($arr) > 0) return "$s";
530
531 return '';
532 }
533
534 /*
535 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
536 So all your blobs must be of type "image".
537
538 Remember to set in php.ini the following...
539
540 ; Valid range 0 - 2147483647. Default = 4096.
541 mssql.textlimit = 0 ; zero to pass through
542
543 ; Valid range 0 - 2147483647. Default = 4096.
544 mssql.textsize = 0 ; zero to pass through
545 */
546 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
547 {
548
549 if (strtoupper($blobtype) == 'CLOB') {
550 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
551 return $this->Execute($sql) != false;
552 }
553 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
554 return $this->Execute($sql) != false;
555 }
556
557 // returns query ID if successful, otherwise false
558 function _query($sql,$inputarr=false)
559 {
560 $this->_errorMsg = false;
561
562 if (is_array($sql)) $sql = $sql[1];
563
564 $insert = false;
565 // handle native driver flaw for retrieving the last insert ID
566 if(preg_match('/^\W*(insert [^;]+);?$/i', $sql)) {
567 $insert = true;
568 $sql .= '; '.$this->identitySQL; // select scope_identity()
569 }
570 if($inputarr) {
571 $rez = sqlsrv_query($this->_connectionID, $sql, $inputarr);
572 } else {
573 $rez = sqlsrv_query($this->_connectionID,$sql);
574 }
575
576 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));
577
578 if(!$rez) {
579 $rez = false;
580 } else if ($insert) {
581 // retrieve the last insert ID (where applicable)
582 sqlsrv_next_result($rez);
583 sqlsrv_fetch($rez);
584 $this->lastInsertID = sqlsrv_get_field($rez, 0);
585 }
586 return $rez;
587 }
588
589 // returns true or false
590 function _close()
591 {
592 if ($this->transCnt) $this->RollbackTrans();
593 $rez = @sqlsrv_close($this->_connectionID);
594 $this->_connectionID = false;
595 return $rez;
596 }
597
598 // mssql uses a default date like Dec 30 2000 12:00AM
599 static function UnixDate($v)
600 {
601 return ADORecordSet_array_mssqlnative::UnixDate($v);
602 }
603
604 static function UnixTimeStamp($v)
605 {
606 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
607 }
608
609 function MetaIndexes($table,$primary=false, $owner = false)
610 {
611 $table = $this->qstr($table);
612
613 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
614 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,
615 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
616 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
617 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
618 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
619 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
620 ORDER BY O.name, I.Name, K.keyno";
621
622 global $ADODB_FETCH_MODE;
623 $save = $ADODB_FETCH_MODE;
624 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
625 if ($this->fetchMode !== FALSE) {
626 $savem = $this->SetFetchMode(FALSE);
627 }
628
629 $rs = $this->Execute($sql);
630 if (isset($savem)) {
631 $this->SetFetchMode($savem);
632 }
633 $ADODB_FETCH_MODE = $save;
634
635 if (!is_object($rs)) {
636 return FALSE;
637 }
638
639 $indexes = array();
640 while ($row = $rs->FetchRow()) {
641 if (!$primary && $row[5]) continue;
642
643 $indexes[$row[0]]['unique'] = $row[6];
644 $indexes[$row[0]]['columns'][] = $row[1];
645 }
646 return $indexes;
647 }
648
649 function MetaForeignKeys($table, $owner=false, $upper=false)
650 {
651 global $ADODB_FETCH_MODE;
652
653 $save = $ADODB_FETCH_MODE;
654 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
655 $table = $this->qstr(strtoupper($table));
656
657 $sql =
658 "select object_name(constid) as constraint_name,
659 col_name(fkeyid, fkey) as column_name,
660 object_name(rkeyid) as referenced_table_name,
661 col_name(rkeyid, rkey) as referenced_column_name
662 from sysforeignkeys
663 where upper(object_name(fkeyid)) = $table
664 order by constraint_name, referenced_table_name, keyno";
665
666 $constraints =& $this->GetArray($sql);
667
668 $ADODB_FETCH_MODE = $save;
669
670 $arr = false;
671 foreach($constraints as $constr) {
672 //print_r($constr);
673 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
674 }
675 if (!$arr) return false;
676
677 $arr2 = false;
678
679 foreach($arr as $k => $v) {
680 foreach($v as $a => $b) {
681 if ($upper) $a = strtoupper($a);
682 $arr2[$a] = $b;
683 }
684 }
685 return $arr2;
686 }
687
688 //From: Fernando Moreira <FMoreira@imediata.pt>
689 function MetaDatabases()
690 {
691 $this->SelectDB("master");
692 $rs =& $this->Execute($this->metaDatabasesSQL);
693 $rows = $rs->GetRows();
694 $ret = array();
695 for($i=0;$i<count($rows);$i++) {
696 $ret[] = $rows[$i][0];
697 }
698 $this->SelectDB($this->database);
699 if($ret)
700 return $ret;
701 else
702 return false;
703 }
704
705 // "Stein-Aksel Basma" <basma@accelero.no>
706 // tested with MSSQL 2000
707 function MetaPrimaryKeys($table, $owner=false)
708 {
709 global $ADODB_FETCH_MODE;
710
711 $schema = '';
712 $this->_findschema($table,$schema);
713 if (!$schema) $schema = $this->database;
714 if ($schema) $schema = "and k.table_catalog like '$schema%'";
715
716 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
717 information_schema.table_constraints tc
718 where tc.constraint_name = k.constraint_name and tc.constraint_type =
719 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
720
721 $savem = $ADODB_FETCH_MODE;
722 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
723 $a = $this->GetCol($sql);
724 $ADODB_FETCH_MODE = $savem;
725
726 if ($a && sizeof($a)>0) return $a;
727 $false = false;
728 return $false;
729 }
730
731
732 function MetaTables($ttype=false,$showSchema=false,$mask=false)
733 {
734 if ($mask) {
735 $save = $this->metaTablesSQL;
736 $mask = $this->qstr(($mask));
737 $this->metaTablesSQL .= " AND name like $mask";
738 }
739 $ret = ADOConnection::MetaTables($ttype,$showSchema);
740
741 if ($mask) {
742 $this->metaTablesSQL = $save;
743 }
744 return $ret;
745 }
746 function MetaColumns($table, $upper=true, $schema=false){
747
748 # start adg
749 static $cached_columns = array();
750 if ($this->cachedSchemaFlush)
751 $cached_columns = array();
752
753 if (array_key_exists($table,$cached_columns)){
754 return $cached_columns[$table];
755 }
756 # end adg
757
758 if (!$this->mssql_version)
759 $this->ServerVersion();
760
761 $this->_findschema($table,$schema);
762 if ($schema) {
763 $dbName = $this->database;
764 $this->SelectDB($schema);
765 }
766 global $ADODB_FETCH_MODE;
767 $save = $ADODB_FETCH_MODE;
768 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
769
770 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
771 $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table));
772
773 if ($schema) {
774 $this->SelectDB($dbName);
775 }
776
777 if (isset($savem)) $this->SetFetchMode($savem);
778 $ADODB_FETCH_MODE = $save;
779 if (!is_object($rs)) {
780 $false = false;
781 return $false;
782 }
783
784 $retarr = array();
785 while (!$rs->EOF){
786
787 $fld = new ADOFieldObject();
788 if (array_key_exists(0,$rs->fields)) {
789 $fld->name = $rs->fields[0];
790 $fld->type = $rs->fields[1];
791 $fld->max_length = $rs->fields[2];
792 $fld->precision = $rs->fields[3];
793 $fld->scale = $rs->fields[4];
794 $fld->not_null =!$rs->fields[5];
795 $fld->has_default = $rs->fields[6];
796 $fld->xtype = $rs->fields[7];
797 $fld->type_length = $rs->fields[8];
798 $fld->auto_increment= $rs->fields[9];
799 } else {
800 $fld->name = $rs->fields['name'];
801 $fld->type = $rs->fields['type'];
802 $fld->max_length = $rs->fields['length'];
803 $fld->precision = $rs->fields['precision'];
804 $fld->scale = $rs->fields['scale'];
805 $fld->not_null =!$rs->fields['nullable'];
806 $fld->has_default = $rs->fields['default_value'];
807 $fld->xtype = $rs->fields['xtype'];
808 $fld->type_length = $rs->fields['type_length'];
809 $fld->auto_increment= $rs->fields['is_identity'];
810 }
811
812 if ($save == ADODB_FETCH_NUM)
813 $retarr[] = $fld;
814 else
815 $retarr[strtoupper($fld->name)] = $fld;
816
817 $rs->MoveNext();
818
819 }
820 $rs->Close();
821 # start adg
822 $cached_columns[$table] = $retarr;
823 # end adg
824 return $retarr;
825 }
826
827 }
828
829 /*--------------------------------------------------------------------------------------
830 Class Name: Recordset
831 --------------------------------------------------------------------------------------*/
832
833 class ADORecordset_mssqlnative extends ADORecordSet {
834
835 var $databaseType = "mssqlnative";
836 var $canSeek = false;
837 var $fieldOffset = 0;
838 // _mths works only in non-localised system
839
840 function ADORecordset_mssqlnative($id,$mode=false)
841 {
842 if ($mode === false) {
843 global $ADODB_FETCH_MODE;
844 $mode = $ADODB_FETCH_MODE;
845
846 }
847 $this->fetchMode = $mode;
848 return $this->ADORecordSet($id,$mode);
849 }
850
851
852 function _initrs()
853 {
854 global $ADODB_COUNTRECS;
855 # KMN # if ($this->connection->debug) error_log("(before) ADODB_COUNTRECS: {$ADODB_COUNTRECS} _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
856 /*$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."
857 error_log("rowsaff: ".serialize($retRowsAff));
858 $this->_numOfRows = ($ADODB_COUNTRECS)? $retRowsAff:-1;*/
859 $this->_numOfRows = -1;//not supported
860 $fieldmeta = sqlsrv_field_metadata($this->_queryID);
861 $this->_numOfFields = ($fieldmeta)? count($fieldmeta):-1;
862 # KMN # if ($this->connection->debug) error_log("(after) _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
863 /*
864 * Copy the oracle method and cache the metadata at init time
865 */
866 if ($this->_numOfFields>0) {
867 $this->_fieldobjs = array();
868 $max = $this->_numOfFields;
869 for ($i=0;$i<$max; $i++) $this->_fieldobjs[] = $this->_FetchField($i);
870 }
871
872 }
873
874
875 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
876 // get next resultset - requires PHP 4.0.5 or later
877 function NextRecordSet()
878 {
879 if (!sqlsrv_next_result($this->_queryID)) return false;
880 $this->_inited = false;
881 $this->bind = false;
882 $this->_currentRow = -1;
883 $this->Init();
884 return true;
885 }
886
887 /* Use associative array to get fields array */
888 function Fields($colname)
889 {
890 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
891 if (!$this->bind) {
892 $this->bind = array();
893 for ($i=0; $i < $this->_numOfFields; $i++) {
894 $o = $this->FetchField($i);
895 $this->bind[strtoupper($o->name)] = $i;
896 }
897 }
898
899 return $this->fields[$this->bind[strtoupper($colname)]];
900 }
901
902 /* Returns: an object containing field information.
903 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
904 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
905 fetchField() is retrieved.
906 Designed By jcortinap#jc.com.mx
907 */
908 function _FetchField($fieldOffset = -1)
909 {
910 $_typeConversion = array(
911 -155 => 'datetimeoffset',
912 -154 => 'time',
913 -152 => 'xml',
914 -151 => 'udt',
915 -11 => 'uniqueidentifier',
916 -10 => 'ntext',
917 -9 => 'nvarchar',
918 -8 => 'nchar',
919 -7 => 'bit',
920 -6 => 'tinyint',
921 -5 => 'bigint',
922 -4 => 'image',
923 -3 => 'varbinary',
924 -2 => 'timestamp',
925 -1 => 'text',
926 1 => 'char',
927 2 => 'numeric',
928 3 => 'decimal',
929 4 => 'int',
930 5 => 'smallint',
931 6 => 'float',
932 7 => 'real',
933 12 => 'varchar',
934 91 => 'date',
935 93 => 'datetime'
936 );
937
938 $fa = @sqlsrv_field_metadata($this->_queryID);
939 if ($fieldOffset != -1) {
940 $fa = $fa[$fieldOffset];
941 }
942 $false = false;
943 if (empty($fa)) {
944 $f = false;//PHP Notice: Only variable references should be returned by reference
945 }
946 else
947 {
948 // Convert to an object
949 $fa = array_change_key_case($fa, CASE_LOWER);
950 $fb = array();
951 if ($fieldOffset != -1)
952 {
953 $fb = array(
954 'name' => $fa['name'],
955 'max_length' => $fa['size'],
956 'column_source' => $fa['name'],
957 'type' => $_typeConversion[$fa['type']]
958 );
959 }
960 else
961 {
962 foreach ($fa as $key => $value)
963 {
964 $fb[] = array(
965 'name' => $value['name'],
966 'max_length' => $value['size'],
967 'column_source' => $value['name'],
968 'type' => $_typeConversion[$value['type']]
969 );
970 }
971 }
972 $f = (object) $fb;
973 }
974 return $f;
975 }
976
977 /*
978 * Fetchfield copies the oracle method, it loads the field information
979 * into the _fieldobjs array once, to save multiple calls to the
980 * sqlsrv_field_metadata function
981 *
982 * @author KM Newnham
983 * @date 02/20/2013
984 */
985 function FetchField($fieldOffset = -1)
986 {
987 return $this->_fieldobjs[$fieldOffset];
988 }
989
990 function _seek($row)
991 {
992 return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams.
993 }
994
995 // speedup
996 function MoveNext()
997 {
998 //# KMN # if ($this->connection->debug) error_log("movenext()");
999 //# KMN # if ($this->connection->debug) error_log("eof (beginning): ".$this->EOF);
1000 if ($this->EOF) return false;
1001
1002 $this->_currentRow++;
1003 // # KMN # if ($this->connection->debug) error_log("_currentRow: ".$this->_currentRow);
1004
1005 if ($this->_fetch()) return true;
1006 $this->EOF = true;
1007 //# KMN # if ($this->connection->debug) error_log("eof (end): ".$this->EOF);
1008
1009 return false;
1010 }
1011
1012
1013 // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
1014 // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
1015 function _fetch($ignore_fields=false)
1016 {
1017 # KMN # if ($this->connection->debug) error_log("_fetch()");
1018 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
1019 if ($this->fetchMode & ADODB_FETCH_NUM) {
1020 //# KMN # if ($this->connection->debug) error_log("fetch mode: both");
1021 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH);
1022 } else {
1023 //# KMN # if ($this->connection->debug) error_log("fetch mode: assoc");
1024 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC);
1025 }
1026
1027 if (is_array($this->fields)) {
1028 if (ADODB_ASSOC_CASE == 0) {
1029 foreach($this->fields as $k=>$v) {
1030 $this->fields[strtolower($k)] = $v;
1031 }
1032 } else if (ADODB_ASSOC_CASE == 1) {
1033 foreach($this->fields as $k=>$v) {
1034 $this->fields[strtoupper($k)] = $v;
1035 }
1036 }
1037 }
1038 } else {
1039 //# KMN # if ($this->connection->debug) error_log("fetch mode: num");
1040 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC);
1041 }
1042 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
1043 $arrFixed = array();
1044 foreach($this->fields as $key=>$value) {
1045 if(is_numeric($key)) {
1046 $arrFixed[$key-1] = $value;
1047 } else {
1048 $arrFixed[$key] = $value;
1049 }
1050 }
1051 //if($this->connection->debug) error_log("<hr>fixing non 0 based return array, old: ".print_r($this->fields,true)." new: ".print_r($arrFixed,true));
1052 $this->fields = $arrFixed;
1053 }
1054 if(is_array($this->fields)) {
1055 foreach($this->fields as $key=>$value) {
1056 if (is_object($value) && method_exists($value, 'format')) {//is DateTime object
1057 $this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z");
1058 }
1059 }
1060 }
1061 if($this->fields === null) $this->fields = false;
1062 # KMN # if ($this->connection->debug) error_log("<hr>after _fetch, fields: <pre>".print_r($this->fields,true)." backtrace: ".adodb_backtrace(false));
1063 return $this->fields;
1064 }
1065
1066 /* close() only needs to be called if you are worried about using too much memory while your script
1067 is running. All associated result memory for the specified result identifier will automatically be freed. */
1068 function _close()
1069 {
1070 $rez = sqlsrv_free_stmt($this->_queryID);
1071 $this->_queryID = false;
1072 return $rez;
1073 }
1074
1075 // mssql uses a default date like Dec 30 2000 12:00AM
1076 static function UnixDate($v)
1077 {
1078 return ADORecordSet_array_mssqlnative::UnixDate($v);
1079 }
1080
1081 static function UnixTimeStamp($v)
1082 {
1083 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
1084 }
1085 }
1086
1087
1088 class ADORecordSet_array_mssqlnative extends ADORecordSet_array {
1089 function ADORecordSet_array_mssqlnative($id=-1,$mode=false)
1090 {
1091 $this->ADORecordSet_array($id,$mode);
1092 }
1093
1094 // mssql uses a default date like Dec 30 2000 12:00AM
1095 static function UnixDate($v)
1096 {
1097
1098 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
1099
1100 global $ADODB_mssql_mths,$ADODB_mssql_date_order;
1101
1102 //Dec 30 2000 12:00AM
1103 if ($ADODB_mssql_date_order == 'dmy') {
1104 if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
1105 return parent::UnixDate($v);
1106 }
1107 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1108
1109 $theday = $rr[1];
1110 $themth = substr(strtoupper($rr[2]),0,3);
1111 } else {
1112 if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
1113 return parent::UnixDate($v);
1114 }
1115 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1116
1117 $theday = $rr[2];
1118 $themth = substr(strtoupper($rr[1]),0,3);
1119 }
1120 $themth = $ADODB_mssql_mths[$themth];
1121 if ($themth <= 0) return false;
1122 // h-m-s-MM-DD-YY
1123 return adodb_mktime(0,0,0,$themth,$theday,$rr[3]);
1124 }
1125
1126 static function UnixTimeStamp($v)
1127 {
1128
1129 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
1130
1131 global $ADODB_mssql_mths,$ADODB_mssql_date_order;
1132
1133 //Dec 30 2000 12:00AM
1134 if ($ADODB_mssql_date_order == 'dmy') {
1135 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})|"
1136 ,$v, $rr)) return parent::UnixTimeStamp($v);
1137 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1138
1139 $theday = $rr[1];
1140 $themth = substr(strtoupper($rr[2]),0,3);
1141 } else {
1142 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})|"
1143 ,$v, $rr)) return parent::UnixTimeStamp($v);
1144 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1145
1146 $theday = $rr[2];
1147 $themth = substr(strtoupper($rr[1]),0,3);
1148 }
1149
1150 $themth = $ADODB_mssql_mths[$themth];
1151 if ($themth <= 0) return false;
1152
1153 switch (strtoupper($rr[6])) {
1154 case 'P':
1155 if ($rr[4]<12) $rr[4] += 12;
1156 break;
1157 case 'A':
1158 if ($rr[4]==12) $rr[4] = 0;
1159 break;
1160 default:
1161 break;
1162 }
1163 // h-m-s-MM-DD-YY
1164 return adodb_mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
1165 }
1166 }
1167
1168 /*
1169 Code Example 1:
1170
1171 select object_name(constid) as constraint_name,
1172 object_name(fkeyid) as table_name,
1173 col_name(fkeyid, fkey) as column_name,
1174 object_name(rkeyid) as referenced_table_name,
1175 col_name(rkeyid, rkey) as referenced_column_name
1176 from sysforeignkeys
1177 where object_name(fkeyid) = x
1178 order by constraint_name, table_name, referenced_table_name, keyno
1179
1180 Code Example 2:
1181 select constraint_name,
1182 column_name,
1183 ordinal_position
1184 from information_schema.key_column_usage
1185 where constraint_catalog = db_name()
1186 and table_name = x
1187 order by constraint_name, ordinal_position
1188
1189 http://www.databasejournal.com/scripts/article.php/1440551
1190 */