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