[BUGFIX] ADOdb: mssqlnative driver is not properly initialized
[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($seq, $start);
213 break;
214 case 11:
215 return $this->GenID2012($seq, $start);
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($seq, $start);
229 break;
230 case 11:
231 return $this->CreateSequence2012($seq, $start);
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 $num;
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, $start);
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 // Port is always given as part of $argHostname but
480 // 1) should only be set if using an IP/hostname and not a named instance
481 // 2) must use a comma instead of a colon
482 list ($hostname, $port) = explode(':', $argHostname, 2);
483 if (strpos($hostname, '\\') === false) {
484 $argHostname = $hostname . ',' . $port;
485 } else {
486 $argHostname = $hostname;
487 }
488 $connectionInfo = $this->connectionInfo;
489 $connectionInfo['Database'] = $argDatabasename;
490 if (!empty($argUsername)) {
491 $connectionInfo['UID'] = $argUsername;
492 }
493 if (!empty($argPassword)) {
494 $connectionInfo['PWD'] = $argPassword;
495 }
496 if (!empty($this->charSet)) {
497 $connectionInfo['CharacterSet'] = $this->charSet;
498 }
499 if ($this->debug) error_log("<hr>connecting... hostname: $argHostname params: ".var_export($connectionInfo,true));
500 //if ($this->debug) error_log("<hr>_connectionID before: ".serialize($this->_connectionID));
501 if(!($this->_connectionID = sqlsrv_connect($argHostname,$connectionInfo))) {
502 if ($this->debug) error_log( "<hr><b>errors</b>: ".print_r( sqlsrv_errors(), true));
503 return false;
504 }
505 //if ($this->debug) error_log(" _connectionID after: ".serialize($this->_connectionID));
506 //if ($this->debug) error_log("<hr>defined functions: <pre>".var_export(get_defined_functions(),true)."</pre>");
507 return true;
508 }
509
510 // returns true or false
511 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
512 {
513 //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!)
514 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
515 }
516
517 function Prepare($sql)
518 {
519 return $sql; // prepare does not work properly with bind parameters as bind parameters are managed by sqlsrv_prepare!
520
521 $stmt = sqlsrv_prepare( $this->_connectionID, $sql);
522 if (!$stmt) return $sql;
523 return array($sql,$stmt);
524 }
525
526 // returns concatenated string
527 // MSSQL requires integers to be cast as strings
528 // automatically cast every datatype to VARCHAR(255)
529 // @author David Rogers (introspectshun)
530 function Concat()
531 {
532 $s = "";
533 $arr = func_get_args();
534
535 // Split single record on commas, if possible
536 if (sizeof($arr) == 1) {
537 foreach ($arr as $arg) {
538 $args = explode(',', $arg);
539 }
540 $arr = $args;
541 }
542
543 array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
544 $s = implode('+',$arr);
545 if (sizeof($arr) > 0) return "$s";
546
547 return '';
548 }
549
550 /*
551 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
552 So all your blobs must be of type "image".
553
554 Remember to set in php.ini the following...
555
556 ; Valid range 0 - 2147483647. Default = 4096.
557 mssql.textlimit = 0 ; zero to pass through
558
559 ; Valid range 0 - 2147483647. Default = 4096.
560 mssql.textsize = 0 ; zero to pass through
561 */
562 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
563 {
564
565 if (strtoupper($blobtype) == 'CLOB') {
566 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
567 return $this->Execute($sql) != false;
568 }
569 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
570 return $this->Execute($sql) != false;
571 }
572
573 // returns query ID if successful, otherwise false
574 function _query($sql,$inputarr=false)
575 {
576 $this->_errorMsg = false;
577
578 if (is_array($sql)) $sql = $sql[1];
579
580 $insert = false;
581 // handle native driver flaw for retrieving the last insert ID
582 if(preg_match('/^\W*(insert [^;]+);?$/i', $sql)) {
583 $insert = true;
584 $sql .= '; '.$this->identitySQL; // select scope_identity()
585 }
586 if($inputarr) {
587 $rez = sqlsrv_query($this->_connectionID, $sql, $inputarr);
588 } else {
589 $rez = sqlsrv_query($this->_connectionID,$sql);
590 }
591
592 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));
593
594 if(!$rez) {
595 $rez = false;
596 } else if ($insert) {
597 // retrieve the last insert ID (where applicable)
598 sqlsrv_next_result($rez);
599 sqlsrv_fetch($rez);
600 $this->lastInsertID = sqlsrv_get_field($rez, 0);
601 }
602 return $rez;
603 }
604
605 // returns true or false
606 function _close()
607 {
608 if ($this->transCnt) $this->RollbackTrans();
609 $rez = @sqlsrv_close($this->_connectionID);
610 $this->_connectionID = false;
611 return $rez;
612 }
613
614 // mssql uses a default date like Dec 30 2000 12:00AM
615 static function UnixDate($v)
616 {
617 return ADORecordSet_array_mssqlnative::UnixDate($v);
618 }
619
620 static function UnixTimeStamp($v)
621 {
622 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
623 }
624
625 function MetaIndexes($table,$primary=false, $owner = false)
626 {
627 $table = $this->qstr($table);
628
629 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
630 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,
631 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
632 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
633 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
634 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
635 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
636 ORDER BY O.name, I.Name, K.keyno";
637
638 global $ADODB_FETCH_MODE;
639 $save = $ADODB_FETCH_MODE;
640 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
641 if ($this->fetchMode !== FALSE) {
642 $savem = $this->SetFetchMode(FALSE);
643 }
644
645 $rs = $this->Execute($sql);
646 if (isset($savem)) {
647 $this->SetFetchMode($savem);
648 }
649 $ADODB_FETCH_MODE = $save;
650
651 if (!is_object($rs)) {
652 return FALSE;
653 }
654
655 $indexes = array();
656 while ($row = $rs->FetchRow()) {
657 if (!$primary && $row[5]) continue;
658
659 $indexes[$row[0]]['unique'] = $row[6];
660 $indexes[$row[0]]['columns'][] = $row[1];
661 }
662 return $indexes;
663 }
664
665 function MetaForeignKeys($table, $owner=false, $upper=false)
666 {
667 global $ADODB_FETCH_MODE;
668
669 $save = $ADODB_FETCH_MODE;
670 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
671 $table = $this->qstr(strtoupper($table));
672
673 $sql =
674 "select object_name(constid) as constraint_name,
675 col_name(fkeyid, fkey) as column_name,
676 object_name(rkeyid) as referenced_table_name,
677 col_name(rkeyid, rkey) as referenced_column_name
678 from sysforeignkeys
679 where upper(object_name(fkeyid)) = $table
680 order by constraint_name, referenced_table_name, keyno";
681
682 $constraints =& $this->GetArray($sql);
683
684 $ADODB_FETCH_MODE = $save;
685
686 $arr = false;
687 foreach($constraints as $constr) {
688 //print_r($constr);
689 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
690 }
691 if (!$arr) return false;
692
693 $arr2 = false;
694
695 foreach($arr as $k => $v) {
696 foreach($v as $a => $b) {
697 if ($upper) $a = strtoupper($a);
698 $arr2[$a] = $b;
699 }
700 }
701 return $arr2;
702 }
703
704 //From: Fernando Moreira <FMoreira@imediata.pt>
705 function MetaDatabases()
706 {
707 $this->SelectDB("master");
708 $rs =& $this->Execute($this->metaDatabasesSQL);
709 $rows = $rs->GetRows();
710 $ret = array();
711 for($i=0;$i<count($rows);$i++) {
712 $ret[] = $rows[$i][0];
713 }
714 $this->SelectDB($this->database);
715 if($ret)
716 return $ret;
717 else
718 return false;
719 }
720
721 // "Stein-Aksel Basma" <basma@accelero.no>
722 // tested with MSSQL 2000
723 function MetaPrimaryKeys($table, $owner=false)
724 {
725 global $ADODB_FETCH_MODE;
726
727 $schema = '';
728 $this->_findschema($table,$schema);
729 if (!$schema) $schema = $this->database;
730 if ($schema) $schema = "and k.table_catalog like '$schema%'";
731
732 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
733 information_schema.table_constraints tc
734 where tc.constraint_name = k.constraint_name and tc.constraint_type =
735 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
736
737 $savem = $ADODB_FETCH_MODE;
738 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
739 $a = $this->GetCol($sql);
740 $ADODB_FETCH_MODE = $savem;
741
742 if ($a && sizeof($a)>0) return $a;
743 $false = false;
744 return $false;
745 }
746
747
748 function MetaTables($ttype=false,$showSchema=false,$mask=false)
749 {
750 if ($mask) {
751 $save = $this->metaTablesSQL;
752 $mask = $this->qstr(($mask));
753 $this->metaTablesSQL .= " AND name like $mask";
754 }
755 $ret = ADOConnection::MetaTables($ttype,$showSchema);
756
757 if ($mask) {
758 $this->metaTablesSQL = $save;
759 }
760 return $ret;
761 }
762 function MetaColumns($table, $upper=true, $schema=false){
763
764 # start adg
765 static $cached_columns = array();
766 if ($this->cachedSchemaFlush)
767 $cached_columns = array();
768
769 if (array_key_exists($table,$cached_columns)){
770 return $cached_columns[$table];
771 }
772 # end adg
773
774 if (!$this->mssql_version)
775 $this->ServerVersion();
776
777 $this->_findschema($table,$schema);
778 if ($schema) {
779 $dbName = $this->database;
780 $this->SelectDB($schema);
781 }
782 global $ADODB_FETCH_MODE;
783 $save = $ADODB_FETCH_MODE;
784 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
785
786 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
787 $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table));
788
789 if ($schema) {
790 $this->SelectDB($dbName);
791 }
792
793 if (isset($savem)) $this->SetFetchMode($savem);
794 $ADODB_FETCH_MODE = $save;
795 if (!is_object($rs)) {
796 $false = false;
797 return $false;
798 }
799
800 $retarr = array();
801 while (!$rs->EOF){
802
803 $fld = new ADOFieldObject();
804 if (array_key_exists(0,$rs->fields)) {
805 $fld->name = $rs->fields[0];
806 $fld->type = $rs->fields[1];
807 $fld->max_length = $rs->fields[2];
808 $fld->precision = $rs->fields[3];
809 $fld->scale = $rs->fields[4];
810 $fld->not_null =!$rs->fields[5];
811 $fld->has_default = $rs->fields[6];
812 $fld->xtype = $rs->fields[7];
813 $fld->type_length = $rs->fields[8];
814 $fld->auto_increment= $rs->fields[9];
815 } else {
816 $fld->name = $rs->fields['name'];
817 $fld->type = $rs->fields['type'];
818 $fld->max_length = $rs->fields['length'];
819 $fld->precision = $rs->fields['precision'];
820 $fld->scale = $rs->fields['scale'];
821 $fld->not_null =!$rs->fields['nullable'];
822 $fld->has_default = $rs->fields['default_value'];
823 $fld->xtype = $rs->fields['xtype'];
824 $fld->type_length = $rs->fields['type_length'];
825 $fld->auto_increment= $rs->fields['is_identity'];
826 }
827
828 if ($save == ADODB_FETCH_NUM)
829 $retarr[] = $fld;
830 else
831 $retarr[strtoupper($fld->name)] = $fld;
832
833 $rs->MoveNext();
834
835 }
836 $rs->Close();
837 # start adg
838 $cached_columns[$table] = $retarr;
839 # end adg
840 return $retarr;
841 }
842
843 }
844
845 /*--------------------------------------------------------------------------------------
846 Class Name: Recordset
847 --------------------------------------------------------------------------------------*/
848
849 class ADORecordset_mssqlnative extends ADORecordSet {
850
851 var $databaseType = "mssqlnative";
852 var $canSeek = false;
853 var $fieldOffset = 0;
854 // _mths works only in non-localised system
855
856 function ADORecordset_mssqlnative($id,$mode=false)
857 {
858 if ($mode === false) {
859 global $ADODB_FETCH_MODE;
860 $mode = $ADODB_FETCH_MODE;
861
862 }
863 $this->fetchMode = $mode;
864 return $this->ADORecordSet($id,$mode);
865 }
866
867
868 function _initrs()
869 {
870 global $ADODB_COUNTRECS;
871 # KMN # if ($this->connection->debug) error_log("(before) ADODB_COUNTRECS: {$ADODB_COUNTRECS} _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
872 /*$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."
873 error_log("rowsaff: ".serialize($retRowsAff));
874 $this->_numOfRows = ($ADODB_COUNTRECS)? $retRowsAff:-1;*/
875 $this->_numOfRows = -1;//not supported
876 $fieldmeta = sqlsrv_field_metadata($this->_queryID);
877 $this->_numOfFields = ($fieldmeta)? count($fieldmeta):-1;
878 # KMN # if ($this->connection->debug) error_log("(after) _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
879 /*
880 * Copy the oracle method and cache the metadata at init time
881 */
882 if ($this->_numOfFields>0) {
883 $this->_fieldobjs = array();
884 $max = $this->_numOfFields;
885 for ($i=0;$i<$max; $i++) $this->_fieldobjs[] = $this->_FetchField($i);
886 }
887
888 }
889
890
891 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
892 // get next resultset - requires PHP 4.0.5 or later
893 function NextRecordSet()
894 {
895 if (!sqlsrv_next_result($this->_queryID)) return false;
896 $this->_inited = false;
897 $this->bind = false;
898 $this->_currentRow = -1;
899 $this->Init();
900 return true;
901 }
902
903 /* Use associative array to get fields array */
904 function Fields($colname)
905 {
906 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
907 if (!$this->bind) {
908 $this->bind = array();
909 for ($i=0; $i < $this->_numOfFields; $i++) {
910 $o = $this->FetchField($i);
911 $this->bind[strtoupper($o->name)] = $i;
912 }
913 }
914
915 return $this->fields[$this->bind[strtoupper($colname)]];
916 }
917
918 /* Returns: an object containing field information.
919 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
920 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
921 fetchField() is retrieved.
922 Designed By jcortinap#jc.com.mx
923 */
924 function _FetchField($fieldOffset = -1)
925 {
926 $_typeConversion = array(
927 -155 => 'datetimeoffset',
928 -154 => 'time',
929 -152 => 'xml',
930 -151 => 'udt',
931 -11 => 'uniqueidentifier',
932 -10 => 'ntext',
933 -9 => 'nvarchar',
934 -8 => 'nchar',
935 -7 => 'bit',
936 -6 => 'tinyint',
937 -5 => 'bigint',
938 -4 => 'image',
939 -3 => 'varbinary',
940 -2 => 'timestamp',
941 -1 => 'text',
942 1 => 'char',
943 2 => 'numeric',
944 3 => 'decimal',
945 4 => 'int',
946 5 => 'smallint',
947 6 => 'float',
948 7 => 'real',
949 12 => 'varchar',
950 91 => 'date',
951 93 => 'datetime'
952 );
953
954 $fa = @sqlsrv_field_metadata($this->_queryID);
955 if ($fieldOffset != -1) {
956 $fa = $fa[$fieldOffset];
957 }
958 $false = false;
959 if (empty($fa)) {
960 $f = false;//PHP Notice: Only variable references should be returned by reference
961 }
962 else
963 {
964 // Convert to an object
965 $fa = array_change_key_case($fa, CASE_LOWER);
966 $fb = array();
967 if ($fieldOffset != -1)
968 {
969 $fb = array(
970 'name' => $fa['name'],
971 'max_length' => $fa['size'],
972 'column_source' => $fa['name'],
973 'type' => $_typeConversion[$fa['type']]
974 );
975 }
976 else
977 {
978 foreach ($fa as $key => $value)
979 {
980 $fb[] = array(
981 'name' => $value['name'],
982 'max_length' => $value['size'],
983 'column_source' => $value['name'],
984 'type' => $_typeConversion[$value['type']]
985 );
986 }
987 }
988 $f = (object) $fb;
989 }
990 return $f;
991 }
992
993 /*
994 * Fetchfield copies the oracle method, it loads the field information
995 * into the _fieldobjs array once, to save multiple calls to the
996 * sqlsrv_field_metadata function
997 *
998 * @author KM Newnham
999 * @date 02/20/2013
1000 */
1001 function FetchField($fieldOffset = -1)
1002 {
1003 return $this->_fieldobjs[$fieldOffset];
1004 }
1005
1006 function _seek($row)
1007 {
1008 return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams.
1009 }
1010
1011 // speedup
1012 function MoveNext()
1013 {
1014 //# KMN # if ($this->connection->debug) error_log("movenext()");
1015 //# KMN # if ($this->connection->debug) error_log("eof (beginning): ".$this->EOF);
1016 if ($this->EOF) return false;
1017
1018 $this->_currentRow++;
1019 // # KMN # if ($this->connection->debug) error_log("_currentRow: ".$this->_currentRow);
1020
1021 if ($this->_fetch()) return true;
1022 $this->EOF = true;
1023 //# KMN # if ($this->connection->debug) error_log("eof (end): ".$this->EOF);
1024
1025 return false;
1026 }
1027
1028
1029 // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
1030 // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
1031 function _fetch($ignore_fields=false)
1032 {
1033 # KMN # if ($this->connection->debug) error_log("_fetch()");
1034 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
1035 if ($this->fetchMode & ADODB_FETCH_NUM) {
1036 //# KMN # if ($this->connection->debug) error_log("fetch mode: both");
1037 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH);
1038 } else {
1039 //# KMN # if ($this->connection->debug) error_log("fetch mode: assoc");
1040 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC);
1041 }
1042
1043 if (is_array($this->fields)) {
1044 if (ADODB_ASSOC_CASE == 0) {
1045 foreach($this->fields as $k=>$v) {
1046 $this->fields[strtolower($k)] = $v;
1047 }
1048 } else if (ADODB_ASSOC_CASE == 1) {
1049 foreach($this->fields as $k=>$v) {
1050 $this->fields[strtoupper($k)] = $v;
1051 }
1052 }
1053 }
1054 } else {
1055 //# KMN # if ($this->connection->debug) error_log("fetch mode: num");
1056 $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC);
1057 }
1058 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
1059 $arrFixed = array();
1060 foreach($this->fields as $key=>$value) {
1061 if(is_numeric($key)) {
1062 $arrFixed[$key-1] = $value;
1063 } else {
1064 $arrFixed[$key] = $value;
1065 }
1066 }
1067 //if($this->connection->debug) error_log("<hr>fixing non 0 based return array, old: ".print_r($this->fields,true)." new: ".print_r($arrFixed,true));
1068 $this->fields = $arrFixed;
1069 }
1070 if(is_array($this->fields)) {
1071 foreach($this->fields as $key=>$value) {
1072 if (is_object($value) && method_exists($value, 'format')) {//is DateTime object
1073 $this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z");
1074 }
1075 }
1076 }
1077 if($this->fields === null) $this->fields = false;
1078 # KMN # if ($this->connection->debug) error_log("<hr>after _fetch, fields: <pre>".print_r($this->fields,true)." backtrace: ".adodb_backtrace(false));
1079 return $this->fields;
1080 }
1081
1082 /* close() only needs to be called if you are worried about using too much memory while your script
1083 is running. All associated result memory for the specified result identifier will automatically be freed. */
1084 function _close()
1085 {
1086 $rez = sqlsrv_free_stmt($this->_queryID);
1087 $this->_queryID = false;
1088 return $rez;
1089 }
1090
1091 // mssql uses a default date like Dec 30 2000 12:00AM
1092 static function UnixDate($v)
1093 {
1094 return ADORecordSet_array_mssqlnative::UnixDate($v);
1095 }
1096
1097 static function UnixTimeStamp($v)
1098 {
1099 return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
1100 }
1101 }
1102
1103
1104 class ADORecordSet_array_mssqlnative extends ADORecordSet_array {
1105 function ADORecordSet_array_mssqlnative($id=-1,$mode=false)
1106 {
1107 $this->ADORecordSet_array($id,$mode);
1108 }
1109
1110 // mssql uses a default date like Dec 30 2000 12:00AM
1111 static function UnixDate($v)
1112 {
1113
1114 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
1115
1116 global $ADODB_mssql_mths,$ADODB_mssql_date_order;
1117
1118 //Dec 30 2000 12:00AM
1119 if ($ADODB_mssql_date_order == 'dmy') {
1120 if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
1121 return parent::UnixDate($v);
1122 }
1123 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1124
1125 $theday = $rr[1];
1126 $themth = substr(strtoupper($rr[2]),0,3);
1127 } else {
1128 if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
1129 return parent::UnixDate($v);
1130 }
1131 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1132
1133 $theday = $rr[2];
1134 $themth = substr(strtoupper($rr[1]),0,3);
1135 }
1136 $themth = $ADODB_mssql_mths[$themth];
1137 if ($themth <= 0) return false;
1138 // h-m-s-MM-DD-YY
1139 return adodb_mktime(0,0,0,$themth,$theday,$rr[3]);
1140 }
1141
1142 static function UnixTimeStamp($v)
1143 {
1144
1145 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
1146
1147 global $ADODB_mssql_mths,$ADODB_mssql_date_order;
1148
1149 //Dec 30 2000 12:00AM
1150 if ($ADODB_mssql_date_order == 'dmy') {
1151 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})|"
1152 ,$v, $rr)) return parent::UnixTimeStamp($v);
1153 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1154
1155 $theday = $rr[1];
1156 $themth = substr(strtoupper($rr[2]),0,3);
1157 } else {
1158 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})|"
1159 ,$v, $rr)) return parent::UnixTimeStamp($v);
1160 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1161
1162 $theday = $rr[2];
1163 $themth = substr(strtoupper($rr[1]),0,3);
1164 }
1165
1166 $themth = $ADODB_mssql_mths[$themth];
1167 if ($themth <= 0) return false;
1168
1169 switch (strtoupper($rr[6])) {
1170 case 'P':
1171 if ($rr[4]<12) $rr[4] += 12;
1172 break;
1173 case 'A':
1174 if ($rr[4]==12) $rr[4] = 0;
1175 break;
1176 default:
1177 break;
1178 }
1179 // h-m-s-MM-DD-YY
1180 return adodb_mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
1181 }
1182 }
1183
1184 /*
1185 Code Example 1:
1186
1187 select object_name(constid) as constraint_name,
1188 object_name(fkeyid) as table_name,
1189 col_name(fkeyid, fkey) as column_name,
1190 object_name(rkeyid) as referenced_table_name,
1191 col_name(rkeyid, rkey) as referenced_column_name
1192 from sysforeignkeys
1193 where object_name(fkeyid) = x
1194 order by constraint_name, table_name, referenced_table_name, keyno
1195
1196 Code Example 2:
1197 select constraint_name,
1198 column_name,
1199 ordinal_position
1200 from information_schema.key_column_usage
1201 where constraint_catalog = db_name()
1202 and table_name = x
1203 order by constraint_name, ordinal_position
1204
1205 http://www.databasejournal.com/scripts/article.php/1440551
1206 */