[!!!][TASK] Doctrine: Remove ext:dbal
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / drivers / adodb-mssql.inc.php
1 <?php
2 /*
3 @version v5.20.3 01-Jan-2016
4 @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
5 @copyright (c) 2014 Damien Regad, Mark Newnham and the ADOdb community
6 Released under both BSD license and Lesser GPL library license.
7 Whenever there is any discrepancy between the two licenses,
8 the BSD license will take precedence.
9 Set tabs to 4 for best viewing.
10
11 Latest version is available at http://adodb.sourceforge.net
12
13 Native mssql driver. Requires mssql client. Works on Windows.
14 To configure for Unix, see
15 http://phpbuilder.com/columns/alberto20000919.php3
16
17 */
18
19
20 // security - hide paths
21 if (!defined('ADODB_DIR')) die();
22
23 //----------------------------------------------------------------
24 // MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002
25 // and this causes tons of problems because localized versions of
26 // MSSQL will return the dates in dmy or mdy order; and also the
27 // month strings depends on what language has been configured. The
28 // following two variables allow you to control the localization
29 // settings - Ugh.
30 //
31 // MORE LOCALIZATION INFO
32 // ----------------------
33 // To configure datetime, look for and modify sqlcommn.loc,
34 // typically found in c:\mssql\install
35 // Also read :
36 // http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918
37 // Alternatively use:
38 // CONVERT(char(12),datecol,120)
39 //----------------------------------------------------------------
40
41
42 // has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc
43 if (ADODB_PHPVER >= 0x4300) {
44 // docs say 4.2.0, but testing shows only since 4.3.0 does it work!
45 ini_set('mssql.datetimeconvert',0);
46 } else {
47 global $ADODB_mssql_mths; // array, months must be upper-case
48
49
50 $ADODB_mssql_date_order = 'mdy';
51 $ADODB_mssql_mths = array(
52 'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6,
53 'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12);
54 }
55
56 //---------------------------------------------------------------------------
57 // Call this to autoset $ADODB_mssql_date_order at the beginning of your code,
58 // just after you connect to the database. Supports mdy and dmy only.
59 // Not required for PHP 4.2.0 and above.
60 function AutoDetect_MSSQL_Date_Order($conn)
61 {
62 global $ADODB_mssql_date_order;
63 $adate = $conn->GetOne('select getdate()');
64 if ($adate) {
65 $anum = (int) $adate;
66 if ($anum > 0) {
67 if ($anum > 31) {
68 //ADOConnection::outp( "MSSQL: YYYY-MM-DD date format not supported currently");
69 } else
70 $ADODB_mssql_date_order = 'dmy';
71 } else
72 $ADODB_mssql_date_order = 'mdy';
73 }
74 }
75
76 class ADODB_mssql extends ADOConnection {
77 var $databaseType = "mssql";
78 var $dataProvider = "mssql";
79 var $replaceQuote = "''"; // string to use to replace quotes
80 var $fmtDate = "'Y-m-d'";
81 var $fmtTimeStamp = "'Y-m-d\TH:i:s'";
82 var $hasInsertID = true;
83 var $substr = "substring";
84 var $length = 'len';
85 var $hasAffectedRows = true;
86 var $metaDatabasesSQL = "select name from sysdatabases where name <> 'master'";
87 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'))";
88 var $metaColumnsSQL = # xtype==61 is datetime
89 "select c.name,t.name,c.length,c.isnullable, c.status,
90 (case when c.xusertype=61 then 0 else c.xprec end),
91 (case when c.xusertype=61 then 0 else c.xscale end)
92 from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'";
93 var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE
94 var $hasGenID = true;
95 var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)';
96 var $sysTimeStamp = 'GetDate()';
97 var $_has_mssql_init;
98 var $maxParameterLen = 4000;
99 var $arrayClass = 'ADORecordSet_array_mssql';
100 var $uniqueSort = true;
101 var $leftOuter = '*=';
102 var $rightOuter = '=*';
103 var $ansiOuter = true; // for mssql7 or later
104 var $poorAffectedRows = true;
105 var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000
106 var $uniqueOrderBy = true;
107 var $_bindInputArray = true;
108 var $forceNewConnect = false;
109
110 function __construct()
111 {
112 $this->_has_mssql_init = (strnatcmp(PHP_VERSION,'4.1.0')>=0);
113 }
114
115 function ServerInfo()
116 {
117 global $ADODB_FETCH_MODE;
118
119
120 if ($this->fetchMode === false) {
121 $savem = $ADODB_FETCH_MODE;
122 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
123 } else
124 $savem = $this->SetFetchMode(ADODB_FETCH_NUM);
125
126 if (0) {
127 $stmt = $this->PrepareSP('sp_server_info');
128 $val = 2;
129 $this->Parameter($stmt,$val,'attribute_id');
130 $row = $this->GetRow($stmt);
131 }
132
133 $row = $this->GetRow("execute sp_server_info 2");
134
135
136 if ($this->fetchMode === false) {
137 $ADODB_FETCH_MODE = $savem;
138 } else
139 $this->SetFetchMode($savem);
140
141 $arr['description'] = $row[2];
142 $arr['version'] = ADOConnection::_findvers($arr['description']);
143 return $arr;
144 }
145
146 function IfNull( $field, $ifNull )
147 {
148 return " ISNULL($field, $ifNull) "; // if MS SQL Server
149 }
150
151 function _insertid()
152 {
153 // SCOPE_IDENTITY()
154 // Returns the last IDENTITY value inserted into an IDENTITY column in
155 // the same scope. A scope is a module -- a stored procedure, trigger,
156 // function, or batch. Thus, two statements are in the same scope if
157 // they are in the same stored procedure, function, or batch.
158 if ($this->lastInsID !== false) {
159 return $this->lastInsID; // InsID from sp_executesql call
160 } else {
161 return $this->GetOne($this->identitySQL);
162 }
163 }
164
165
166
167 /**
168 * Correctly quotes a string so that all strings are escaped. We prefix and append
169 * to the string single-quotes.
170 * An example is $db->qstr("Don't bother",magic_quotes_runtime());
171 *
172 * @param s the string to quote
173 * @param [magic_quotes] if $s is GET/POST var, set to get_magic_quotes_gpc().
174 * This undoes the stupidity of magic quotes for GPC.
175 *
176 * @return quoted string to be sent back to database
177 */
178 function qstr($s,$magic_quotes=false)
179 {
180 if (!$magic_quotes) {
181 return "'".str_replace("'",$this->replaceQuote,$s)."'";
182 }
183
184 // undo magic quotes for " unless sybase is on
185 $sybase = ini_get('magic_quotes_sybase');
186 if (!$sybase) {
187 $s = str_replace('\\"','"',$s);
188 if ($this->replaceQuote == "\\'") // ' already quoted, no need to change anything
189 return "'$s'";
190 else {// change \' to '' for sybase/mssql
191 $s = str_replace('\\\\','\\',$s);
192 return "'".str_replace("\\'",$this->replaceQuote,$s)."'";
193 }
194 } else {
195 return "'".$s."'";
196 }
197 }
198 // moodle change end - see readme_moodle.txt
199
200 function _affectedrows()
201 {
202 return $this->GetOne('select @@rowcount');
203 }
204
205 var $_dropSeqSQL = "drop table %s";
206
207 function CreateSequence($seq='adodbseq',$start=1)
208 {
209
210 $this->Execute('BEGIN TRANSACTION adodbseq');
211 $start -= 1;
212 $this->Execute("create table $seq (id float(53))");
213 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
214 if (!$ok) {
215 $this->Execute('ROLLBACK TRANSACTION adodbseq');
216 return false;
217 }
218 $this->Execute('COMMIT TRANSACTION adodbseq');
219 return true;
220 }
221
222 function GenID($seq='adodbseq',$start=1)
223 {
224 //$this->debug=1;
225 $this->Execute('BEGIN TRANSACTION adodbseq');
226 $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
227 if (!$ok) {
228 $this->Execute("create table $seq (id float(53))");
229 $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
230 if (!$ok) {
231 $this->Execute('ROLLBACK TRANSACTION adodbseq');
232 return false;
233 }
234 $this->Execute('COMMIT TRANSACTION adodbseq');
235 return $start;
236 }
237 $num = $this->GetOne("select id from $seq");
238 $this->Execute('COMMIT TRANSACTION adodbseq');
239 return $num;
240
241 // in old implementation, pre 1.90, we returned GUID...
242 //return $this->GetOne("SELECT CONVERT(varchar(255), NEWID()) AS 'Char'");
243 }
244
245
246 function SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
247 {
248 if ($nrows > 0 && $offset <= 0) {
249 $sql = preg_replace(
250 '/(^\s*select\s+(distinctrow|distinct)?)/i','\\1 '.$this->hasTop." $nrows ",$sql);
251
252 if ($secs2cache)
253 $rs = $this->CacheExecute($secs2cache, $sql, $inputarr);
254 else
255 $rs = $this->Execute($sql,$inputarr);
256 } else
257 $rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
258
259 return $rs;
260 }
261
262
263 // Format date column in sql string given an input format that understands Y M D
264 function SQLDate($fmt, $col=false)
265 {
266 if (!$col) $col = $this->sysTimeStamp;
267 $s = '';
268
269 $len = strlen($fmt);
270 for ($i=0; $i < $len; $i++) {
271 if ($s) $s .= '+';
272 $ch = $fmt[$i];
273 switch($ch) {
274 case 'Y':
275 case 'y':
276 $s .= "datename(yyyy,$col)";
277 break;
278 case 'M':
279 $s .= "convert(char(3),$col,0)";
280 break;
281 case 'm':
282 $s .= "replace(str(month($col),2),' ','0')";
283 break;
284 case 'Q':
285 case 'q':
286 $s .= "datename(quarter,$col)";
287 break;
288 case 'D':
289 case 'd':
290 $s .= "replace(str(day($col),2),' ','0')";
291 break;
292 case 'h':
293 $s .= "substring(convert(char(14),$col,0),13,2)";
294 break;
295
296 case 'H':
297 $s .= "replace(str(datepart(hh,$col),2),' ','0')";
298 break;
299
300 case 'i':
301 $s .= "replace(str(datepart(mi,$col),2),' ','0')";
302 break;
303 case 's':
304 $s .= "replace(str(datepart(ss,$col),2),' ','0')";
305 break;
306 case 'a':
307 case 'A':
308 $s .= "substring(convert(char(19),$col,0),18,2)";
309 break;
310
311 default:
312 if ($ch == '\\') {
313 $i++;
314 $ch = substr($fmt,$i,1);
315 }
316 $s .= $this->qstr($ch);
317 break;
318 }
319 }
320 return $s;
321 }
322
323
324 function BeginTrans()
325 {
326 if ($this->transOff) return true;
327 $this->transCnt += 1;
328 $ok = $this->Execute('BEGIN TRAN');
329 return $ok;
330 }
331
332 function CommitTrans($ok=true)
333 {
334 if ($this->transOff) return true;
335 if (!$ok) return $this->RollbackTrans();
336 if ($this->transCnt) $this->transCnt -= 1;
337 $ok = $this->Execute('COMMIT TRAN');
338 return $ok;
339 }
340 function RollbackTrans()
341 {
342 if ($this->transOff) return true;
343 if ($this->transCnt) $this->transCnt -= 1;
344 $ok = $this->Execute('ROLLBACK TRAN');
345 return $ok;
346 }
347
348 function SetTransactionMode( $transaction_mode )
349 {
350 $this->_transmode = $transaction_mode;
351 if (empty($transaction_mode)) {
352 $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
353 return;
354 }
355 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
356 $this->Execute("SET TRANSACTION ".$transaction_mode);
357 }
358
359 /*
360 Usage:
361
362 $this->BeginTrans();
363 $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
364
365 # some operation on both tables table1 and table2
366
367 $this->CommitTrans();
368
369 See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
370 */
371 function RowLock($tables,$where,$col='1 as adodbignore')
372 {
373 if ($col == '1 as adodbignore') $col = 'top 1 null as ignore';
374 if (!$this->transCnt) $this->BeginTrans();
375 return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where");
376 }
377
378
379 function MetaColumns($table, $normalize=true)
380 {
381 // $arr = ADOConnection::MetaColumns($table);
382 // return $arr;
383
384 $this->_findschema($table,$schema);
385 if ($schema) {
386 $dbName = $this->database;
387 $this->SelectDB($schema);
388 }
389 global $ADODB_FETCH_MODE;
390 $save = $ADODB_FETCH_MODE;
391 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
392
393 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
394 $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table));
395
396 if ($schema) {
397 $this->SelectDB($dbName);
398 }
399
400 if (isset($savem)) $this->SetFetchMode($savem);
401 $ADODB_FETCH_MODE = $save;
402 if (!is_object($rs)) {
403 $false = false;
404 return $false;
405 }
406
407 $retarr = array();
408 while (!$rs->EOF){
409 $fld = new ADOFieldObject();
410 $fld->name = $rs->fields[0];
411 $fld->type = $rs->fields[1];
412
413 $fld->not_null = (!$rs->fields[3]);
414 $fld->auto_increment = ($rs->fields[4] == 128); // sys.syscolumns status field. 0x80 = 128 ref: http://msdn.microsoft.com/en-us/library/ms186816.aspx
415
416 if (isset($rs->fields[5]) && $rs->fields[5]) {
417 if ($rs->fields[5]>0) $fld->max_length = $rs->fields[5];
418 $fld->scale = $rs->fields[6];
419 if ($fld->scale>0) $fld->max_length += 1;
420 } else
421 $fld->max_length = $rs->fields[2];
422
423 if ($save == ADODB_FETCH_NUM) {
424 $retarr[] = $fld;
425 } else {
426 $retarr[strtoupper($fld->name)] = $fld;
427 }
428 $rs->MoveNext();
429 }
430
431 $rs->Close();
432 return $retarr;
433
434 }
435
436
437 function MetaIndexes($table,$primary=false, $owner=false)
438 {
439 $table = $this->qstr($table);
440
441 $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
442 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,
443 CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
444 FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
445 INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
446 INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
447 WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
448 ORDER BY O.name, I.Name, K.keyno";
449
450 global $ADODB_FETCH_MODE;
451 $save = $ADODB_FETCH_MODE;
452 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
453 if ($this->fetchMode !== FALSE) {
454 $savem = $this->SetFetchMode(FALSE);
455 }
456
457 $rs = $this->Execute($sql);
458 if (isset($savem)) {
459 $this->SetFetchMode($savem);
460 }
461 $ADODB_FETCH_MODE = $save;
462
463 if (!is_object($rs)) {
464 return FALSE;
465 }
466
467 $indexes = array();
468 while ($row = $rs->FetchRow()) {
469 if ($primary && !$row[5]) continue;
470
471 $indexes[$row[0]]['unique'] = $row[6];
472 $indexes[$row[0]]['columns'][] = $row[1];
473 }
474 return $indexes;
475 }
476
477 function MetaForeignKeys($table, $owner=false, $upper=false)
478 {
479 global $ADODB_FETCH_MODE;
480
481 $save = $ADODB_FETCH_MODE;
482 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
483 $table = $this->qstr(strtoupper($table));
484
485 $sql =
486 "select object_name(constid) as constraint_name,
487 col_name(fkeyid, fkey) as column_name,
488 object_name(rkeyid) as referenced_table_name,
489 col_name(rkeyid, rkey) as referenced_column_name
490 from sysforeignkeys
491 where upper(object_name(fkeyid)) = $table
492 order by constraint_name, referenced_table_name, keyno";
493
494 $constraints = $this->GetArray($sql);
495
496 $ADODB_FETCH_MODE = $save;
497
498 $arr = false;
499 foreach($constraints as $constr) {
500 //print_r($constr);
501 $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
502 }
503 if (!$arr) return false;
504
505 $arr2 = false;
506
507 foreach($arr as $k => $v) {
508 foreach($v as $a => $b) {
509 if ($upper) $a = strtoupper($a);
510 $arr2[$a] = $b;
511 }
512 }
513 return $arr2;
514 }
515
516 //From: Fernando Moreira <FMoreira@imediata.pt>
517 function MetaDatabases()
518 {
519 if(@mssql_select_db("master")) {
520 $qry=$this->metaDatabasesSQL;
521 if($rs=@mssql_query($qry,$this->_connectionID)){
522 $tmpAr=$ar=array();
523 while($tmpAr=@mssql_fetch_row($rs))
524 $ar[]=$tmpAr[0];
525 @mssql_select_db($this->database);
526 if(sizeof($ar))
527 return($ar);
528 else
529 return(false);
530 } else {
531 @mssql_select_db($this->database);
532 return(false);
533 }
534 }
535 return(false);
536 }
537
538 // "Stein-Aksel Basma" <basma@accelero.no>
539 // tested with MSSQL 2000
540 function MetaPrimaryKeys($table, $owner=false)
541 {
542 global $ADODB_FETCH_MODE;
543
544 $schema = '';
545 $this->_findschema($table,$schema);
546 if (!$schema) $schema = $this->database;
547 if ($schema) $schema = "and k.table_catalog like '$schema%'";
548
549 $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
550 information_schema.table_constraints tc
551 where tc.constraint_name = k.constraint_name and tc.constraint_type =
552 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
553
554 $savem = $ADODB_FETCH_MODE;
555 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
556 $a = $this->GetCol($sql);
557 $ADODB_FETCH_MODE = $savem;
558
559 if ($a && sizeof($a)>0) return $a;
560 $false = false;
561 return $false;
562 }
563
564
565 function MetaTables($ttype=false,$showSchema=false,$mask=false)
566 {
567 if ($mask) {
568 $save = $this->metaTablesSQL;
569 $mask = $this->qstr(($mask));
570 $this->metaTablesSQL .= " AND name like $mask";
571 }
572 $ret = ADOConnection::MetaTables($ttype,$showSchema);
573
574 if ($mask) {
575 $this->metaTablesSQL = $save;
576 }
577 return $ret;
578 }
579
580 function SelectDB($dbName)
581 {
582 $this->database = $dbName;
583 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
584 if ($this->_connectionID) {
585 return @mssql_select_db($dbName);
586 }
587 else return false;
588 }
589
590 function ErrorMsg()
591 {
592 if (empty($this->_errorMsg)){
593 $this->_errorMsg = mssql_get_last_message();
594 }
595 return $this->_errorMsg;
596 }
597
598 function ErrorNo()
599 {
600 if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
601 if (empty($this->_errorMsg)) {
602 $this->_errorMsg = mssql_get_last_message();
603 }
604 $id = @mssql_query("select @@ERROR",$this->_connectionID);
605 if (!$id) return false;
606 $arr = mssql_fetch_array($id);
607 @mssql_free_result($id);
608 if (is_array($arr)) return $arr[0];
609 else return -1;
610 }
611
612 // returns true or false, newconnect supported since php 5.1.0.
613 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename,$newconnect=false)
614 {
615 if (!function_exists('mssql_pconnect')) return null;
616 $this->_connectionID = mssql_connect($argHostname,$argUsername,$argPassword,$newconnect);
617 if ($this->_connectionID === false) return false;
618 if ($argDatabasename) return $this->SelectDB($argDatabasename);
619 return true;
620 }
621
622
623 // returns true or false
624 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
625 {
626 if (!function_exists('mssql_pconnect')) return null;
627 $this->_connectionID = mssql_pconnect($argHostname,$argUsername,$argPassword);
628 if ($this->_connectionID === false) return false;
629
630 // persistent connections can forget to rollback on crash, so we do it here.
631 if ($this->autoRollback) {
632 $cnt = $this->GetOne('select @@TRANCOUNT');
633 while (--$cnt >= 0) $this->Execute('ROLLBACK TRAN');
634 }
635 if ($argDatabasename) return $this->SelectDB($argDatabasename);
636 return true;
637 }
638
639 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
640 {
641 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename, true);
642 }
643
644 function Prepare($sql)
645 {
646 $sqlarr = explode('?',$sql);
647 if (sizeof($sqlarr) <= 1) return $sql;
648 $sql2 = $sqlarr[0];
649 for ($i = 1, $max = sizeof($sqlarr); $i < $max; $i++) {
650 $sql2 .= '@P'.($i-1) . $sqlarr[$i];
651 }
652 return array($sql,$this->qstr($sql2),$max,$sql2);
653 }
654
655 function PrepareSP($sql,$param=true)
656 {
657 if (!$this->_has_mssql_init) {
658 ADOConnection::outp( "PrepareSP: mssql_init only available since PHP 4.1.0");
659 return $sql;
660 }
661 $stmt = mssql_init($sql,$this->_connectionID);
662 if (!$stmt) return $sql;
663 return array($sql,$stmt);
664 }
665
666 // returns concatenated string
667 // MSSQL requires integers to be cast as strings
668 // automatically cast every datatype to VARCHAR(255)
669 // @author David Rogers (introspectshun)
670 function Concat()
671 {
672 $s = "";
673 $arr = func_get_args();
674
675 // Split single record on commas, if possible
676 if (sizeof($arr) == 1) {
677 foreach ($arr as $arg) {
678 $args = explode(',', $arg);
679 }
680 $arr = $args;
681 }
682
683 array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
684 $s = implode('+',$arr);
685 if (sizeof($arr) > 0) return "$s";
686
687 return '';
688 }
689
690 /*
691 Usage:
692 $stmt = $db->PrepareSP('SP_RUNSOMETHING'); -- takes 2 params, @myid and @group
693
694 # note that the parameter does not have @ in front!
695 $db->Parameter($stmt,$id,'myid');
696 $db->Parameter($stmt,$group,'group',false,64);
697 $db->Execute($stmt);
698
699 @param $stmt Statement returned by Prepare() or PrepareSP().
700 @param $var PHP variable to bind to. Can set to null (for isNull support).
701 @param $name Name of stored procedure variable name to bind to.
702 @param [$isOutput] Indicates direction of parameter 0/false=IN 1=OUT 2= IN/OUT. This is ignored in oci8.
703 @param [$maxLen] Holds an maximum length of the variable.
704 @param [$type] The data type of $var. Legal values depend on driver.
705
706 See mssql_bind documentation at php.net.
707 */
708 function Parameter(&$stmt, &$var, $name, $isOutput=false, $maxLen=4000, $type=false)
709 {
710 if (!$this->_has_mssql_init) {
711 ADOConnection::outp( "Parameter: mssql_bind only available since PHP 4.1.0");
712 return false;
713 }
714
715 $isNull = is_null($var); // php 4.0.4 and above...
716
717 if ($type === false)
718 switch(gettype($var)) {
719 default:
720 case 'string': $type = SQLVARCHAR; break;
721 case 'double': $type = SQLFLT8; break;
722 case 'integer': $type = SQLINT4; break;
723 case 'boolean': $type = SQLINT1; break; # SQLBIT not supported in 4.1.0
724 }
725
726 if ($this->debug) {
727 $prefix = ($isOutput) ? 'Out' : 'In';
728 $ztype = (empty($type)) ? 'false' : $type;
729 ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);");
730 }
731 /*
732 See http://phplens.com/lens/lensforum/msgs.php?id=7231
733
734 RETVAL is HARD CODED into php_mssql extension:
735 The return value (a long integer value) is treated like a special OUTPUT parameter,
736 called "RETVAL" (without the @). See the example at mssql_execute to
737 see how it works. - type: one of this new supported PHP constants.
738 SQLTEXT, SQLVARCHAR,SQLCHAR, SQLINT1,SQLINT2, SQLINT4, SQLBIT,SQLFLT8
739 */
740 if ($name !== 'RETVAL') $name = '@'.$name;
741 return mssql_bind($stmt[1], $name, $var, $type, $isOutput, $isNull, $maxLen);
742 }
743
744 /*
745 Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
746 So all your blobs must be of type "image".
747
748 Remember to set in php.ini the following...
749
750 ; Valid range 0 - 2147483647. Default = 4096.
751 mssql.textlimit = 0 ; zero to pass through
752
753 ; Valid range 0 - 2147483647. Default = 4096.
754 mssql.textsize = 0 ; zero to pass through
755 */
756 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
757 {
758
759 if (strtoupper($blobtype) == 'CLOB') {
760 $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
761 return $this->Execute($sql) != false;
762 }
763 $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
764 return $this->Execute($sql) != false;
765 }
766
767 // returns query ID if successful, otherwise false
768 function _query($sql,$inputarr=false)
769 {
770 $this->_errorMsg = false;
771 if (is_array($inputarr)) {
772
773 # bind input params with sp_executesql:
774 # see http://www.quest-pipelines.com/newsletter-v3/0402_F.htm
775 # works only with sql server 7 and newer
776 $getIdentity = false;
777 if (!is_array($sql) && preg_match('/^\\s*insert/i', $sql)) {
778 $getIdentity = true;
779 $sql .= (preg_match('/;\\s*$/i', $sql) ? ' ' : '; ') . $this->identitySQL;
780 }
781 if (!is_array($sql)) $sql = $this->Prepare($sql);
782 $params = '';
783 $decl = '';
784 $i = 0;
785 foreach($inputarr as $v) {
786 if ($decl) {
787 $decl .= ', ';
788 $params .= ', ';
789 }
790 if (is_string($v)) {
791 $len = strlen($v);
792 if ($len == 0) $len = 1;
793
794 if ($len > 4000 ) {
795 // NVARCHAR is max 4000 chars. Let's use NTEXT
796 $decl .= "@P$i NTEXT";
797 } else {
798 $decl .= "@P$i NVARCHAR($len)";
799 }
800
801 $params .= "@P$i=N". (strncmp($v,"'",1)==0? $v : $this->qstr($v));
802 } else if (is_integer($v)) {
803 $decl .= "@P$i INT";
804 $params .= "@P$i=".$v;
805 } else if (is_float($v)) {
806 $decl .= "@P$i FLOAT";
807 $params .= "@P$i=".$v;
808 } else if (is_bool($v)) {
809 $decl .= "@P$i INT"; # Used INT just in case BIT in not supported on the user's MSSQL version. It will cast appropriately.
810 $params .= "@P$i=".(($v)?'1':'0'); # True == 1 in MSSQL BIT fields and acceptable for storing logical true in an int field
811 } else {
812 $decl .= "@P$i CHAR"; # Used char because a type is required even when the value is to be NULL.
813 $params .= "@P$i=NULL";
814 }
815 $i += 1;
816 }
817 $decl = $this->qstr($decl);
818 if ($this->debug) ADOConnection::outp("<font size=-1>sp_executesql N{$sql[1]},N$decl,$params</font>");
819 $rez = mssql_query("sp_executesql N{$sql[1]},N$decl,$params", $this->_connectionID);
820 if ($getIdentity) {
821 $arr = @mssql_fetch_row($rez);
822 $this->lastInsID = isset($arr[0]) ? $arr[0] : false;
823 @mssql_data_seek($rez, 0);
824 }
825
826 } else if (is_array($sql)) {
827 # PrepareSP()
828 $rez = mssql_execute($sql[1]);
829 $this->lastInsID = false;
830
831 } else {
832 $rez = mssql_query($sql,$this->_connectionID);
833 $this->lastInsID = false;
834 }
835 return $rez;
836 }
837
838 // returns true or false
839 function _close()
840 {
841 if ($this->transCnt) $this->RollbackTrans();
842 $rez = @mssql_close($this->_connectionID);
843 $this->_connectionID = false;
844 return $rez;
845 }
846
847 // mssql uses a default date like Dec 30 2000 12:00AM
848 static function UnixDate($v)
849 {
850 return ADORecordSet_array_mssql::UnixDate($v);
851 }
852
853 static function UnixTimeStamp($v)
854 {
855 return ADORecordSet_array_mssql::UnixTimeStamp($v);
856 }
857 }
858
859 /*--------------------------------------------------------------------------------------
860 Class Name: Recordset
861 --------------------------------------------------------------------------------------*/
862
863 class ADORecordset_mssql extends ADORecordSet {
864
865 var $databaseType = "mssql";
866 var $canSeek = true;
867 var $hasFetchAssoc; // see http://phplens.com/lens/lensforum/msgs.php?id=6083
868 // _mths works only in non-localised system
869
870 function __construct($id,$mode=false)
871 {
872 // freedts check...
873 $this->hasFetchAssoc = function_exists('mssql_fetch_assoc');
874
875 if ($mode === false) {
876 global $ADODB_FETCH_MODE;
877 $mode = $ADODB_FETCH_MODE;
878
879 }
880 $this->fetchMode = $mode;
881 return parent::__construct($id,$mode);
882 }
883
884
885 function _initrs()
886 {
887 GLOBAL $ADODB_COUNTRECS;
888 $this->_numOfRows = ($ADODB_COUNTRECS)? @mssql_num_rows($this->_queryID):-1;
889 $this->_numOfFields = @mssql_num_fields($this->_queryID);
890 }
891
892
893 //Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
894 // get next resultset - requires PHP 4.0.5 or later
895 function NextRecordSet()
896 {
897 if (!mssql_next_result($this->_queryID)) return false;
898 $this->_inited = false;
899 $this->bind = false;
900 $this->_currentRow = -1;
901 $this->Init();
902 return true;
903 }
904
905 /* Use associative array to get fields array */
906 function Fields($colname)
907 {
908 if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
909 if (!$this->bind) {
910 $this->bind = array();
911 for ($i=0; $i < $this->_numOfFields; $i++) {
912 $o = $this->FetchField($i);
913 $this->bind[strtoupper($o->name)] = $i;
914 }
915 }
916
917 return $this->fields[$this->bind[strtoupper($colname)]];
918 }
919
920 /* Returns: an object containing field information.
921 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
922 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
923 fetchField() is retrieved. */
924
925 function FetchField($fieldOffset = -1)
926 {
927 if ($fieldOffset != -1) {
928 $f = @mssql_fetch_field($this->_queryID, $fieldOffset);
929 }
930 else if ($fieldOffset == -1) { /* The $fieldOffset argument is not provided thus its -1 */
931 $f = @mssql_fetch_field($this->_queryID);
932 }
933 $false = false;
934 if (empty($f)) return $false;
935 return $f;
936 }
937
938 function _seek($row)
939 {
940 return @mssql_data_seek($this->_queryID, $row);
941 }
942
943 // speedup
944 function MoveNext()
945 {
946 if ($this->EOF) return false;
947
948 $this->_currentRow++;
949
950 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
951 if ($this->fetchMode & ADODB_FETCH_NUM) {
952 //ADODB_FETCH_BOTH mode
953 $this->fields = @mssql_fetch_array($this->_queryID);
954 }
955 else {
956 if ($this->hasFetchAssoc) {// only for PHP 4.2.0 or later
957 $this->fields = @mssql_fetch_assoc($this->_queryID);
958 } else {
959 $flds = @mssql_fetch_array($this->_queryID);
960 if (is_array($flds)) {
961 $fassoc = array();
962 foreach($flds as $k => $v) {
963 if (is_numeric($k)) continue;
964 $fassoc[$k] = $v;
965 }
966 $this->fields = $fassoc;
967 } else
968 $this->fields = false;
969 }
970 }
971
972 if (is_array($this->fields)) {
973 if (ADODB_ASSOC_CASE == 0) {
974 foreach($this->fields as $k=>$v) {
975 $kn = strtolower($k);
976 if ($kn <> $k) {
977 unset($this->fields[$k]);
978 $this->fields[$kn] = $v;
979 }
980 }
981 } else if (ADODB_ASSOC_CASE == 1) {
982 foreach($this->fields as $k=>$v) {
983 $kn = strtoupper($k);
984 if ($kn <> $k) {
985 unset($this->fields[$k]);
986 $this->fields[$kn] = $v;
987 }
988 }
989 }
990 }
991 } else {
992 $this->fields = @mssql_fetch_row($this->_queryID);
993 }
994 if ($this->fields) return true;
995 $this->EOF = true;
996
997 return false;
998 }
999
1000
1001 // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
1002 // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
1003 function _fetch($ignore_fields=false)
1004 {
1005 if ($this->fetchMode & ADODB_FETCH_ASSOC) {
1006 if ($this->fetchMode & ADODB_FETCH_NUM) {
1007 //ADODB_FETCH_BOTH mode
1008 $this->fields = @mssql_fetch_array($this->_queryID);
1009 } else {
1010 if ($this->hasFetchAssoc) // only for PHP 4.2.0 or later
1011 $this->fields = @mssql_fetch_assoc($this->_queryID);
1012 else {
1013 $this->fields = @mssql_fetch_array($this->_queryID);
1014 if (@is_array($$this->fields)) {
1015 $fassoc = array();
1016 foreach($$this->fields as $k => $v) {
1017 if (is_integer($k)) continue;
1018 $fassoc[$k] = $v;
1019 }
1020 $this->fields = $fassoc;
1021 }
1022 }
1023 }
1024
1025 if (!$this->fields) {
1026 } else if (ADODB_ASSOC_CASE == 0) {
1027 foreach($this->fields as $k=>$v) {
1028 $kn = strtolower($k);
1029 if ($kn <> $k) {
1030 unset($this->fields[$k]);
1031 $this->fields[$kn] = $v;
1032 }
1033 }
1034 } else if (ADODB_ASSOC_CASE == 1) {
1035 foreach($this->fields as $k=>$v) {
1036 $kn = strtoupper($k);
1037 if ($kn <> $k) {
1038 unset($this->fields[$k]);
1039 $this->fields[$kn] = $v;
1040 }
1041 }
1042 }
1043 } else {
1044 $this->fields = @mssql_fetch_row($this->_queryID);
1045 }
1046 return $this->fields;
1047 }
1048
1049 /* close() only needs to be called if you are worried about using too much memory while your script
1050 is running. All associated result memory for the specified result identifier will automatically be freed. */
1051
1052 function _close()
1053 {
1054 if($this->_queryID) {
1055 $rez = mssql_free_result($this->_queryID);
1056 $this->_queryID = false;
1057 return $rez;
1058 }
1059 return true;
1060 }
1061
1062 // mssql uses a default date like Dec 30 2000 12:00AM
1063 static function UnixDate($v)
1064 {
1065 return ADORecordSet_array_mssql::UnixDate($v);
1066 }
1067
1068 static function UnixTimeStamp($v)
1069 {
1070 return ADORecordSet_array_mssql::UnixTimeStamp($v);
1071 }
1072
1073 }
1074
1075
1076 class ADORecordSet_array_mssql extends ADORecordSet_array {
1077 function __construct($id=-1,$mode=false)
1078 {
1079 parent::__construct($id,$mode);
1080 }
1081
1082 // mssql uses a default date like Dec 30 2000 12:00AM
1083 static function UnixDate($v)
1084 {
1085
1086 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
1087
1088 global $ADODB_mssql_mths,$ADODB_mssql_date_order;
1089
1090 //Dec 30 2000 12:00AM
1091 if ($ADODB_mssql_date_order == 'dmy') {
1092 if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
1093 return parent::UnixDate($v);
1094 }
1095 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1096
1097 $theday = $rr[1];
1098 $themth = substr(strtoupper($rr[2]),0,3);
1099 } else {
1100 if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
1101 return parent::UnixDate($v);
1102 }
1103 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1104
1105 $theday = $rr[2];
1106 $themth = substr(strtoupper($rr[1]),0,3);
1107 }
1108 $themth = $ADODB_mssql_mths[$themth];
1109 if ($themth <= 0) return false;
1110 // h-m-s-MM-DD-YY
1111 return mktime(0,0,0,$themth,$theday,$rr[3]);
1112 }
1113
1114 static function UnixTimeStamp($v)
1115 {
1116
1117 if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
1118
1119 global $ADODB_mssql_mths,$ADODB_mssql_date_order;
1120
1121 //Dec 30 2000 12:00AM
1122 if ($ADODB_mssql_date_order == 'dmy') {
1123 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})|"
1124 ,$v, $rr)) return parent::UnixTimeStamp($v);
1125 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1126
1127 $theday = $rr[1];
1128 $themth = substr(strtoupper($rr[2]),0,3);
1129 } else {
1130 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})|"
1131 ,$v, $rr)) return parent::UnixTimeStamp($v);
1132 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
1133
1134 $theday = $rr[2];
1135 $themth = substr(strtoupper($rr[1]),0,3);
1136 }
1137
1138 $themth = $ADODB_mssql_mths[$themth];
1139 if ($themth <= 0) return false;
1140
1141 switch (strtoupper($rr[6])) {
1142 case 'P':
1143 if ($rr[4]<12) $rr[4] += 12;
1144 break;
1145 case 'A':
1146 if ($rr[4]==12) $rr[4] = 0;
1147 break;
1148 default:
1149 break;
1150 }
1151 // h-m-s-MM-DD-YY
1152 return mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
1153 }
1154 }
1155
1156 /*
1157 Code Example 1:
1158
1159 select object_name(constid) as constraint_name,
1160 object_name(fkeyid) as table_name,
1161 col_name(fkeyid, fkey) as column_name,
1162 object_name(rkeyid) as referenced_table_name,
1163 col_name(rkeyid, rkey) as referenced_column_name
1164 from sysforeignkeys
1165 where object_name(fkeyid) = x
1166 order by constraint_name, table_name, referenced_table_name, keyno
1167
1168 Code Example 2:
1169 select constraint_name,
1170 column_name,
1171 ordinal_position
1172 from information_schema.key_column_usage
1173 where constraint_catalog = db_name()
1174 and table_name = x
1175 order by constraint_name, ordinal_position
1176
1177 http://www.databasejournal.com/scripts/article.php/1440551
1178 */