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