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